DFG2_Data_Visuals_Hands-on_Guide_EN

1.

Dataflow Gen2 data visuals (preview): hands-on guide Build a “dashboard experience” in Dataflow Gen2 A step-by-step guide to data visuals (preview), using sample data you can try yourself September 30, 2026 | Applies to: Microsoft Fabric Dataflow Gen2 | Estimated time: basics about 35 min + advanced about 35 min About this guide • Data visuals are a preview feature of Dataflow Gen2. This guide is based on public information, including the Microsoft Learn article “Create data visuals in Dataflow Gen2 (Preview)” (updated September 18, 2026) and the Fabric September 2026 Feature Summary, and rebuilds the walkthrough around its own sample data. • Every M query in this guide was evaluated with the Power Query engine in Excel and automatically checked against the visualization-document rules in the official documentation (exactly one root row, a Card has exactly one child, KpiCard values are text, and so on). The checker itself was tested with 16 rule-violation patterns. How visuals render in Dataflow Gen2 (colors, sort order, layout details) can differ from the figures in this guide. • Figures are native Word shapes and charts, so you can edit them (right-click a chart, and then select Edit Data). Screenshots are from Microsoft Learn (CC BY 4.0). • All code is also included as text files (.pq) in the M_code_en folder. Copying from the files is recommended. Contents 1. What are data visuals? ....................................................................................................................................................... 3 1.1 What’s new ................................................................................................................................................................................................. 3 1.2 How it works: the query returns a “visualization document”................................................................................................. 3 1.3 Parts (PartType) and charts (ChartType) ......................................................................................................................................... 4 1.4 Data visuals vs. Power BI reports ....................................................................................................................................................... 5 1.5 How to build a “dashboard experience” in DFG2 ....................................................................................................................... 6 2. Get ready .............................................................................................................................................................................. 7 2.1 What you need ......................................................................................................................................................................................... 7 1

2.

Dataflow Gen2 data visuals (preview): hands-on guide 2.2 What you’ll build ...................................................................................................................................................................................... 7 2.3 Sample data: “Contoso Coffee” ......................................................................................................................................................... 8 2.4 Create a Dataflow Gen2 ........................................................................................................................................................................ 9 2.5 Common operations .............................................................................................................................................................................. 9 3. Basics: assemble the dashboard .................................................................................................................................... 10 Step 1 Create the sample data (SalesData) [5 min] .................................................................................................................. 10 Step 2 Your first visual (Visual_Hello) [5 min] .............................................................................................................................. 12 Step 3 Build the skeleton: header and KPI cards [10 min] ..................................................................................................... 13 Step 4 Add charts [10 min] ................................................................................................................................................................. 15 Step 5 Add a detail table to finish [5 min] .................................................................................................................................... 17 4. Advanced: make it feel more like a dashboard ......................................................................................................... 19 Step 6 Switch regions like a slicer with a parameter [10 min] .............................................................................................. 19 Step 7 A regional scorecard generated from data [10 min].................................................................................................. 20 Step 8 A reusable data profile dashboard [10 min] .................................................................................................................. 22 Step 9 Check with the self-diagnostic tool [5 min] ................................................................................................................... 24 5. Share and reuse ................................................................................................................................................................ 25 6. Troubleshooting ............................................................................................................................................................... 27 7. Limitations and design tips ............................................................................................................................................ 28 7.1 Limitations (during preview) ............................................................................................................................................................ 28 7.2 Design tips ............................................................................................................................................................................................... 28 7.3 Use your own data ............................................................................................................................................................................... 29 8. References .......................................................................................................................................................................... 29 Appendix A Visualization document quick reference ............................................................................................... 30 Appendix B Included files................................................................................................................................................. 30 Appendix C Complete code .............................................................................................................................................. 31 C-1 Dashboard (Step 5, complete) ..................................................................................................................................................... 31 C-2 Dashboard_Regions (Step 7) ........................................................................................................................................................ 33 C-3 fnProfileDashboard (Step 8) ......................................................................................................................................................... 35 C-4 fnCheckVisualDocument (Step 9) ............................................................................................................................................... 37 2

3.

Dataflow Gen2 data visuals (preview): hands-on guide 1. What are data visuals? 1.1 What’s new Until now, the results of a Dataflow Gen2 (DFG2) query appeared in the data preview only as a grid of rows and columns. With data visuals (preview), a query can return a visualization document instead of a table, and DFG2 renders a dashboard of headers, KPI cards, charts, and tables on the same authoring canvas. • A dashboard is “just another query”: You write it entirely in Power Query M. There’s no separate designer or report file; the dashboard is saved with your dataflow and can be reviewed in the Advanced editor. • Generated dynamically from data: Not only KPI values, but also chart series and even the number of cards can be computed from your data (you do this in Step 7). • Nothing to enable: According to the Feature Summary, data visuals render automatically in DFG2. • Two main uses: (1) exploring data (profiles of nulls, distributions, and outliers) and (2) summarizing results (KPIs and charts that highlight the story). Figure 1. Data visuals rendered on the DFG2 canvas (sample from the official documentation; source: Microsoft Learn, CC BY 4.0) 1.2 How it works: the query returns a “visualization document” Behind the dashboard is a small, flat table with the following five columns. Each row represents one visual, and the Parent column builds the hierarchy (nesting). The table itself has no nesting. 3

4.

Dataflow Gen2 data visuals (preview): hands-on guide Table 1. The five columns of a visualization document Column Type Meaning Name nullable text A unique ID for the row (for example, kpi-revenue) Parent nullable text The Name of the parent row; null only for the single top-level (root) row PartType nullable text The kind of visual: Container, Card, Header, KpiCard, Table, or Chart Properties nullable record Data any Settings for that kind (title, displayed values, chart type and column mapping, and so on) The table a chart or table visual reads; null for rows that don’t use data ③ Visualization document (a five-column table) ① Source data ② Shape the data Name | Parent | PartType ④ Rendered as SalesData Table.Group dashboard | null | Container a dashboard (a 576-row table) Number.ToText header | dashboard | Header on the DFG2 an ordinary query Power Query M kpi-row | dashboard | Container canvas kpi-revenue | kpi-row | KpiCard … (plus Properties and Data) Return a five-column table (Name, Parent, PartType, Properties, Data) and DFG2 renders visuals, not a grid Figure 2. From query to rendering Two points to remember • Whether a result renders as visuals depends on the column names of the returned table. If any of the five columns is missing or misnamed, you see a regular table instead (columns beyond these five are ignored). • Even with all five columns, invalid contents cause errors. For example, a number in Properties prevents the whole preview from rendering, while a number in a KpiCard’s Value shows an error only for that one visual. 1.3 Parts (PartType) and charts (ChartType) During preview, six PartType values are supported. All charts use PartType = "Chart", and the kind of chart is set with ChartType in Properties. Earlier chart-specific values such as LineChart and BarChart aren’t recognized. 4

5.

[beta]

Dataflow Gen2 data visuals (preview): hands-on guide

Table 2. PartType values
PartType
Container

Card
Header
KpiCard

What it shows
Arranges children in a row or a
column
Wraps a child in a frame with a
title
Header text plus far-aligned text
One key metric, displayed
prominently

Children

Required inputs

One or more

None

Exactly one

Title (text)

–

None

Header (text)

FarText (text or null)

None

Value and Label (text)

Sub (text or null)

A table in the Data column

–

Table

The rows and columns of a table

None

Chart

A chart

None

Optional inputs
Direction: "row"
(default) or "column"

ChartType, DataSeries, and a
table in the Data column

ChartTitle

Table 3. ChartType values
ChartType

Best for

ValueColumns

Line

Trends (for example, revenue by month)

1 column

Area

Trends, with the area under the line filled in

1 column

Bar

Comparing values across categories

1 column

StackedBar

Comparing categories with a color-coded breakdown

1 or more (wide format)

Doughnut

Share of a whole (hollow center)

1 column

Pie

Share of a whole

1 column

In a chart’s Properties, pass a DataSeries record, and set AxisColumns (the axis column) and ValueColumns
(the value columns) to column names in the table that you pass in the Data column. A single column name
can be written as text or as a list: "Region" and {"Region"} mean the same thing.

1.4 Data visuals vs. Power BI reports
Table 4. DFG2 data visuals compared with Power BI reports
Aspect
Primary users

Purpose
How you build

DFG2 data visuals

Power BI reports

Authors who prepare data (data engineers,
analysts)
Checking data while you prepare it,

Business users and decision makers

Analysis, sharing, decision-making

summarizing results, quality checks
Write a visualization document in M code

5

Drag and drop on a report canvas

6.

Dataflow Gen2 data visuals (preview): hands-on guide Aspect Interactivity Where it appears DFG2 data visuals Power BI reports No filters, slicers, or cross-filtering (a snapshot at evaluation time) The DFG2 authoring canvas (including the shared-query view) Refresh and Not part of refresh output, data destinations, output or the DFG2 connector Availability Preview Interactive filtering and drill-down Power BI service, apps, Teams, and more Scheduled refresh, Direct Lake, and more Generally available In short, data visuals don’t replace Power BI. They let you check your data and communicate the key points right where you prepare it. This guide shows, hands-on, how far you can take the “dashboard experience” with this feature. 1.5 How to build a “dashboard experience” in DFG2 The following table maps what you expect from a dashboard to how you can achieve it with DFG2 data visuals. Even features that aren’t built in, such as filters and slicers, can be approximated by combining Power Query M with parameters. Table 5. Dashboard elements and how to achieve them Dashboard element How to achieve it in DFG2 In this guide Layout (rows, columns, grid) Nest Containers (row/column), and add titles with Cards Steps 3–5 Title and period Header (Header plus far-aligned FarText) Step 3 KpiCard (Value/Label/Sub); format numbers as text with Number.ToText Step 3 KPIs (big number + comparison) Trend, comparison, and mix Chart (Line/Area/Bar/StackedBar/Doughnut/Pie) fed with small charts aggregated tables Details Table with only the columns you need Step 5 Slicers (filtering) Switch a parameter value and re-evaluate the whole query Step 6 Change Sub or table text by condition (✓, ⚠, ✗) Step 7 Generate visualization-document rows with List.Transform/List.Split Step 7 Conditional formatting and alerts Small multiples Data quality monitoring Apply a profile function (Table.Profile + distributions + outliers) to any table Steps 2, 4 Step 8 Quality assurance Inspect visualization documents with a rule-checking function Step 9 Sharing and reuse My queries, shared queries, and share links (view-only experience) Section 5 Keeping it current Re-evaluate with Refresh preview (a snapshot at evaluation time) Section 2.5 6

7.

Dataflow Gen2 data visuals (preview): hands-on guide 2. Get ready 2.1 What you need • A workspace that has a Microsoft Fabric capacity (a trial capacity works) • Permission to create and edit Dataflow Gen2 items in that workspace (Contributor or higher) • No external data sources or credentials (the Northwind sample in Step 8 is optional) 2.2 What you’ll build You add the following queries to a single dataflow. In Steps 3–6, you keep growing the same Dashboard query. Table 6. Queries you create in this guide Step Query name Kind Contents File (M_code_en) 1 SalesData Table Sample data (576 rows) 01_SalesData.pq 2 Visual_Hello Visuals 3–5 Dashboard Visuals 6 pRegion Parameter 7 Dashboard_Regions Visuals 8 fnProfileDashboard, and so on 9 Function + visuals Minimal example: a card with a line chart Header, KPIs, six chart types, detail table 02_Visual_Hello.pq 03–05_Dashboard_*.pq Switch regions (Dashboard is 06a_pRegion_parameter.pq updated too) / 06b_*.pq Regional scorecard (generated) 07_Dashboard_Regions.pq Reusable data profile 08a–08d_*.pq fnCheckVisualDocument / Function + Self-check for visualization Check_All table documents 7 09a–09b_*.pq

8.

Dataflow Gen2 data visuals (preview): hands-on guide dashboard (Container: column) header (Header) Contoso Coffee Sales Dashboard ………… FY2025 (vs. prior year) kpi-row (Container: row) Revenue Target achievement Gross margin Orders ¥4.99B 101.7% 46.8% 2,015,397 +11.4% vs. prior year Target ¥4.91B +0.3 pts vs. prior year Avg. order ¥2,477 trend-row (Container: row) Card: Monthly revenue trend (¥M) Chart: Line Card: Monthly orders Month → Revenue (¥M) Chart: Area Month → Orders region-row (Container: row) Card: Revenue by region (¥M) Chart: Bar Card: Revenue by region and category (¥M) Region → Revenue (¥M) Chart: StackedBar Region → 4 categories mix-row (Container: row) Card: Revenue mix by category Chart: Doughnut Card: Order mix by category Category → Revenue Chart: Pie Category → Orders detail-card (Card) Card: Detail by region and category (FY2025) | Table (24 rows × 6 columns) Figure 3. The Dashboard you complete in Step 5 (wireframe) 2.3 Sample data: “Contoso Coffee” The sample contains sales records for Contoso Coffee, a fictitious coffee company in Japan. The data is generated by M formulas, so you don’t need any external file. Because no random numbers are used, everyone gets the same values and can compare their results with the checkpoints in each step. Table 7. Columns of SalesData Column Type Description Example (first row) MonthStart date First day of the month 2024-04-01 Month text Year and month 2024-04 FiscalYear whole number Fiscal year (April–March) 2024 Region text Region (6 regions) Hokkaido-Tohoku Category text Product category (4 categories) Coffee Beans Revenue number Revenue (JPY) 13,582,000 Target number Revenue target (JPY) 14,440,000 GrossProfit number Gross profit (JPY) 6,292,000 Orders whole number Number of orders 5,509 8

9.

Dataflow Gen2 data visuals (preview): hands-on guide The data covers 24 months (April 2024 to March 2026, which is fiscal years 2024 and 2025) across 6 regions and 4 categories, for 576 rows. Revenue rises in winter, especially in December (the year-end gift season in Japan), and gifts also peak in July (the summer gift season). FY2025 revenue is ¥4.99B (+11.4% vs. prior year), but target achievement differs by region. 700 Revenue (¥M) 600 500 400 300 200 100 0 Apr May Jun Jul Aug Sep FY2024 Oct Nov Dec Jan Feb Mar FY2025 Figure 4. Reference: monthly revenue in the sample data (¥M), re-created as a Word chart 2.4 Create a Dataflow Gen2 1. In the Fabric portal (app.fabric.microsoft.com), open the workspace you want to use. 2. Select + New item, and then select Dataflow Gen2. 3. Enter a name, such as DFG2_DataVisuals_Handson, and create the item. The Power Query editor opens. 2.5 Common operations You repeat these operations throughout the steps. Table 8. Common operations Operation Add a blank query Rename a query Replace a query’s code Refresh the display How to do it Select Home > Get data > Blank query. Delete the existing text in the editor, paste the code, and then select Next. Change Name under Query settings on the right. Or, right-click the query in the Queries pane and select Rename. Select the query, open Home > Advanced editor, replace all of the code, and then select OK. Select Home > Refresh > Refresh preview. 9

10.

[beta]

Dataflow Gen2 data visuals (preview): hands-on guide

Recommended settings for visual queries
• Don’t set a data destination: Data visuals are only for display on the authoring canvas. They aren’t part of
refresh output or data destinations.

• Turn off staging: Right-click the query; if Enable staging is on, turning it off is a safe choice (this guide’s
recommendation).

• Remember to save: Save the dataflow to keep your work. You don’t need to run a refresh just to view
visuals.

3. Basics: assemble the dashboard
In the basics, you grow a single query, Dashboard, step by step—header, KPIs, charts, and details—until the
dashboard is complete.

Step 1 Create the sample data (SalesData) [5 min]
1. Add a blank query, paste the following code (01_SalesData.pq), and then select Next.
2. Rename the query to SalesData. Later queries refer to it by this name.
Query: SalesData | File: M_code_en\01_SalesData.pq
// SalesData (Step 1)
// Sample sales data for Contoso Coffee (a fictitious company in Japan)
// FY2024-FY2025 (Apr 2024-Mar 2026): 24 months x 6 regions x 4 categories = 576 rows
// Values come from formulas, not random numbers, so everyone gets the same results.
let
// Regions: base monthly revenue (JPY millions) and FY2025 growth rate
Regions = #table(
type table [Region = text, Base = number, Growth = number],
{
{"Hokkaido-Tohoku", 38, -0.02},
{"Kanto", 120, 0.08},
{"Chubu", 55, 0.05},
{"Kansai", 70, 0.06},
{"Chugoku-Shikoku", 30, 0.03},
{"Kyushu-Okinawa", 42, 0.15}
}
),
// Categories: revenue share, gross margin, average order value (JPY), FY2025 growth rate
Categories = #table(
type table [
Category = text, Share = number, Margin = number,
Ticket = number, Growth = number, IsGift = logical
],
{
{"Coffee Beans", 0.40, 0.45, 2400, 0.03, false},
{"Drip Bags", 0.25, 0.55, 1500, 0.14, false},
{"Cafe Supplies", 0.20, 0.35, 4800, -0.04, false},
{"Gifts", 0.15, 0.50, 5500, 0.06, true}
}
),
// Seasonal factors (April-March). Gifts peak in July and December (gift seasons in Japan)
Season
= {0.95, 0.90, 0.88, 0.85, 0.85, 0.95, 1.05, 1.12, 1.25, 1.18, 1.10, 1.00},
GiftPeak = {1.0, 1.0, 1.0, 2.2, 1.0, 1.0, 1.0, 1.0, 2.8, 1.0, 1.0, 1.0},
// A "pseudo-random" function: the same input always returns the same value (0 to <1)
Hash = (seed as number) as number =>
Number.Mod((seed + 1) * (seed + 7) * 7919 + 104729, 10007) / 10007,

10

11.

[beta]

Dataflow Gen2 data visuals (preview): hands-on guide

RegionList
= Table.ToRecords(Regions),
CategoryList = Table.ToRecords(Categories),
// The list of rows (List.Buffer computes it once and keeps it in memory)
RowList = List.Buffer(List.Combine(
List.Transform({0..23}, (m) =>
List.Combine(
List.Transform({0..5}, (r) =>
List.Transform({0..3}, (c) =>
let
reg
= RegionList{r},
cat
= CategoryList{c},
i
= m * 24 + r * 4 + c,
fm
= Number.Mod(m, 12),
fy
= if m < 12 then 2024 else 2025,
monthStart = Date.AddMonths(#date(2024, 4, 1), m),
plan
= reg[Base] * 1000000 * cat[Share] * Season{fm}
* (if cat[IsGift] then GiftPeak{fm} else 1),
growth
= if fy = 2025
then (1 + reg[Growth]) * (1 + cat[Growth]) else 1,
noise
= 0.94 + 0.12 * Hash(i),
revenue
= Number.Round(plan * growth * noise / 1000) * 1000,
targetRate = if fy = 2025 then 1.10 else 1,
target
= Number.Round(plan * targetRate / 1000) * 1000,
margin
= cat[Margin] + (Hash(i + 1000) - 0.5) * 0.04,
gross
= Number.Round(revenue * margin / 1000) * 1000,
ticket
= cat[Ticket] * (0.95 + 0.10 * Hash(i + 2000)),
orders
= Number.Round(revenue / ticket)
in
{monthStart, Date.ToText(monthStart, "yyyy-MM"), fy,
reg[Region], cat[Category], revenue, target, gross, orders}
)
)
)
)
)),
SalesData = #table(
type table [
MonthStart = date, Month = text, FiscalYear = Int64.Type,
Region = text, Category = text,
Revenue = number, Target = number, GrossProfit = number, Orders = Int64.Type
],
RowList
)
in
SalesData

Key points
• Regions and Categories: #table definitions of the assumptions for each region and category (base
revenue, share, gross margin, average order value, growth rate).
• Season and GiftPeak: Monthly seasonal factors. The first item in each list is April.
• Hash: A “pseudo-random” function that always returns the same value for the same input. It adds about
±6% variation to revenue.
• List.Buffer: Keeps the generated list of rows in memory so it isn’t recalculated each time other queries
reference it.

11

12.

[beta]

Dataflow Gen2 data visuals (preview): hands-on guide

Checkpoint
• A table with 576 rows and 9 columns appears (288 rows each for FiscalYear 2024 and 2025).
• The first row is “2024-04 / Hokkaido-Tohoku / Coffee Beans / Revenue 13,582,000”.
• It still appears as a regular table, not visuals, because it isn’t a five-column visualization document.

Step 2 Your first visual (Visual_Hello) [5 min]
1. Add a blank query and paste the following code (02_Visual_Hello.pq).
2. Rename the query to Visual_Hello.
Query: Visual_Hello | File: M_code_en\02_Visual_Hello.pq
// Visual_Hello (Step 2)
// The smallest data visual: one line chart inside a card (which supplies the title)
let
// A small table with FY2025 monthly revenue (JPY millions)
MonthlyRevenue = Table.Sort(
Table.Group(
Table.SelectRows(SalesData, each [FiscalYear] = 2025),
{"Month"},
{{"Revenue (¥M)", each Number.Round(List.Sum([Revenue]) / 1000000, 1), type number}}
),
{{"Month", Order.Ascending}}
),
// Visualization document type: with these five columns, the result renders as visuals
VisualDocumentType = type table [
Name = nullable text, Parent = nullable text, PartType = nullable text,
Properties = nullable record, Data = any
],
// One row = one visual. Row 2 names row 1 as its Parent, so it sits inside the card
VisualDocument = #table(
VisualDocumentType,
{
{"sales-card", null, "Card", [Title = "Monthly revenue (FY2025, ¥M)"], null},
{"sales-trend", "sales-card", "Chart",
[ChartType = "Line",
DataSeries = [AxisColumns = "Month", ValueColumns = "Revenue (¥M)"]],
MonthlyRevenue}
}
)
in
VisualDocument

Key points
• MonthlyRevenue is a small table (12 rows × 2 columns) of FY2025 monthly revenue in millions of yen.
Pass small, pre-aggregated tables like this to charts.
• The first row of the visualization document is a Card. Its Parent is null, so it’s the root. The second row is a
Chart whose Parent is "sales-card", which places the chart inside the card.

12

13.

Dataflow Gen2 data visuals (preview): hands-on guide • The chart’s Properties set ChartType = "Line" and, in DataSeries, map the Month column to the axis and the Revenue (¥M) column to the values. These names must match the column names of MonthlyRevenue, the table you pass in the Data column. Figure 5. Reference: the same structure (a Line chart inside a Card), rendered (sample from the official documentation; source: Microsoft Learn, CC BY 4.0) Checkpoint • A line chart covering 12 months appears inside the card “Monthly revenue (FY2025, ¥M)”. • Values start at 375.3 in April and peak at 636.0 in December. Try it • Change ChartType to "Area" or "Bar" to see the same data as a different chart. • Change PartType to "LineChart" to see “Visual not recognized” (earlier names aren’t supported). Change it back afterward. Step 3 Build the skeleton: header and KPI cards [10 min] 1. Add a blank query and paste the following code (03_Dashboard_step3_skeleton.pq). 2. Rename the query to Dashboard. In Steps 4–6, you replace this query’s code. Query: Dashboard (Step 3) | File: M_code_en\03_Dashboard_step3_skeleton.pq // Dashboard (Step 3): the skeleton, with a header and KPI cards only let // ---- 1) Data: latest fiscal year (current) and the prior year ---CurFY = List.Max(SalesData[FiscalYear]), PrevFY = CurFY - 1, Cur = Table.SelectRows(SalesData, each [FiscalYear] = CurFY), Prev = Table.SelectRows(SalesData, each [FiscalYear] = PrevFY), 13

14.

[beta]

Dataflow Gen2 data visuals (preview): hands-on guide

// ---- 2) KPI calculations (numbers) ---Revenue
= List.Sum(Cur[Revenue]),
RevenuePrev
= List.Sum(Prev[Revenue]),
Target
= List.Sum(Cur[Target]),
GrossMargin
= List.Sum(Cur[GrossProfit]) / Revenue,
GrossMarginPrev = List.Sum(Prev[GrossProfit]) / RevenuePrev,
Orders
= List.Sum(Cur[Orders]),
// ---- 3) KPI display text (KpiCard values must be text) ---Fmt
= (n as number, format as text) as text => Number.ToText(n, format, "en-US"),
Signed = (n as number, format as text) as text =>
(if n > 0 then "+" else "") & Fmt(n, format),
Pct
= (n as number) as text => Fmt(n * 100, "N1") & "%",
RevenueText
= "¥" & Fmt(Revenue / 1000000000, "N2") & "B",
RevenueSub
= Signed((Revenue / RevenuePrev - 1) * 100, "N1") & "% vs. prior year",
AchievementText = Pct(Revenue / Target),
AchievementSub = "Target ¥" & Fmt(Target / 1000000000, "N2") & "B",
MarginText
= Pct(GrossMargin),
MarginSub
= Signed((GrossMargin - GrossMarginPrev) * 100, "N1") & " pts vs. prior year",
OrdersText
= Fmt(Orders, "N0"),
OrdersSub
= "Avg. order ¥" & Fmt(Revenue / Orders, "N0"),
// ---- 4) Visualization document (one row = one visual; Parent builds the hierarchy) ---VisualDocumentType = type table [
Name = nullable text, Parent = nullable text, PartType = nullable text,
Properties = nullable record, Data = any
],
VisualDocument = #table(
VisualDocumentType,
{
// Root (the only row with Parent = null). column = stack children vertically
{"dashboard", null, "Container", [Direction = "column"], null},
{"header", "dashboard", "Header",
[Header = "Contoso Coffee Sales Dashboard",
FarText = "FY" & Text.From(CurFY) & " (vs. prior year)"], null},
// KPI row. row = place children side by side
{"kpi-row", "dashboard", "Container", [Direction = "row"], null},
{"kpi-revenue", "kpi-row", "KpiCard",
[Label = "Revenue", Value = RevenueText, Sub = RevenueSub], null},
{"kpi-achievement", "kpi-row", "KpiCard",
[Label = "Target achievement", Value = AchievementText, Sub = AchievementSub], null},
{"kpi-margin", "kpi-row", "KpiCard",
[Label = "Gross margin", Value = MarginText, Sub = MarginSub], null},
{"kpi-orders", "kpi-row", "KpiCard",
[Label = "Orders", Value = OrdersText, Sub = OrdersSub], null}
}
)
in
VisualDocument

14

15.

Dataflow Gen2 data visuals (preview): hands-on guide Layout is defined by Parent nesting dashboard Container (column) = root header kpi-row Header Container (row) Put the parent’s Name in Parent, and the row nests under it. column = stack vertically kpi-revenue kpi-achievement kpi-margin kpi-orders KpiCard KpiCard KpiCard KpiCard row = place side by side Figure 6. Parent-child structure in Step 3 (Name and Parent) • The root, dashboard, is a Container with Direction = "column", so its children—the header and kpirow—stack vertically. • kpi-row is a Container with Direction = "row", so its four KpiCards line up side by side. • KpiCard Value and Label must be text. Format numbers with Number.ToText first ("N1" = one decimal place, "N0" = a whole number with thousands separators). Passing "en-US" as the third argument makes the result independent of the environment. This guide builds percentages as Fmt(n * 100, "N1") & "%" to avoid platform differences in the "P" format. • Put a comparison, such as year-over-year change or the target, in Sub (the small line) so the meaning of each number is clear at a glance. Table 9. Checkpoint: KPI cards (all regions, FY2025) Label Value Sub Revenue ¥4.99B +11.4% vs. prior year Target achievement 101.7% Target ¥4.91B Gross margin 46.8% +0.3 pts vs. prior year Orders 2,015,397 Avg. order ¥2,477 The status bar shows 5 columns and 7 rows (the rows of the visualization document). Step 4 Add charts [10 min] 1. Select the Dashboard query, open Advanced editor, and replace all of the code with the contents of 04_Dashboard_step4_charts.pq. 2. Select OK. Three rows of charts (trends, regional comparison, and mix) appear below the KPIs. 15

16.

[beta]

Dataflow Gen2 data visuals (preview): hands-on guide

You added (1) aggregated tables for the charts and (2) rows for the cards and charts. The key parts are
excerpted below; comments marked with ★ indicate additions.
Excerpt 1: aggregated tables for the charts (04_Dashboard_step4_charts.pq)
// ---- 4) Aggregated tables for the charts (★Added in Step 4) ---// Column names become axis titles and table headers, so use display names with units
ToMillions = (n as number) as number => Number.Round(n / 1000000, 1),
RevenueByMonth = Table.Sort(
Table.Group(Cur, {"Month"},
{{"Revenue (¥M)", each ToMillions(List.Sum([Revenue])), type number}}),
{{"Month", Order.Ascending}}),
OrdersByMonth = Table.Sort(
Table.Group(Cur, {"Month"},
{{"Orders", each List.Sum([Orders]), type number}}),
{{"Month", Order.Ascending}}),
RevenueByRegion = Table.Sort(
Table.Group(Cur, {"Region"},
{{"Revenue (¥M)", each ToMillions(List.Sum([Revenue])), type number}}),
{{"Revenue (¥M)", Order.Descending}}),
// A stacked bar needs wide data (one column per category): spread them with Table.Pivot
CategoryNames = List.Sort(List.Distinct(Cur[Category])),
RevenueByRegionCategory = Table.Pivot(
Table.Group(Cur, {"Region", "Category"},
{{"Revenue", each ToMillions(List.Sum([Revenue])), type number}}),
CategoryNames, "Category", "Revenue", List.Sum),
// (RevenueByCategory and OrdersByCategory are aggregated the same way)

Excerpt 2: rows added to the visualization document (04_Dashboard_step4_charts.pq)
// ★Added in Step 4: trends (line and area)
{"trend-row", "dashboard", "Container", [Direction = "row"], null},
{"revenue-trend-card", "trend-row", "Card", [Title = "Monthly revenue trend (¥M)"], null},
{"revenue-trend-chart", "revenue-trend-card", "Chart",
[ChartType = "Line",
DataSeries = [AxisColumns = "Month", ValueColumns = "Revenue (¥M)"]],
RevenueByMonth},
{"orders-trend-card", "trend-row", "Card", [Title = "Monthly orders"], null},
{"orders-trend-chart", "orders-trend-card", "Chart",
[ChartType = "Area",
DataSeries = [AxisColumns = "Month", ValueColumns = "Orders"]],
OrdersByMonth},
…
{"region-mix-chart", "region-mix-card", "Chart",
[ChartType = "StackedBar",
DataSeries = [AxisColumns = "Region", ValueColumns = CategoryNames]],
RevenueByRegionCategory},

Key points
• Axis title = column name: Charts show the column names of the Data table as axis titles. That’s why the
aggregated columns get display names with units, such as Revenue (¥M).
• Format numbers in the data: Charts have no number-format property, so ToMillions rounds to millions
of yen, and the column name shows the unit.
• Stacked bars need wide data: StackedBar needs one column per category. Table.Pivot spreads the
Category values into columns, and ValueColumns receives the list of column names, CategoryNames, as
is. New categories are picked up automatically.

16

17.

Dataflow Gen2 data visuals (preview): hands-on guide • Titles come from Cards: Each chart is the child of a Card that supplies its title (a Card has exactly one child). Table 10. Charts added in Step 4 Data (rows × Name ChartType Axis → values revenue-trend-chart Line 12 × 2 Month → Revenue (¥M) orders-trend-chart Area 12 × 2 Month → Orders region-chart Bar 6×2 Region → Revenue (¥M) region-mix-chart StackedBar 6×5 category-chart Doughnut 4×2 Category → Revenue (¥M) orders-mix-chart Pie 4×2 Category → Orders columns) Region → Cafe Supplies, Coffee Beans, Drip Bags, Gifts Kanto 1,719.0 Kansai 973.7 Chubu 761.3 Kyushu-Okinawa 637.5 Hokkaido-Tohoku 492.0 Chugoku-Shikoku 409.6 0 500 1,000 1,500 2,000 Revenue (¥M) Figure 7. Checkpoint: revenue by region (FY2025, ¥M), re-created as a Word chart About sort order In the official documentation’s screenshot, the regional bar chart appears in alphabetical order even though the data passed to it is sorted by revenue in descending order. To fix the display order reliably, prefix the labels with numbers (the histograms in Step 8 use ①, ②, and so on). Step 5 Add a detail table to finish [5 min] 1. Replace all of the Dashboard query’s code with the contents of 05_Dashboard_step5_complete.pq (Appendix C-1). 17

18.

[beta]

Dataflow Gen2 data visuals (preview): hands-on guide

Excerpt: adding the detail table (05_Dashboard_step5_complete.pq)
// ★Added in Step 5: detail table (region x category, largest revenue first)
DetailTable = Table.Sort(
Table.Group(Cur, {"Region", "Category"}, {
{"Revenue (¥M)", each ToMillions(List.Sum([Revenue])), type number},
{"Target achievement (%)",
each Number.Round(List.Sum([Revenue]) / List.Sum([Target]) * 100, 1), type number},
{"Gross margin (%)",
each Number.Round(List.Sum([GrossProfit]) / List.Sum([Revenue]) * 100, 1), type number},
{"Orders", each List.Sum([Orders]), type number}}),
{{"Revenue (¥M)", Order.Descending}}),
…
// ★Added in Step 5: detail (a Table as the only child of a Card)
{"detail-card", "dashboard", "Card",
[Title = "Detail by region and category (FY" & Text.From(CurFY) & ")"], null},
{"detail-table", "detail-card", "Table", [], DetailTable}

A Table part shows the table you pass in Data as rows and columns; it doesn’t aggregate. Detail tables tend
to get large, so select only the columns you need and sort the rows before you pass the table.

Figure 8. Reference: mix charts and a detail table (sample from the official documentation; source: Microsoft Learn,
CC BY 4.0)

Checkpoint
• The status bar at the bottom shows 5 columns and 24 rows (the rows of the visualization document,
including containers and cards).

• The detail table has 24 rows and 6 columns. The first row is “Kanto / Coffee Beans: revenue ¥659.1M, target
achievement 103.3%, gross margin 44.8%”.

18

19.

Dataflow Gen2 data visuals (preview): hands-on guide Summary of the basics: write in three stages • Aggregate: Build small tables for the charts and tables with Table.Group and similar functions (column names = display names). • Format: Turn KPI numbers into text with Number.ToText. • Arrange: Nest Containers (row/column) and Cards, with one visual per row of the document. 4. Advanced: make it feel more like a dashboard Data visuals have no filters, slicers, or cross-filtering. The advanced steps combine DFG2 and Power Query M features to add dashboard-like experiences: switching views, growing automatically, reusing across any data, and checking that nothing is broken. Step 6 Switch regions like a slicer with a parameter [10 min] When you change the value of a DFG2 parameter, the queries that reference it are re-evaluated. You use this behavior to switch the whole dashboard when you select a region. ① Pick a value ② Re-evaluate ③ Recalculate Current value of pRegion Source filters SalesData KPIs, charts, and details ④ View switches The header shows All / Kanto / Kansai … by Region (snapshot at evaluation) “Kanto | FY2025 …” Instead of a slicer, change the parameter value to re-evaluate the whole query (offer a list of values to choose from) Figure 9. Switching views with a parameter 1. Select Home > Manage parameters > New parameter. 2. Set Name to pRegion, Type to Text, and Suggested values to List of values, and then enter All, Hokkaido-Tohoku, Kanto, Chubu, Kansai, Chugoku-Shikoku, and Kyushu-Okinawa. Set the default and current values to All, and save. Instead of using the UI, you can also paste 06a_pRegion_parameter.pq (below) into a blank query and name it pRegion. 3. Replace all of the Dashboard query’s code with the contents of 06b_Dashboard_step6_parameter.pq. 4. Select pRegion in the Queries pane, change the current value to Kanto, and then select Dashboard. If the display doesn’t change, select Refresh preview. 19

20.

[beta]

Dataflow Gen2 data visuals (preview): hands-on guide

Query: pRegion (parameter) | File: M_code_en\06a_pRegion_parameter.pq
// pRegion (Step 6): a parameter for switching regions
// Usually created with Home > Manage parameters. Without the UI, paste this code into
// a blank query and name it pRegion; Power Query then treats it as a parameter.
"All" meta [
IsParameterQuery = true,
IsParameterQueryRequired = true,
Type = "Text",
List = {"All", "Hokkaido-Tohoku", "Kanto", "Chubu", "Kansai", "Chugoku-Shikoku", "Kyushu-Okinawa"},
DefaultValue = "All"
]

Excerpt: changes to Dashboard (06b_Dashboard_step6_parameter.pq). The header’s FarText and the detail title also show
pRegion
// ---- 0) Filter the data with the pRegion parameter (★Added in Step 6) ---Source = if pRegion = "All" then SalesData
else Table.SelectRows(SalesData, each [Region] = pRegion),
Checked = if Table.IsEmpty(Source)
then error Error.Record("NoData", "No data matches the pRegion value", pRegion)
else Source,
// ---- 1) Data: latest and prior fiscal year (★SalesData changed to Checked) ---CurFY = List.Max(Checked[FiscalYear]),
PrevFY = CurFY - 1,
Cur
= Table.SelectRows(Checked, each [FiscalYear] = CurFY),
Prev
= Table.SelectRows(Checked, each [FiscalYear] = PrevFY),

Table 11. Checkpoint: KPIs when you switch pRegion
KPI

All

Kanto

Revenue

¥4.99B (+11.4% vs. prior year)

¥1.72B (+13.6% vs. prior year)

Target achievement

101.7% (Target ¥4.91B)

103.6% (Target ¥1.66B)

Gross margin

46.8% (+0.3 pts vs. prior year)

46.7% (-0.1 pts vs. prior year)

Orders

2,015,397 (Avg. order ¥2,477)

692,663 (Avg. order ¥2,482)

Points to note
• The far-aligned text in the header (FarText) shows the selected region, so you always know which view
you’re looking at.

• If you enter a value that doesn’t exist (for example, Tokyo), the Checked step raises the error “No data
matches the pRegion value.” This prevents a confusing display of empty data.

• You can add parameters for fiscal year or category in the same way.

Step 7 A regional scorecard generated from data [10 min]
If you hand-write a KPI card and a chart for each region, you must edit the code whenever the regions
change. Because a visualization document is “just a table,” you can generate the rows themselves in M.
20

21.

[beta]

Dataflow Gen2 data visuals (preview): hands-on guide

RegionStats
One record per region × 6
[Region, Achievement,
YoY, Status, Monthly]

Generate rows

List.Split(…, 3)

kpi-row-1 / kpi-row-2

Groups of three

(Container: row)

regions × 2

Fixed & generated
→ visualization document

kpi-1 … kpi-6 (KpiCard)

(31 rows)

When regions are added, KPI cards, charts, and row containers grow with no code changes
Names are built from numbers, so they’re unique (kpi-1, trend-card-1, trend-chart-1 …)

Figure 10. Generating the scorecard rows automatically

1. Add a blank query, paste 07_Dashboard_Regions.pq (Appendix C-2), and name the query
Dashboard_Regions.
Excerpt: generating the KPI card rows (07_Dashboard_Regions.pq)
// ---- 2) Three per row: generate row containers and their child rows as lists ---Groups = List.Split(RegionStats, 3),
KpiRows = List.Combine(List.Transform(List.Positions(Groups), (g) =>
{ {"kpi-row-" & Text.From(g + 1), "scorecard", "Container", [Direction = "row"], null} }
& List.Transform(Groups{g}, (r) =>
{"kpi-" & Text.From(r[No]), "kpi-row-" & Text.From(g + 1), "KpiCard",
[Label = r[Region] & ": target achievement",
Value = Pct(r[Achievement]),
Sub
= r[Status]], null})
)),

Key points
• RegionStats: One record per region with revenue, target achievement, year-over-year change, status,
and a small table of monthly revenue.
• List.Split(RegionStats, 3): Splits the regions into groups of three and, for each group, generates a
row container (kpi-row-1, kpi-row-2) and its child KpiCard rows. Names are built from numbers, so
they’re always unique.
• Combine the fixed rows (root, header, and so on) and the generated lists of rows with & to form a single
visualization document.
• Status text (✓ On target, ⚠ Close, ✗ Action needed) takes the place of conditional formatting. You can’t
change colors, but symbols and words convey the state.

21

22.

Dataflow Gen2 data visuals (preview): hands-on guide 115% 109.8% Target achievement 110% 103.6% 105% 100.1% 100.6% Chubu Kansai 98.7% 100% 93.6% 95% 90% 85% 80% Hokkaido-Tohoku Kanto Target achievement (%) Target (100%) Chugoku-Shikoku Kyushu-Okinawa Caution line (95%) Figure 11. Checkpoint: target achievement by region (FY2025), re-created as a Word chart Table 12. Checkpoint: the “Lowest target achievement first” table Region Revenue (¥M) Target achievement (%) YoY (%) Status Hokkaido-Tohoku 492.0 93.6 2.8 ✗ Action needed (below 95%) Chugoku-Shikoku 409.6 98.7 8.5 ⚠ Close (95% or more) Chubu 761.3 100.1 9.4 ✓ On target Kansai 973.7 100.6 9.7 ✓ On target Kanto 1,719.0 103.6 13.6 ✓ On target 637.5 109.8 20.7 ✓ On target Kyushu-Okinawa Hokkaido-Tohoku grew year over year (+2.8%) but missed its target, which was set at 10% above the prior year’s plan, so it’s flagged “✗ Action needed.” Placing KPIs next to trends helps you catch misreadings such as “growing, yet below target.” The visualization document has 31 rows, but the code doesn’t depend on the number of regions. Step 8 A reusable data profile dashboard [10 min] While you prepare data, you often want to check “Are there nulls?” and “Does the distribution look right?” If you create one function that takes any table and returns a profile visualization document, you can attach the same dashboard to any query. 1. Add a blank query, paste 08a_fnProfileDashboard.pq (Appendix C-3), and name it fnProfileDashboard. It’s recognized as a function. 2. Add a blank query, paste the following line, and name it Profile_SalesData. 22

23.

Dataflow Gen2 data visuals (preview): hands-on guide 3. (Optional) Try it on the public Northwind sample, too. Add 08c_NorthwindOrders.pq as NorthwindOrders (if you’re prompted for credentials, choose Anonymous), and then create Profile_Northwind with the code in 08d. Queries: Profile_SalesData and Profile_Northwind | Files: 08b and 08d // Profile_SalesData (Step 8): apply the fnProfileDashboard function to SalesData fnProfileDashboard(SalesData, "SalesData data profile") // Profile_Northwind (Step 8, optional): reuse the same function on another table as is fnProfileDashboard(NorthwindOrders, "Northwind Orders data profile") What’s in the profile dashboard • Header and KPIs: rows × columns, the number of numeric columns, the null rate, and the number of duplicate rows. • Quality charts: nulls per column and distinct values per column (bar charts). • Distributions: histograms for numeric columns (up to six), counted in eight equal-width bins. The bin labels start with ①, ②, and so on to fix the order. Whole-number columns with eight or fewer distinct values, such as fiscal year, are counted per value. • Column profile table: the output of Table.Profile (count, nulls, distinct values, min/max, average, standard deviation), plus quartiles (linear interpolation, the same as Excel’s PERCENTILE.INC) and the number of IQR outliers (below Q1 − 1.5 × IQR or above Q3 + 1.5 × IQR). • Table.Buffer: reads the source only once so repeated aggregations don’t fetch it again. This matters especially for external sources such as OData. Figure 12. Reference: the data profile example in the official documentation (Northwind Orders; source: Microsoft Learn, CC BY 4.0) 23

24.

Dataflow Gen2 data visuals (preview): hands-on guide 800 700 677 Count 600 500 400 300 200 106 100 24 10 4 4 3 2 ③ 252–378 ④ 378–504 ⑤ 504–630 ⑥ 630–756 ⑦ 756–882 ⑧ 882–1,008 0 ① 0.02–126 ② 126–252 Freight bins (8 equal-width bins) Figure 13. Checkpoint: distribution of Freight in Northwind (count), re-created as a Word chart Table 13. Checkpoint: key profile values Item Profile_SalesData Profile_Northwind Header (FarText) 576 rows × 9 columns 830 rows × 9 columns Null rate 0.0% (0 null cells) 7.1% (ShipRegion 507, ShippedDate 21) Duplicate rows 0 0 Distributions Freight quartiles Numeric columns: 5 (FiscalYear shows “① 2024” and “② 2025”) – Numeric columns: 3 (OrderID, EmployeeID, Freight) Q1 13.38, median 41.36, Q3 91.43; 67 outliers The Northwind quartiles and outlier counts match the values in the official documentation’s screenshot. You can attach the same dashboard to your own queries with a single line: fnProfileDashboard(QueryName, "Title"). Step 9 Check with the self-diagnostic tool [5 min] Some mistakes in a visualization document don’t raise an error—the visual simply doesn’t appear (a misspelled Parent, for example). This function checks the rules from the official documentation in M. 1. Add a blank query with the contents of 09a_fnCheckVisualDocument.pq (Appendix C-4), and name it fnCheckVisualDocument. 2. Add the following 09b_Check_All.pq as Check_All. It checks all the visual queries you created and shows the results as a regular table. 24

25.

[beta]

Dataflow Gen2 data visuals (preview): hands-on guide

Query: Check_All | File: M_code_en\09b_Check_All.pq
// Check_All (Step 9): check all the visual queries you created (shows as a regular table)
let
Targets = {
{"Visual_Hello", Visual_Hello},
{"Dashboard", Dashboard},
{"Dashboard_Regions", Dashboard_Regions},
{"Profile_SalesData", Profile_SalesData}
},
Results = List.Transform(Targets, (t) =>
Table.AddColumn(fnCheckVisualDocument(t{1}), "Query", each t{0}, type text)),
Combined = Table.ReorderColumns(Table.Combine(Results),
{"Query", "Severity", "Name", "Rule", "Message"})
in
Combined

Table 14. Main rules that fnCheckVisualDocument checks
Area

Rule

Document

Document

Structure

Structure

Severity

The five columns (Name, Parent, PartType, Properties, Data) exist; exactly one root
row (Parent = null)
Name isn’t null and is unique; no rows are unreachable from the root (for example,
Parent cycles)
Parent matches an existing Name; PartType is one of the six values; Properties is a
record
A Container has at least one child, a Card exactly one; Header, KpiCard, Table, and
Chart have no children
Card Title, Header Header, KpiCard Value and Label are text; FarText and Sub are

Parts

text or null

Charts

Charts

ChartType is one of the six values; AxisColumns is one column; ValueColumns is
one column except for StackedBar
Named columns exist in Data; value columns are numeric; no backslashes in column
names; Data isn’t too large

Error

Error / Warning

Error

Error

Error

Error

Warning / Info

Checkpoint
• All four rows of Check_All (Visual_Hello, Dashboard, Dashboard_Regions, Profile_SalesData) show “OK”.
• If you deliberately change one Parent in Dashboard, the error “Parent must match an existing Name”
appears. Change it back afterward.

5. Share and reuse
You can reuse and share the dashboards and functions you create by using My queries and shared queries in
DFG2. Combined with data visuals, this lets you distribute a team-standard profile view or a view that

25

26.

Dataflow Gen2 data visuals (preview): hands-on guide summarizes the current state. Import it into your other My queries (GA) dataflows from Get data [Add to My Queries] Saves M code only; latest 50 shown Author’s dataflow Shared queries (preview) fnProfileDashboard [Enable sharing (Preview)] Colleagues import a copy from Get data > Shared queries Independent copy; source edits not applied Dashboard, and so on Colleagues view and refresh Share link (preview) the result (no editor) [Get share link (Preview)] Requires Contributor on the workspace Figure 14. Reuse and sharing with My queries and shared queries Table 15. Ways to reuse and share Feature My queries Shared queries (reuse while authoring) Status Generally available Preview Use it to How Keep a personal library; reuse Right-click the query > Add to My Queries. functions and templates in other To import, select Get data > Recents & My dataflows Queries > My Queries filter Let colleagues import a copy of a query into their own dataflows Let others view and refresh a Share link (view) Preview query’s result without opening the editor Right-click > Enable sharing (Preview). Colleagues select it from Get data > Shared queries Right-click > Get share link (Preview) > copy the link and send it • To open a share link during preview, people need Contributor or higher permissions in the workspace that contains the source dataflow. The link itself doesn’t grant access. • The share-link consumption view can show output built with data visuals (the official documentation shows an example with a dataset statistical profile). Select Refresh to update the results. • My queries saves only the M code, not credentials, data destinations, or query attributes. The list shows the latest 50 queries, and you can’t delete them. • Queries imported from shared queries are independent copies. Later changes to the original query aren’t applied to your copy. Recommended usage Save fnProfileDashboard and fnCheckVisualDocument to My queries, and you can add profiling and checks to any dataflow in seconds. For team use, keep them in a dataflow that has sharing enabled. 26

27.

[beta]

Dataflow Gen2 data visuals (preview): hands-on guide

Visuals themselves aren’t output at refresh time. However, the Advanced format of the Excel data destination
(generally available) uses a similar navigation table with PartType and Data columns to produce Excel
workbooks with charts at every refresh. If recurring distribution is your goal, consider it as well.

6. Troubleshooting
The following table summarizes the error messages in the official documentation and the cautions confirmed
while validating this guide. When in doubt, run fnCheckVisualDocument from Step 9.
Table 16. Common symptoms and fixes
Symptom or message

Likely cause

Fix

One of the five columns is missing or

Check the spelling of Name, Parent,

misnamed

PartType, Properties, and Data

Preview.Error: The navigation table must

Zero rows, or two or more rows, have

Keep exactly one root row, and set every

contain exactly one root row.

Parent = null

other row’s Parent to an existing Name

Visual not recognized: "<value>"

Invalid PartType (for example, LineChart)

A regular table appears instead of visuals

Use one of the six PartTypes; for charts,
use "Chart" plus ChartType
Add a child. When you generate rows by

Missing required visual property "cells"

A Container or Card has no child

condition, don’t create the container when
it would be empty

Unexpected number of cells. Expected: 1.
Actual: n

A Card has two or more children

Unexpected result type. Expected: "Text".

A number was passed to KpiCard Value

Actual: "number"

(or a similar property)

Some visuals don’t appear, with no error

The same visual appears twice

A chart is empty, or shows a single
“undefined” category

The whole preview doesn’t render
A stacked bar is grouped incorrectly
Display or editing is slow
No data matches the pRegion value

Parent points to a Name that doesn’t exist
(a typo)

To show several visuals, put one Container
inside the Card
Convert it to text with Number.ToText first

Make Parent and Name match exactly
Make Names unique (for example, add

Duplicate Name

numbers)

An AxisColumns/ValueColumns name isn’t
in Data (after a column rename, for

Match the column names in Data

example)
Properties contains something other than
a record (such as a number)

Always pass a record ([...]) in Properties

A column name contains a backslash (\)

Rename the column

Many visuals, large Data tables, or

Aggregate before passing tables, use

repeated source evaluation

Table.Buffer, and start small

A misspelled parameter value (Step 6)

Select a value from the list

27

28.

Dataflow Gen2 data visuals (preview): hands-on guide 7. Limitations and design tips 7.1 Limitations (during preview) • This feature is in preview and is subject to change. • There are no filters, slicers, date pickers, or cross-filtering between visuals. Each visual shows a snapshot of its Data at evaluation time. • A large number of visuals or large Data tables can slow down authoring. Start with a few visuals and a small number of rows, and then scale up after it works well. • Visuals render on the DFG2 authoring canvas. They aren’t part of the dataflow’s refresh output, data destinations, or the Dataflow Gen2 connector. • For Line and Area charts, a date axis isn’t treated as a continuous timeline. Missing months aren’t filled in, and point spacing doesn’t represent elapsed time. • StackedBar requires wide-format data and has rendering characteristics such as an alphabetical series order, no numeric axis title, and a legend below the chart. • Only six chart types are available. Placement properties from the Excel chart schema (such as Bounds) are accepted but ignored. 7.2 Design tips • Split your code into aggregate → format → arrange (sections 1–5 in this guide’s code). • Pass small aggregated tables to charts, and use display names with units as column names. • Think of every KPI as a set: Value (the big number), Label (what it is), and Sub (comparison or note). • Build grids by nesting: a Container (column) for the whole dashboard, a Container (row) for each row, and Cards for titles. • Use unique alphanumeric IDs for Name, and decide on a naming convention (for example, kpi-…, …card, …-chart) to make maintenance easier. • For elements whose number changes, such as regions or categories, generate the rows with List.Transform and List.Split so that no code changes are needed. • Prefix labels with numbers when their order matters. • Aggregate large sources first to make them small (fold the work to the source when possible), and then use Table.Buffer as needed. • Finish by checking the document with fnCheckVisualDocument. 28

29.

Dataflow Gen2 data visuals (preview): hands-on guide 7.3 Use your own data 1. Replace the contents of the SalesData query with your own data source, or replace SalesData in Dashboard with the name of your query. 2. Match the column names and types (Month, Region, Category, Revenue, Target, GrossProfit, Orders, and so on), or update the aggregation steps and the chart column mappings. 3. The stacked bar series are determined automatically by CategoryNames. If you use a hand-written list instead, update it to match the column names after the pivot. 4. First, check your data with fnProfileDashboard. Finally, check the visuals with fnCheckVisualDocument. 8. References • Create data visuals in Dataflow Gen2 (Preview) (Microsoft Learn) https://learn.microsoft.com/en-us/fabric/data-factory/dataflow-gen2-data-visuals • Fabric September 2026 Feature Summary (Fabric Updates Blog) https://community.fabric.microsoft.com/t5/Fabric-Updates-Blog/Fabric-September-2026-Feature-Summary/ba-p/5325825 • My queries and shared queries in Dataflow Gen2 (Microsoft Learn) https://learn.microsoft.com/en-us/fabric/data-factory/dataflow-gen2-my-queries-shared-queries • Create your first Dataflow Gen2 (Microsoft Learn) https://learn.microsoft.com/en-us/fabric/data-factory/create-first-dataflow-gen2 • Using parameters (Power Query) https://learn.microsoft.com/en-us/power-query/power-query-query-parameters • Use public parameters in Dataflow Gen2 (Microsoft Learn) https://learn.microsoft.com/en-us/fabric/data-factory/dataflow-parameters • Excel Advanced data destination (Microsoft Learn) https://learn.microsoft.com/en-us/fabric/data-factory/dataflow-gen2-data-destinations-excel-advanced • Table.Profile (Power Query M reference) https://learn.microsoft.com/en-us/powerquery-m/table-profile • Number.ToText (Power Query M reference) https://learn.microsoft.com/en-us/powerquery-m/number-totext • Table.Buffer (Power Query M reference) https://learn.microsoft.com/en-us/powerquery-m/table-buffer 29

30.

Dataflow Gen2 data visuals (preview): hands-on guide Appendix A Visualization document quick reference Table 17. Children and inputs by PartType (based on the Schema summary in the official documentation) PartType Children Required inputs Optional inputs Container One or more None Card Exactly one Title: text None Header None (leaf) Header: text FarText: text or null KpiCard None (leaf) Value: text, Label: text Sub: text or null Table None (leaf) Data column: table None ChartType (Line, Area, Bar, StackedBar, Doughnut, ChartTitle: text; Chart None (leaf) or Pie); DataSeries (a record with AxisColumns and DataSeries.PrimaryAxisColumn: ValueColumns); Data column: table text (no effect) Direction: "row" or "column" (default "row") Table 18. Document-level rules Rule What happens if it’s broken Exactly one row has Parent = null (the root). The root can be any PartType Name is unique The whole query fails with Preview.Error No error, but a visual can appear more than once The row and its descendants aren’t rendered, with no Parent is null or exactly matches an existing Name error “Visual not recognized” appears in that row’s place (the PartType is one of the six values rest still renders) A Container has one or more children, a Card exactly one; all others Errors such as “Missing required visual property "cells"” are leaves appear Properties is a record; KpiCard Value and similar properties are text A non-record fails the whole preview; a wrong type shows an error for that row only Appendix B Included files All files are in the DFG2_DataVisuals_Guide\M_code_en folder (UTF-8 text files). Open a file in a text editor such as Notepad, select all, copy, and paste the code into DFG2. 30

31.

Dataflow Gen2 data visuals (preview): hands-on guide Table 19. Files in the M_code_en folder File Query name Purpose 01_SalesData.pq SalesData Step 1: sample data 02_Visual_Hello.pq Visual_Hello Step 2: minimal visual 03_Dashboard_step3_skeleton.pq Dashboard Step 3: header and KPIs 04_Dashboard_step4_charts.pq Dashboard Step 4: charts added 05_Dashboard_step5_complete.pq Dashboard Step 5: complete version (Appendix C-1) 06a_pRegion_parameter.pq pRegion 06b_Dashboard_step6_parameter.pq Dashboard Step 6: parameter-enabled version 07_Dashboard_Regions.pq Dashboard_Regions Step 7: regional scorecard (Appendix C-2) 08a_fnProfileDashboard.pq fnProfileDashboard Step 8: profile function (Appendix C-3) 08b_Profile_SalesData.pq Profile_SalesData Step 8: calls the function 08c_NorthwindOrders.pq NorthwindOrders Step 8 (optional): gets public OData 08d_Profile_Northwind.pq Profile_Northwind Step 8 (optional): calls the function 09a_fnCheckVisualDocument.pq fnCheckVisualDocument Step 9: checker function (Appendix C-4) 09b_Check_All.pq Check_All Step 9: check everything at once Step 6: parameter (not needed if you use the UI) Appendix C Complete code The full text of the queries excerpted in the steps. The content is the same as in the included files. C-1 Dashboard (Step 5, complete) Query: Dashboard | File: M_code_en\05_Dashboard_step5_complete.pq // Dashboard (Step 5, complete): header + KPIs + six chart types + detail table let // ---- 1) Data: latest fiscal year (current) and the prior year ---CurFY = List.Max(SalesData[FiscalYear]), PrevFY = CurFY - 1, Cur = Table.SelectRows(SalesData, each [FiscalYear] = CurFY), Prev = Table.SelectRows(SalesData, each [FiscalYear] = PrevFY), // ---- 2) KPI calculations (numbers) ---Revenue = List.Sum(Cur[Revenue]), RevenuePrev = List.Sum(Prev[Revenue]), Target = List.Sum(Cur[Target]), GrossMargin = List.Sum(Cur[GrossProfit]) / Revenue, GrossMarginPrev = List.Sum(Prev[GrossProfit]) / RevenuePrev, Orders = List.Sum(Cur[Orders]), // ---- 3) KPI display text (KpiCard values must be text) ---Fmt = (n as number, format as text) as text => Number.ToText(n, format, "en-US"), Signed = (n as number, format as text) as text => (if n > 0 then "+" else "") & Fmt(n, format), Pct = (n as number) as text => Fmt(n * 100, "N1") & "%", 31

32.

[beta]

Dataflow Gen2 data visuals (preview): hands-on guide

RevenueText
= "¥" & Fmt(Revenue / 1000000000, "N2") & "B",
RevenueSub
= Signed((Revenue / RevenuePrev - 1) * 100, "N1") & "% vs. prior year",
AchievementText = Pct(Revenue / Target),
AchievementSub = "Target ¥" & Fmt(Target / 1000000000, "N2") & "B",
MarginText
= Pct(GrossMargin),
MarginSub
= Signed((GrossMargin - GrossMarginPrev) * 100, "N1") & " pts vs. prior year",
OrdersText
= Fmt(Orders, "N0"),
OrdersSub
= "Avg. order ¥" & Fmt(Revenue / Orders, "N0"),
// ---- 4) Aggregated tables for the charts and the table visual ---// Column names become axis titles and table headers, so use display names with units
ToMillions = (n as number) as number => Number.Round(n / 1000000, 1),
RevenueByMonth = Table.Sort(
Table.Group(Cur, {"Month"},
{{"Revenue (¥M)", each ToMillions(List.Sum([Revenue])), type number}}),
{{"Month", Order.Ascending}}),
OrdersByMonth = Table.Sort(
Table.Group(Cur, {"Month"},
{{"Orders", each List.Sum([Orders]), type number}}),
{{"Month", Order.Ascending}}),
RevenueByRegion = Table.Sort(
Table.Group(Cur, {"Region"},
{{"Revenue (¥M)", each ToMillions(List.Sum([Revenue])), type number}}),
{{"Revenue (¥M)", Order.Descending}}),
// A stacked bar needs wide data (one column per category): spread them with Table.Pivot
CategoryNames = List.Sort(List.Distinct(Cur[Category])),
RevenueByRegionCategory = Table.Pivot(
Table.Group(Cur, {"Region", "Category"},
{{"Revenue", each ToMillions(List.Sum([Revenue])), type number}}),
CategoryNames, "Category", "Revenue", List.Sum),
RevenueByCategory = Table.Sort(
Table.Group(Cur, {"Category"},
{{"Revenue (¥M)", each ToMillions(List.Sum([Revenue])), type number}}),
{{"Revenue (¥M)", Order.Descending}}),
OrdersByCategory = Table.Sort(
Table.Group(Cur, {"Category"},
{{"Orders", each List.Sum([Orders]), type number}}),
{{"Orders", Order.Descending}}),
// ★Added in Step 5: detail table (region x category, largest revenue first)
DetailTable = Table.Sort(
Table.Group(Cur, {"Region", "Category"}, {
{"Revenue (¥M)", each ToMillions(List.Sum([Revenue])), type number},
{"Target achievement (%)",
each Number.Round(List.Sum([Revenue]) / List.Sum([Target]) * 100, 1), type number},
{"Gross margin (%)",
each Number.Round(List.Sum([GrossProfit]) / List.Sum([Revenue]) * 100, 1), type number},
{"Orders", each List.Sum([Orders]), type number}}),
{{"Revenue (¥M)", Order.Descending}}),
// ---- 5) Visualization document (one row = one visual; Parent builds the hierarchy) ---VisualDocumentType = type table [
Name = nullable text, Parent = nullable text, PartType = nullable text,
Properties = nullable record, Data = any
],
VisualDocument = #table(
VisualDocumentType,
{
// Root (the only row with Parent = null). column = stack children vertically
{"dashboard", null, "Container", [Direction = "column"], null},
{"header", "dashboard", "Header",
[Header = "Contoso Coffee Sales Dashboard",
FarText = "FY" & Text.From(CurFY) & " (vs. prior year)"], null},
// KPI row. row = place children side by side
{"kpi-row", "dashboard", "Container", [Direction = "row"], null},
{"kpi-revenue", "kpi-row", "KpiCard",
[Label = "Revenue", Value = RevenueText, Sub = RevenueSub], null},
{"kpi-achievement", "kpi-row", "KpiCard",
[Label = "Target achievement", Value = AchievementText, Sub = AchievementSub], null},
{"kpi-margin", "kpi-row", "KpiCard",
[Label = "Gross margin", Value = MarginText, Sub = MarginSub], null},
{"kpi-orders", "kpi-row", "KpiCard",
[Label = "Orders", Value = OrdersText, Sub = OrdersSub], null},
// Trends (line and area)
{"trend-row", "dashboard", "Container", [Direction = "row"], null},
{"revenue-trend-card", "trend-row", "Card", [Title = "Monthly revenue trend (¥M)"], null},
{"revenue-trend-chart", "revenue-trend-card", "Chart",

32

33.

[beta]

Dataflow Gen2 data visuals (preview): hands-on guide

[ChartType = "Line",
DataSeries = [AxisColumns = "Month", ValueColumns = "Revenue (¥M)"]],
RevenueByMonth},
{"orders-trend-card", "trend-row", "Card", [Title = "Monthly orders"], null},
{"orders-trend-chart", "orders-trend-card", "Chart",
[ChartType = "Area",
DataSeries = [AxisColumns = "Month", ValueColumns = "Orders"]],
OrdersByMonth},
// Regional comparison (bar and stacked bar)
{"region-row", "dashboard", "Container", [Direction = "row"], null},
{"region-card", "region-row", "Card", [Title = "Revenue by region (¥M)"], null},
{"region-chart", "region-card", "Chart",
[ChartType = "Bar",
DataSeries = [AxisColumns = "Region", ValueColumns = "Revenue (¥M)"]],
RevenueByRegion},
{"region-mix-card", "region-row", "Card",
[Title = "Revenue by region and category (¥M)"], null},
{"region-mix-chart", "region-mix-card", "Chart",
[ChartType = "StackedBar",
DataSeries = [AxisColumns = "Region", ValueColumns = CategoryNames]],
RevenueByRegionCategory},
// Mix (doughnut and pie)
{"mix-row", "dashboard", "Container", [Direction = "row"], null},
{"category-card", "mix-row", "Card", [Title = "Revenue mix by category"], null},
{"category-chart", "category-card", "Chart",
[ChartType = "Doughnut",
DataSeries = [AxisColumns = "Category", ValueColumns = "Revenue (¥M)"]],
RevenueByCategory},
{"orders-mix-card", "mix-row", "Card", [Title = "Order mix by category"], null},
{"orders-mix-chart", "orders-mix-card", "Chart",
[ChartType = "Pie",
DataSeries = [AxisColumns = "Category", ValueColumns = "Orders"]],
OrdersByCategory},
// ★Added in Step 5: detail (a Table as the only child of a Card)
{"detail-card", "dashboard", "Card",
[Title = "Detail by region and category (FY" & Text.From(CurFY) & ")"], null},
{"detail-table", "detail-card", "Table", [], DetailTable}
}
)
in
VisualDocument

C-2 Dashboard_Regions (Step 7)
Query: Dashboard_Regions | File: M_code_en\07_Dashboard_Regions.pq
// Dashboard_Regions (Step 7): KPI cards and charts "generated from the data", one per region
let
CurFY
= List.Max(SalesData[FiscalYear]),
PrevFY
= CurFY - 1,
Fmt
= (n as number, format as text) as text => Number.ToText(n, format, "en-US"),
Pct
= (n as number) as text => Fmt(n * 100, "N1") & "%",
SignedPct = (n as number) as text => (if n > 0 then "+" else "") & Pct(n),
ToMillions = (n as number) as number => Number.Round(n / 1000000, 1),
// ---- 1) One record of metrics per region (follows automatically when regions change) ---Regions = List.Distinct(SalesData[Region]),
RegionStats = List.Transform(List.Positions(Regions), (i) =>
let
region
= Regions{i},
rows
= Table.SelectRows(SalesData, each [Region] = region),
cur
= Table.SelectRows(rows, each [FiscalYear] = CurFY),
prev
= Table.SelectRows(rows, each [FiscalYear] = PrevFY),
revenue
= List.Sum(cur[Revenue]),
achievement = revenue / List.Sum(cur[Target]),
yoy
= revenue / List.Sum(prev[Revenue]) - 1,
// Change the display text by condition (instead of conditional formatting)
status
= if achievement >= 1 then "✓ On target"
else if achievement >= 0.95 then "⚠ Close (95% or more)"
else "✗ Action needed (below 95%)",
monthly
= Table.Sort(
Table.Group(cur, {"Month"},

33

34.

[beta]

Dataflow Gen2 data visuals (preview): hands-on guide

{{"Revenue (¥M)", each ToMillions(List.Sum([Revenue])),
type number}}),
{{"Month", Order.Ascending}})
in
[No = i + 1, Region = region, Revenue = revenue, Achievement = achievement,
YoY = yoy, Status = status, Monthly = monthly]
),
// ---- 2) Three per row: generate row containers and their child rows as lists ---Groups = List.Split(RegionStats, 3),
KpiRows = List.Combine(List.Transform(List.Positions(Groups), (g) =>
{ {"kpi-row-" & Text.From(g + 1), "scorecard", "Container", [Direction = "row"], null} }
& List.Transform(Groups{g}, (r) =>
{"kpi-" & Text.From(r[No]), "kpi-row-" & Text.From(g + 1), "KpiCard",
[Label = r[Region] & ": target achievement",
Value = Pct(r[Achievement]),
Sub
= r[Status]], null})
)),
TrendRows = List.Combine(List.Transform(List.Positions(Groups), (g) =>
{ {"trend-row-" & Text.From(g + 1), "trends", "Container", [Direction = "row"], null} }
& List.Combine(List.Transform(Groups{g}, (r) =>
{
{"trend-card-" & Text.From(r[No]), "trend-row-" & Text.From(g + 1), "Card",
[Title = r[Region] & " (YoY " & SignedPct(r[YoY]) & ")"], null},
{"trend-chart-" & Text.From(r[No]), "trend-card-" & Text.From(r[No]), "Chart",
[ChartType = "Line",
DataSeries = [AxisColumns = "Month", ValueColumns = "Revenue (¥M)"]],
r[Monthly]}
}
))
)),
// ---- 3) Bottom row: YoY bar chart and a list sorted by lowest achievement ---GrowthTable = #table(
type table [Region = text, #"YoY (%)" = number],
List.Transform(RegionStats, (r) => {r[Region], Number.Round(r[YoY] * 100, 1)})
),
StatusTable = Table.Sort(
#table(
type table [
Region = text, #"Revenue (¥M)" = number, #"Target achievement (%)" = number,
#"YoY (%)" = number, Status = text
],
List.Transform(RegionStats, (r) =>
{r[Region], ToMillions(r[Revenue]), Number.Round(r[Achievement] * 100, 1),
Number.Round(r[YoY] * 100, 1), r[Status]})
),
{{"Target achievement (%)", Order.Ascending}}
),
// ---- 4) Visualization document: fixed rows & generated rows, combined with & ---VisualDocumentType = type table [
Name = nullable text, Parent = nullable text, PartType = nullable text,
Properties = nullable record, Data = any
],
VisualDocument = #table(
VisualDocumentType,
{
{"root", null, "Container", [Direction = "column"], null},
{"header", "root", "Header",
[Header = "Regional scorecard",
FarText = "FY" & Text.From(CurFY) & " | "
& Text.From(List.Count(Regions)) & " regions"],
null},
{"scorecard", "root", "Container", [Direction = "column"], null}
}
& KpiRows
& { {"trends", "root", "Container", [Direction = "column"], null} }
& TrendRows
& {
{"bottom-row", "root", "Container", [Direction = "row"], null},
{"growth-card", "bottom-row", "Card", [Title = "YoY growth by region (%)"], null},
{"growth-chart", "growth-card", "Chart",
[ChartType = "Bar",
DataSeries = [AxisColumns = "Region", ValueColumns = "YoY (%)"]],
GrowthTable},
{"status-card", "bottom-row", "Card", [Title = "Lowest target achievement first"], null},
{"status-table", "status-card", "Table", [], StatusTable}
}

34

35.

[beta]

Dataflow Gen2 data visuals (preview): hands-on guide

)
in
VisualDocument

C-3 fnProfileDashboard (Step 8)
Query: fnProfileDashboard | File: M_code_en\08a_fnProfileDashboard.pq
// fnProfileDashboard (Step 8): pass any table and get a "data profile" dashboard back
// Usage: fnProfileDashboard(SalesData, "SalesData data profile")
(Source as table, optional Title as nullable text) as table =>
let
Title_ = if Title = null then "Data profile" else Title,
Fmt
= (n as number, format as text) as text => Number.ToText(n, format, "en-US"),
Pct
= (n as number) as text => Fmt(n * 100, "N1") & "%",
// ---- 0) Skip columns that can't be displayed (tables, records, lists, and so on) ---ExcludedKinds = {"table", "record", "list", "function", "binary", "type", "action"},
ScalarColumns = Table.SelectRows(Table.Schema(Source),
each not List.Contains(ExcludedKinds, [Kind]))[Name],
ExcludedCount = Table.ColumnCount(Source) - List.Count(ScalarColumns),
// Table.Buffer: read the source once and keep it in memory (prevents re-evaluation)
Src
= Table.Buffer(Table.SelectColumns(Source, ScalarColumns)),
Schema
= Table.Buffer(Table.SelectColumns(Table.Schema(Src), {"Name", "Kind"})),
KindOf
= (col as text) as text => Table.SelectRows(Schema, each [Name] = col){0}[Kind],
RowCount
= Table.RowCount(Src),
ColumnCount = List.Count(ScalarColumns),
// ---- 1) Basic statistics per column (count, nulls, distinct, min/max, average, std dev) ---Profile
= Table.Buffer(Table.Profile(Src)),
NullCells
= if Table.IsEmpty(Profile) then 0 else List.Sum(Profile[NullCount]),
TotalCells
= RowCount * ColumnCount,
NullRate
= if TotalCells = 0 then null else NullCells / TotalCells,
DuplicateRows = RowCount - Table.RowCount(Table.Distinct(Src)),
// ---- 2) Numeric columns: quartiles (like Excel's PERCENTILE.INC) and IQR outliers ---IsNumeric = (col as text) as logical =>
let
kind
= KindOf(col),
nonNull = List.RemoveNulls(Table.Column(Src, col))
in
List.Count(nonNull) > 0
and (kind = "number"
or (kind = "any"
and List.AllTrue(List.Transform(nonNull, each Value.Is(_, type number))))),
NumericColumns = List.Buffer(List.Select(ScalarColumns, IsNumeric)),
Quantile = (sorted as list, p as number) as number =>
let
h = (List.Count(sorted) - 1) * p,
lo = Number.RoundDown(h),
hi = Number.RoundUp(h)
in
sorted{lo} + (h - lo) * (sorted{hi} - sorted{lo}),
NumericStats = List.Buffer(List.Transform(NumericColumns, (col) =>
let
sorted = List.Buffer(List.Sort(List.RemoveNulls(Table.Column(Src, col)))),
q1
= Quantile(sorted, 0.25),
median = Quantile(sorted, 0.5),
q3
= Quantile(sorted, 0.75),
iqr
= q3 - q1,
lower = q1 - 1.5 * iqr,
upper = q3 + 1.5 * iqr
in
[Column = col, Q1 = q1, Median = median, Q3 = q3, IQR = iqr,
Outliers = List.Count(List.Select(sorted, each _ < lower or _ > upper))]
)),
NoStats = [Q1 = null, Median = null, Q3 = null, IQR = null, Outliers = null],
StatsFor = (col as text) as record =>
let found = List.Select(NumericStats, each [Column] = col)
in if List.IsEmpty(found) then NoStats else found{0},
// ---- 3) Tables for display (column names become headers) ---Round2 = (v as any) as any =>
if v <> null and Value.Is(v, type number) then Number.Round(v, 2) else null,
ToDisplay = (v as any) as nullable text =>

35

36.

[beta]

Dataflow Gen2 data visuals (preview): hands-on guide

if v = null then null
else if Value.Is(v, type number) then Fmt(v, if Number.Round(v) = v then "N0" else "N2")
else Text.From(v, "en-US"),
ProfileTable = Table.FromRecords(
List.Transform(Table.ToRecords(Profile), (r) =>
let s = StatsFor(r[Column]) in
[Column = r[Column], Type = KindOf(r[Column]), Count = r[Count], Nulls = r[NullCount],
Distinct = r[DistinctCount],
Min = ToDisplay(r[Min]), Max = ToDisplay(r[Max]),
Average = Round2(r[Average]), #"Std dev" = Round2(r[StandardDeviation]),
Q1 = Round2(s[Q1]), Median = Round2(s[Median]), Q3 = Round2(s[Q3]),
IQR = Round2(s[IQR]), Outliers = s[Outliers]]),
type table [
Column = text, Type = text, Count = number, Nulls = number, Distinct = number,
Min = nullable text, Max = nullable text,
Average = nullable number, #"Std dev" = nullable number,
Q1 = nullable number, Median = nullable number, Q3 = nullable number,
IQR = nullable number, Outliers = nullable number
]
),
NullChart = Table.FromRecords(
List.Transform(Table.ToRecords(Profile), (r) => [Column = r[Column], Nulls = r[NullCount]]),
type table [Column = text, Nulls = number]),
DistinctChart = Table.FromRecords(
List.Transform(Table.ToRecords(Profile),
(r) => [Column = r[Column], #"Distinct values" = r[DistinctCount]]),
type table [Column = text, #"Distinct values" = number]),
// ---- 4) Histograms of numeric columns (8 equal-width bins), numbered to fix the order ---Circled = {"①", "②", "③", "④", "⑤", "⑥", "⑦", "⑧"},
Compact = (x as number) as text =>
let a = Number.Abs(x) in
if a >= 1000000000 then Fmt(x / 1000000000, "N1") & "B"
else if a >= 1000000 then Fmt(x / 1000000, "N1") & "M"
else if a >= 10000 then Fmt(x / 1000, "N0") & "K"
else if a >= 100 or Number.Round(x) = x then Fmt(x, "N0")
else Fmt(x, "N2"),
Histogram = (col as text) as table =>
let
values
= List.Buffer(List.RemoveNulls(Table.Column(Src, col))),
distinct = List.Buffer(List.Sort(List.Distinct(values))),
bins
= 8,
// Whole-number columns with 8 or fewer distinct values (years, codes): count per value
discrete = List.Count(distinct) <= bins
and List.AllTrue(List.Transform(distinct, each Number.Round(_) = _)),
mn
= List.Min(values),
mx
= List.Max(values),
width
= (mx - mn) / bins,
binOf
= (v as number) as number =>
List.Min({bins - 1, Number.RoundDown((v - mn) / width)}),
idx
= List.Buffer(List.Transform(values, binOf)),
rows =
if discrete then
List.Transform(List.Positions(distinct), (i) =>
{Circled{i} & " " & Text.From(distinct{i}),
List.Count(List.Select(values, each _ = distinct{i}))})
else if width = 0 then
{{Circled{0} & " " & Compact(mn), List.Count(values)}}
else
List.Transform({0 .. bins - 1}, (b) =>
{Circled{b} & " " & Compact(mn + b * width) & "–"
& Compact(mn + (b + 1) * width),
List.Count(List.Select(idx, each _ = b))})
in
#table(type table [Bin = text, Count = number], rows),
DistColumns = List.FirstN(NumericColumns, 6),
// distribution charts for up to 6 columns
DistGroups = List.Split(DistColumns, 3),
// three per row
DistRows =
if List.IsEmpty(DistColumns) then {}
// never create an empty Container
else
{ {"dist", "root", "Container", [Direction = "column"], null} }
& List.Combine(List.Transform(List.Positions(DistGroups), (g) =>
{ {"dist-row-" & Text.From(g + 1), "dist", "Container", [Direction = "row"], null} }
& List.Combine(List.Transform(List.Positions(DistGroups{g}), (k) =>
let
col = DistGroups{g}{k},
id = Text.From(g * 3 + k + 1)
in
{
{"dist-card-" & id, "dist-row-" & Text.From(g + 1), "Card",
[Title = col & " distribution"], null},
{"dist-chart-" & id, "dist-card-" & id, "Chart",
[ChartType = "Bar",
DataSeries = [AxisColumns = "Bin", ValueColumns = "Count"]],
Histogram(col)}

36

37.

[beta]

Dataflow Gen2 data visuals (preview): hands-on guide

}
))
)),
// ---- 5) Visualization document ---FarText = Fmt(RowCount, "N0") & " rows × " & Text.From(ColumnCount) & " columns"
& (if ExcludedCount > 0 then " (" & Text.From(ExcludedCount) & " excluded)" else ""),
VisualDocumentType = type table [
Name = nullable text, Parent = nullable text, PartType = nullable text,
Properties = nullable record, Data = any
],
VisualDocument = #table(
VisualDocumentType,
{
{"root", null, "Container", [Direction = "column"], null},
{"header", "root", "Header", [Header = Title_, FarText = FarText], null},
{"kpi-row", "root", "Container", [Direction = "row"], null},
{"kpi-rows", "kpi-row", "KpiCard",
[Label = "Rows", Value = Fmt(RowCount, "N0"), Sub = null], null},
{"kpi-cols", "kpi-row", "KpiCard",
[Label = "Columns", Value = Fmt(ColumnCount, "N0"),
Sub = "Numeric columns: " & Text.From(List.Count(NumericColumns))], null},
{"kpi-null", "kpi-row", "KpiCard",
[Label = "Null rate", Value = if NullRate = null then "–" else Pct(NullRate),
Sub = Fmt(NullCells, "N0") & " null cells"], null},
{"kpi-dup", "kpi-row", "KpiCard",
[Label = "Duplicate rows", Value = Fmt(DuplicateRows, "N0"),
Sub = "Identical in all columns"], null},
{"quality-row", "root", "Container", [Direction = "row"], null},
{"null-card", "quality-row", "Card", [Title = "Nulls by column"], null},
{"null-chart", "null-card", "Chart",
[ChartType = "Bar", DataSeries = [AxisColumns = "Column", ValueColumns = "Nulls"]],
NullChart},
{"distinct-card", "quality-row", "Card", [Title = "Distinct values by column"], null},
{"distinct-chart", "distinct-card", "Chart",
[ChartType = "Bar",
DataSeries = [AxisColumns = "Column", ValueColumns = "Distinct values"]],
DistinctChart}
}
& DistRows
& {
{"profile-card", "root", "Card",
[Title = "Column profile (count, nulls, min/max, quartiles, outliers)"], null},
{"profile-table", "profile-card", "Table", [], ProfileTable}
}
)
in
VisualDocument

C-4 fnCheckVisualDocument (Step 9)
Query: fnCheckVisualDocument | File: M_code_en\09a_fnCheckVisualDocument.pq
// fnCheckVisualDocument (Step 9): check a visualization document against the documented rules
// Usage: fnCheckVisualDocument(Dashboard)
//
-> returns the issues as a regular table (a single "OK" row when there are none)
(Doc as table) as table =>
let
Required
= {"Name", "Parent", "PartType", "Properties", "Data"},
PartTypes = {"Container", "Card", "Header", "KpiCard", "Table", "Chart"},
LeafTypes = {"Header", "KpiCard", "Table", "Chart"},
ChartTypes = {"Line", "Area", "Bar", "StackedBar", "Doughnut", "Pie"},
ResultType = type table [Severity = text, Name = nullable text, Rule = text, Message = text],
Issue = (severity as text, name as nullable text, rule as text, message as text) as list =>
{severity, name, rule, message},
MissingColumns = List.Difference(Required, Table.ColumnNames(Doc)),
// List.Buffer: evaluate once and keep in memory
Rows = List.Buffer(
if List.IsEmpty(MissingColumns)
then Table.ToRecords(Table.SelectColumns(Doc, Required)) else {}),
Names = List.Buffer(List.Transform(Rows, each [Name])),

37

38.

[beta]

Dataflow Gen2 data visuals (preview): hands-on guide

IsText
= (v as any) as logical => Value.Is(v, type text),
ChildCount = (name as any) as number =>
List.Count(List.Select(Rows, each [Parent] <> null and [Parent] = name)),
AsList
= (v as any) as any =>
if Value.Is(v, type text) then {v} else if Value.Is(v, type list) then v else null,
Txt = (v as any) as text =>
if v = null then "null"
else if Value.Is(v, type text) then v
else try Text.From(v) otherwise
"(" & (if Value.Is(v, type record) then "record"
else if Value.Is(v, type list) then "list"
else if Value.Is(v, type table) then "table" else "value") & ")",
// ---- Document-level rules ---RootRows
= List.Select(Rows, each [Parent] = null),
Duplicates = List.Select(List.Distinct(List.RemoveNulls(Names)),
(n) => List.Count(List.Select(Names, each _ = n)) > 1),
DocIssues =
(if List.Count(RootRows) <> 1
then {Issue("Error", null, "Exactly one root row",
Text.From(List.Count(RootRows))
& " rows have Parent = null (the whole preview fails)")}
else {})
& List.Transform(List.Select(Rows, each [Name] = null), (r) =>
Issue("Error", null, "Name is required",
"A row has a null Name (PartType: " & Txt(r[PartType]) & ")"))
& List.Transform(Duplicates, (n) =>
Issue("Warning", n, "Name must be unique",
"Several rows share this Name (visuals can appear more than once)")),
// ---- Rows that can't be reached from the root (for example, a Parent cycle) ---RootName = if List.Count(RootRows) = 1 then RootRows{0}[Name] else null,
Reachable =
if RootName = null then {}
else List.Last(List.Generate(
() => [seen = {RootName}, frontier = {RootName}],
each not List.IsEmpty([frontier]),
(s) =>
let
children = List.Transform(
List.Select(Rows, (r) => List.Contains(s[frontier], r[Parent])),
each [Name]),
next = List.Difference(List.Distinct(children), s[seen])
in
[seen = s[seen] & next, frontier = next],
each [seen])),
UnreachableIssues =
if RootName = null then {}
else List.Transform(
List.Select(Rows, (r) =>
r[Parent] <> null and List.Contains(Names, r[Parent])
and not List.Contains(Reachable, r[Name])),
(r) => Issue("Warning", r[Name], "Reachable from the root",
"Not rendered because it can't be reached from the root"
& " (for example, a Parent cycle)")),
// ---- Chart rules ---CheckChart = (name as any, p as record, data0 as any) as list =>
let
data
= if Value.Is(data0, type table) then Table.Buffer(data0) else data0,
chartType = Record.FieldOrDefault(p, "ChartType", null),
series
= Record.FieldOrDefault(p, "DataSeries", null),
seriesOk = Value.Is(series, type record),
axis
= if seriesOk then AsList(Record.FieldOrDefault(series, "AxisColumns", null))
else null,
values
= if seriesOk then AsList(Record.FieldOrDefault(series, "ValueColumns", null))
else null,
axisOk
= axis <> null and List.Count(axis) = 1
and List.AllTrue(List.Transform(axis, IsText)),
valuesOk = values <> null and not List.IsEmpty(values)
and List.AllTrue(List.Transform(values, IsText)),
isTable
= Value.Is(data, type table),
columns
= if isTable then Table.ColumnNames(data) else {},
named
= (if axisOk then axis else {}) & (if valuesOk then values else {}),
missing
= if isTable
then List.Distinct(List.Select(named, (c) => not List.Contains(columns, c)))
else {},
nonNumeric =
if isTable and valuesOk
then List.Select(List.Intersect({values, columns}), (c) =>
not List.AllTrue(List.Transform(List.RemoveNulls(Table.Column(data, c)),

38

39.

[beta]

Dataflow Gen2 data visuals (preview): hands-on guide

each Value.Is(_, type number))))
else {}
in
(if not List.Contains(ChartTypes, chartType)
then {Issue("Error", name, "ChartType must be supported",
"Unsupported ChartType: " & Txt(chartType)
& " (use Line, Area, Bar, StackedBar, Doughnut, or Pie)")} else {})
& (if not seriesOk
then {Issue("Error", name, "DataSeries is required",
"Properties has no DataSeries record")} else {})
& (if seriesOk and not axisOk
then {Issue("Error", name, "AxisColumns is one column",
"Specify exactly one column name (text) in AxisColumns")} else {})
& (if seriesOk and not valuesOk
then {Issue("Error", name, "ValueColumns are column names",
"Specify a column name (text) or a list of column names in ValueColumns")}
else {})
& (if valuesOk and chartType <> "StackedBar" and List.Count(values) > 1
then {Issue("Error", name, "One value column (except StackedBar)",
Txt(chartType) & " accepts only one ValueColumns column")} else {})
& List.Transform(missing, (c) =>
Issue("Warning", name, "Columns exist in Data",
"Column '" & c & "' isn't in the Data table (can cause an empty chart)"))
& List.Transform(nonNumeric, (c) =>
Issue("Warning", name, "Value columns are numeric",
"Column '" & c & "' contains non-numeric values"))
& (if chartType = "StackedBar"
and List.AnyTrue(List.Transform(named, each Text.Contains(_, "\")))
then {Issue("Warning", name, "No backslash in column names",
"A stacked bar column name contains \")} else {})
& (if isTable and Table.RowCount(data) > 1000
then {Issue("Info", name, "Keep Data small",
"Data has " & Text.From(Table.RowCount(data))
& " rows. Aggregate to fewer rows for faster authoring")} else {}),
// ---- Row-level rules ---CheckRow = (r as record) as list =>
let
name
= r[Name],
partType = r[PartType],
props
= r[Properties],
data
= r[Data],
propsBad = props <> null and not Value.Is(props, type record),
p
= if Value.Is(props, type record) then props else [],
get
= (field as text) as any => Record.FieldOrDefault(p, field, null),
kids
= ChildCount(name),
// Structure rules (hierarchy, PartType, Data)
Structure =
(if r[Parent] <> null and not List.Contains(Names, r[Parent])
then {Issue("Error", name, "Parent must match an existing Name",
"Parent '" & Txt(r[Parent])
& "' not found (this row and its descendants aren't rendered)")} else {})
& (if not List.Contains(PartTypes, partType)
then {Issue("Error", name, "PartType must be supported",
"Visual not recognized: " & Txt(partType))} else {})
& (if propsBad
then {Issue("Error", name, "Properties must be a record",
"Properties isn't a record (the whole preview fails to render)")}
else {})
& (if partType = "Container" and kids = 0
then {Issue("Error", name, "Container needs at least one child",
"Missing required visual property ""cells"" (no children)")} else {})
& (if partType = "Card" and kids <> 1
then {Issue("Error", name, "Card needs exactly one child",
"Number of children: " & Text.From(kids))} else {})
& (if List.Contains(LeafTypes, partType) and kids > 0
then {Issue("Error", name, "Leaf visuals can't have children",
Txt(partType) & " has " & Text.From(kids) & " child rows")}
else {})
& (if List.Contains({"Table", "Chart"}, partType) and not Value.Is(data, type table)
then {Issue("Error", name, "Data must be a table",
"The Data of this " & Txt(partType) & " isn't a table")} else {}),
// Rules for the contents of Properties (only when Properties is a record)
PropertyRules =
if propsBad then {}
else
(if partType = "Container" and get("Direction") <> null
and not List.Contains({"row", "column"}, get("Direction"))
then {Issue("Warning", name, "Direction is row or column",
"Direction value: " & Txt(get("Direction")))} else {})
& (if partType = "Card" and not IsText(get("Title"))

39

40.

[beta]

Dataflow Gen2 data visuals (preview): hands-on guide

then {Issue("Error", name, "Card needs a Title (text)",
"Title isn't text")} else {})
& (if partType = "Header" and not IsText(get("Header"))
then {Issue("Error", name, "Header needs Header (text)",
"Header isn't text")} else {})
& (if partType = "Header" and get("FarText") <> null and not IsText(get("FarText"))
then {Issue("Error", name, "FarText is text or null",
"FarText has an invalid type")} else {})
& (if partType = "KpiCard" and not (IsText(get("Value")) and IsText(get("Label")))
then {Issue("Error", name, "KpiCard Value and Label are text",
"Unexpected result type (convert numbers with Number.ToText)")}
else {})
& (if partType = "KpiCard" and get("Sub") <> null and not IsText(get("Sub"))
then {Issue("Error", name, "Sub is text or null", "Sub has an invalid type")}
else {})
& (if partType = "Chart" then CheckChart(name, p, data) else {})
in
Structure & PropertyRules,
AllIssues =
if not List.IsEmpty(MissingColumns)
then {Issue("Error", null, "The five required columns",
"Missing columns: " & Text.Combine(MissingColumns, ", ")
& " (renders as a regular table, not visuals)")}
else DocIssues & UnreachableIssues & List.Combine(List.Transform(Rows, CheckRow)),
Result =
if List.IsEmpty(AllIssues)
then #table(ResultType, {{"OK", null, "All rules",
"No issues found (" & Text.From(List.Count(Rows)) & " rows)"}})
else #table(ResultType, AllIssues)
in
Result

40

DFG2_Data_Visuals_Hands-on_Guide_EN

Eiki Sui

関連スライド

DAX_Studio

Modeling_Fundamentals

Copilot in Fabric

Power BIの進化_Fabricの登場_202504

A03_Power BIの進化_Fabricの登場

Fabric_Roadshow_E2E_Demo_JP

各ページのテキスト