[Data Analysis & BI]Data Models & Power Query ETL with AI

Summary: Let AI design your data model, then use Power Query to scrub dirty data into clean tables ready for modeling.

1. Pain Point Introduction

Have you ever run into a spreadsheet like this: in a single Excel worksheet, order dates, product names, regions, sales amounts, and quantities are all crammed together; the amount column is text with symbols like "¥12,345"; and region and product names repeat row after row. You want to analyze it with a PivotTable (the Excel table where you drag fields to summarize), but it's painfully slow — and to cross-summarize by "Province" and "Category" you have to stitch things together with all kinds of VLOOKUPs.

What's worse, you probably have a vague sense that "I should split the table up and build a model," but you don't know how to split it, into how many tables, or which connects to which. Cleaning hundreds of rows by hand with copy-paste, your eyes go blurry and mistakes are easy to make.

In this article, we'll use AI to handle the "table-splitting design" half of the job, then use Power Query (the "Get & Transform Data" cleaning tool in Excel / Power BI) to scrub the dirty data into clean tables ready for modeling. Once cleaned, you can breeze through PivotTables and write measures.

2. Target Output

By the end of this article, you'll have three things in hand:

An AI-designed star schema: clearly showing which is the fact table, which are the dimension tables, and how primary and foreign keys connect.
A piece of real, runnable M code: a complete ETL (ETL = Extract, Transform, Load — the whole routine of cleaning raw data before moving it into the model) that does "append → change type → conditional column → custom column → merge queries."
A clean data model: ready to drop straight into a PivotTable or DAX measure — no lag, no mess.

Environment assumption: Excel 2016 and above / Excel 365 / Power BI Desktop all work. All the M code below behaves consistently across these versions (M is the formula language behind Power Query — every click you make in the UI is actually translated into a snippet of M code).

3. Case Study

Let's take a "sales data" set as our example. The original situation: three months of sales details sit in three separate Excel tables (January, February, March), with fields: Order Date, Order ID, Product ID, Region ID, Sales Amount, Quantity. The sales amount is text like "¥12,345", and Product ID and Region ID are also text. Product names/categories and region names/provinces live in two separate dimension tables.

3.1 Step 1: Let AI design your star schema

Don't rush to write code. A star schema = one fact table in the middle, with several dimension tables linked around it — looking down from above, it resembles the rays of a star. Why use it? Because the fact table holds only "the numbers of each transaction + the linking keys (foreign keys)," while the dimension table holds "the angles from which you view the data" (product, region, etc. — these are just example angles). This keeps tables smaller and queries faster, making PivotTables and measures easier to write.

Copy the following straight into any AI:

Prompt: I have a sales dataset. The raw fields are: Order Date, Order ID, Product ID, Region ID, Sales Amount, Quantity; there are also a Product dimension table (Product ID, Product Name, Category) and a Region dimension table (Region ID, Region, Province). Please design a star schema for me: ① identify which are the fact table and dimension tables; ② list the fields of each table, marking primary keys (PK) and foreign keys (FK); ③ explain in one sentence how the tables connect.

The AI will most likely give you a design like this (let's check it for correctness):

Sales Fact Table (fact table): Order ID (PK), Order Date, Product ID (FK), Region ID (FK), Sales Amount, Quantity, Sales Grade, Unit Price

Note: For simplicity in this article, the order date (OrderDate) stays in the fact table for now; we do not build a separate date dimension table. The proper approach is to build a separate Date dimension table and link it — left as an L4 extension.

Product Dimension Table (dimension table): Product ID (PK), Product Name, Category
Region Dimension Table (dimension table): Region ID (PK), Region, Province

Join relationships: Fact Table.Product ID → Product Dimension Table.Product ID; Fact Table.Region ID → Region Dimension Table.Region ID.

Grain assumption: one row in the fact table = one order (keyed by Order ID), and one row in a dimension table = one product / one region; dimensions are all linked by "ID," and product names and region names are not stuffed directly into the fact table — this is exactly the "thin fact, fat dimension" intent of the star schema.

With this list in hand, we know how many queries to build in Power Query and which merges which.

3.2 Step 2: Use Power Query for ETL cleaning

Open Excel → Data → From Table/Range (or Get Data), and turn all three tables into Power Query queries. Below, every step comes with real M code — you can paste it into the Power Query Advanced Editor, or click it out through the UI.

(1) Append queries: stack the three months into one

"Append queries" in M is Table.Combine — it stacks tables with the same structure on top of each other:

powerquery
let
    // Source: stack the three monthly sales queries (Chinese query names kept as-is)
    Source = Table.Combine({一月销售, 二月销售, 三月销售})
in
    Source

Key point: the tables being combined must have identical column names, column counts, and order, otherwise they'll be misaligned or throw an error.

(2) Set data types: scrub "text numbers" into real numbers

The raw sales amount carries "¥" and commas, and the IDs are text — strip the symbols first, then change the types:

powerquery
let
    // Source: stack the three monthly sales queries (Chinese query names kept as-is)
    Source = Table.Combine({一月销售, 二月销售, 三月销售}),
    // First strip the currency symbol, thousands commas, and spaces (incl. full-width) from the amount, so converting to number won't error
    CleanAmount = Table.TransformColumns(Source, {
        {"销售额", each Text.Trim(Text.Replace(Text.Replace(Text.Replace(Text.Replace(_, "¥", ""), ",", ""), " ", ""), "　", "")), type text}
    }),
    // Then change every column to its proper type
    ChangedType = Table.TransformColumnTypes(CleanAmount, {
        {"订单日期", type date},
        {"产品编号", Int64.Type},
        {"地区编号", Int64.Type},
        {"销售额", type number},
        {"数量", Int64.Type}
    })
in
    ChangedType

Key point: change types after cleaning and before any arithmetic. If types are wrong, the division and conditional logic later all break. Note: the above assumes the "Sales Amount" column is text on original import; if some cells are already real numbers, Text.Replace will error — wrap with try first, or convert everything to type text before processing. Note: the ID columns (Product ID, Region ID, Quantity) need to become Int64.Type, on the premise that they are themselves pure numeric text; if an ID contains letters (e.g., P001), converting to Int64.Type will error — keep them uniformly as type text, and make sure the ID columns on both the fact and dimension sides have matching types (all text or all Int64), otherwise the later "merge queries" won't connect.

(3) Add a conditional column: grade by sales amount

The Table.AddConditionalColumn generated behind the UI's "Add Conditional Column" button is an undocumented internal function (its parameter order isn't guaranteed, and hand-writing it is easy to trip over). For hand-written M, prefer the officially documented Table.AddColumn + each if equivalent — conditions are evaluated in order and the first match wins:

powerquery
let
    Source = ChangedType,
    // Add a "Sales Grade" column (Chinese column name kept): conditions short-circuit top-down
    AddedGrade = Table.AddColumn(Source, "销售等级", each
        if [销售额] >= 10000 then "A级"
        else if [销售额] >= 5000 then "B级"
        else "C级", type text)
in
    AddedGrade

Key point: conditions short-circuit top-to-bottom, so >=10000 must come before >=5000, otherwise all big orders get classified as Grade B.

(4) Add a custom column: calculate unit price

Table.AddColumn is the M implementation of "Custom Column," and the second argument is an each expression:

powerquery
let
    Source = AddedGrade,
    // Add a "Unit Price" column (Chinese column name kept); divide, falling back to null on error
    AddedUnitPrice = Table.AddColumn(Source, "单价", each try [销售额] / [数量] otherwise null, type number)
in
    AddedUnitPrice

Note: here "Unit Price" is a "derived column" computed at the ETL stage with a Power Query custom column and stored with the table — good for "fixed per-row derived" attributes. If you want an aggregate that changes live with filtering (e.g., "average unit price changes with the slicer"), a DAX measure fits better (see B22) — don't confuse the two.

(5) Merge queries: bring dimension info into the fact table

"Merge queries" in M takes two steps — first Table.NestedJoin brings the dimension table in as a nested column, then Table.ExpandTableColumn expands the fields you want:

powerquery
let
    Source = AddedUnitPrice,
    // Left-join the product dimension on 产品编号, producing a nested table column named "产品信息"
    JoinedProduct = Table.NestedJoin(Source, {"产品编号"}, 产品维度, {"产品编号"}, "产品信息", JoinKind.LeftOuter),
    // Expand only the two columns we need: 产品名 and 类别
    ExpandedProduct = Table.ExpandTableColumn(JoinedProduct, "产品信息", {"产品名", "类别"}, {"产品名", "类别"}),
    // Join the region dimension the same way
    JoinedRegion = Table.NestedJoin(ExpandedProduct, {"地区编号"}, 地区维度, {"地区编号"}, "地区信息", JoinKind.LeftOuter),
    ExpandedRegion = Table.ExpandTableColumn(JoinedRegion, "地区信息", {"地区", "省份"}, {"地区", "省份"})
in
    ExpandedRegion

Key point: before merging, the data types of the join keys on both sides must match (text "1" ≠ number 1), otherwise it won't connect or yields nulls. JoinKind.LeftOuter means "keep all rows of the left table" — the standard way to fill dimensions into a fact table.

String the snippets above together and you get one complete "Sales Fact" query. The two dimension tables — Product and Region — each load via Excel.CurrentWorkbook(){[Name="产品表"]}[Content] and then get their types fixed (code in the "Appendix" below).

(Appendix) M for the two dimension tables (minimal version)

powerquery
// Query name: 产品维度 (Product Dimension; Chinese query name kept as-is)
let
    Source = Excel.CurrentWorkbook(){[Name="产品表"]}[Content],
    ChangedType = Table.TransformColumnTypes(Source, {
        {"产品编号", Int64.Type}, {"产品名", type text}, {"类别", type text}
    })
in
    ChangedType

// Query name: 地区维度 (Region Dimension; same structure)
let
    Source = Excel.CurrentWorkbook(){[Name="地区表"]}[Content],
    ChangedType = Table.TransformColumnTypes(Source, {
        {"地区编号", Int64.Type}, {"地区", type text}, {"省份", type text}
    })
in
    ChangedType

Key point: Excel.CurrentWorkbook(){[Name="..."]}[Content] requires that the range in Excel is defined as a "Table" (select the range and press Ctrl+T), not a plain cell range — otherwise it can't be retrieved.

(6) You can also let AI write the M directly

If you'd rather not hand-write it, throw the requirements together with the table structure at the AI:

Prompt: Please write a snippet of Power Query M code: the source is Table.Combine({一月销售, 二月销售, 三月销售}); first remove the ¥, commas, and spaces from the "Sales Amount" column; then change Order Date to type date, Product ID / Region ID / Quantity to Int64.Type, and Sales Amount to type number; then add a conditional column "Sales Grade" (>=10000 is Grade A, >=5000 is Grade B, otherwise Grade C); then add a custom column "Unit Price" = Sales Amount / Quantity; finally use Product ID to merge "Product Dimension" with LeftOuter and expand Product Name and Category. Output only M code with Chinese comments.

Always run the code you get in Power Query before trusting it — AI occasionally misspells a function name, and the "Pitfall Guide" below explains how to verify quickly.

4. Principle Summary

Power Query's M language is a "declarative" language: you don't write "how to loop through every row," but a long chain of steps, where each step takes in a table and emits a new one — the output of one step becomes the input of the next, strung together like a pipeline. The star schema is the "destination" of this cleaning — it moves the repetitive text (product names, region names) out of the bulky fact table into compact dimension tables, leaving the fact table with only numbers and linking keys (FK). The model gets smaller and the relationships clearer, so PivotTables and DAX (Data Analysis Expressions — the language for writing "total / share / year-over-year" formulas on the model) run fast and accurate.

5. Pitfall Guide

Merge returns no data? Check the join key type first. Text "1" and number 1 are not the same thing in M's eyes — before merging, make sure both key columns have matching types (both Int64.Type or both kept as type text).
Append errors or misaligns? Check the column names. Table.Combine requires the merged tables to have identical column names, counts, and order; miss one and column A's data lands in column B.
Conditional column grades all wrong? Check the order. Table.AddConditionalColumn short-circuits top-to-bottom, so the wider condition (e.g., >=10000) must come before the narrower one (e.g., >=5000).
Type change says "cannot convert"? Clean the dirty characters first. The "¥", commas, and full-width spaces in amounts directly cause the type number conversion to fail — remember to strip them first with Text.Replace/Text.Trim.
Renamed a column and everything after turns red? Update the references in sync. M is sequentially dependent — if you rename "Sales Amount" to "Amount" up front, every later [销售额] will flag "column not found," so change them all together.

6. Advanced Extensions

Look back to basics: If Power Query is still new to you, revisit B21 (your first report) and B26 (use AI to write a Power Query M query for data cleaning) — this article is its hardcore upgrade.
Next level: Once the model is clean, the real power is writing measures — go to B22 (use AI to write DAX measures), and use DAX on the cleaned star schema to compute "year-over-year, month-over-month, share," which is the essence of BI.
Go further: When the data hits millions of rows, or you need daily auto-refresh, you can move the ETL into the Power BI cloud or a database, leaving Power Query for light cleaning only — that's an L4 topic.

🛠️ Data Models & Power Query ETL with AI