How to write Power Query M with Claude: unpivot, types and null checks

2 minutes ago

8 min read

Cover showing the title How to write Power Query M with Claude: unpivot, types and null checks over a teal city skyline, Data Studios original cover

Claude can draft a complete Power Query M query from a plain-English description of a messy export, but it cannot run that query. Its code execution sandbox runs Python, so the M code it returns has never touched the Power Query engine until you paste it into Power BI Desktop. The practical workflow is therefore to let Claude write the M, have it compute the expected answer from the same file in Python, and compare the two numbers after the query runs in Power BI.

The task covered here is one of the most common in BI work: a regional sales file arrives in wide format, with one column per month, numbers stored as text with thousands separators, inconsistent region names and a few blank cells. Power BI needs it in long format, with one row per region, product and month, before any DAX measure or time-intelligence calculation can work on it. The worked example below has 18 month cells, two of them blank, and the correct cleaned table has 16 rows and a grand total of 19,345.

··········

WHAT CLAUDE AND POWER BI DESKTOP EACH DO IN THIS TASK.

Power BI Desktop runs the query. Microsoft's query overview documentation says you open Power Query Editor by selecting Transform data on the Home tab, open the Advanced Editor from the View tab, and apply the result with Close & Apply from the File menu. Every transformation is saved as a step in the Applied steps pane, and selecting a step previews the data at that point, which is the feature you will use to check Claude's work step by step.

Claude reads the source file and writes the code. Anthropic's help center lists CSV and XLSX among the supported upload types, with up to 20 files per chat, and notes that XLSX uploads require the "Code execution and file creation" capability to be enabled. That capability is described as available on Free, Pro, Max, Ultra, Team and Enterprise plans and enabled by default, with Team and Enterprise owners able to turn it off. The two help pages state different per-file size limits (500 MB per file for chat uploads on the upload page, 30 MB per file on the file-creation page), so a large export should be tested before you build a routine around it.

........

Step	Tool	Documented basis (verified November 11, 2026)
Read the CSV and describe its problems	Claude	CSV upload; code execution and file creation setting
Write the M query	Claude	Text output; Claude does not run M
Compute expected totals	Claude or local Python	Python in Claude's sandbox
Run the query and inspect steps	Power BI Desktop	Power Query Editor, Advanced Editor, Applied steps
Approve and load the table	Analyst	Close & Apply in Power BI Desktop

........

If you also write SQL with Claude, the same verify-against-a-known-answer habit is covered in how to write SQL with Claude and catch fan-out and date errors. For more detail on how Claude handles uploaded spreadsheets, see Claude AI spreadsheet uploading and its CSV and Excel limits.

··········

THE ILLUSTRATIVE REGIONAL SALES EXPORT.

The file below is illustrative and small enough to check by hand. It has three defects that real exports share: region names with stray spaces and mixed capitalization ("East ", "east", " West", "NORTH"), sales figures stored as text with comma thousands separators, and two empty cells, one for Widget B in the East in February and one for Widget B in the West in March.

........

Region	Product	Jan	Feb	Mar
East (trailing space)	Widget A	1,250	1,100	1,320
east	Widget B	860	(blank)	910
West	Widget A	2,040	1,980	2,115
West (leading space)	Widget B	455	500	(blank)
North	Widget A	1,500	1,620	1,580
NORTH	Widget B	720	690	705

........

The target is a long table with four columns (Region, Product, Month, Sales), clean region names, Sales typed as a number, and no rows for the blank cells. Each unstated requirement is a decision Claude will make on its own, so the prompt spells them out.

I uploaded regional-sales-wide.csv. Write a Power Query M query for
Power BI Desktop that:
1. Loads the CSV from C:\Data\regional-sales-wide.csv with UTF-8 encoding
   and promotes the first row to headers.
2. Trims Region and Product and converts Region to proper case.
3. Unpivots every column except Region and Product into Month and Sales,
   so that new month columns added later are picked up automatically.
4. Removes rows where Sales is null or empty text.
5. Converts Sales to a number using the en-US culture, because the file
   uses commas as thousands separators.
Give each step a readable name. Do not add steps I did not ask for.
Then, separately, use Python on the uploaded file to compute the expected
row count, the total Sales per region and the grand total.

··········

THE POWER QUERY M CODE FOR THE UNPIVOT AND TYPE CONVERSION.

A correct answer looks like the query below. Every function in it is documented in Microsoft's Power Query M reference: Csv.Document with its options record (Delimiter, Encoding, QuoteStyle), Table.PromoteHeaders, Table.TransformColumns with an optional new column type as the third element of each operation, Text.Trim and Text.Proper, Table.UnpivotOtherColumns, Table.SelectRows and Table.TransformColumnTypes with its optional culture argument.

let
    Source = Csv.Document(
        File.Contents("C:\Data\regional-sales-wide.csv"),
        [Delimiter = ",", Encoding = 65001, QuoteStyle = QuoteStyle.Csv]
    ),
    PromotedHeaders = Table.PromoteHeaders(Source, [PromoteAllScalars = true]),
    CleanedNames = Table.TransformColumns(
        PromotedHeaders,
        {
            {"Region", each Text.Proper(Text.Trim(_)), type text},
            {"Product", Text.Trim, type text}
        }
    ),
    Unpivoted = Table.UnpivotOtherColumns(CleanedNames, {"Region", "Product"}, "Month", "Sales"),
    NonBlankSales = Table.SelectRows(Unpivoted, each [Sales] <> null and Text.Trim([Sales]) <> ""),
    TypedSales = Table.TransformColumnTypes(NonBlankSales, {{"Sales", type number}}, "en-US")
in
    TypedSales

Three choices in that code deserve a line-by-line review whenever Claude produces a variant of it.

The unpivot uses Table.UnpivotOtherColumns with Region and Product as the fixed columns. Microsoft's Unpivot columns documentation recommends Unpivot other columns for sources with an unknown number of columns, because new columns are unpivoted on refresh, while the Unpivot only selected columns option leaves new columns untouched. A query that lists "Jan", "Feb" and "Mar" by name will keep working in April but silently ignore the April column.

The blank filter runs before the type conversion and tests for both null and empty text. In Microsoft's documented example for Table.Unpivot, cells holding null produce no output row, but a CSV source can deliver an empty cell as empty text instead, and an empty text value would then reach the number conversion. Filtering both cases first keeps the step order explicit and the row count predictable.

The type conversion pins the culture to "en-US". The Table.TransformColumnTypes reference documents the optional culture argument and shows the same value converting differently under "fr-FR" and "de-DE". If this file were converted under a culture that reads the comma as a decimal separator, "1,250" would become 1.25, and the grand total of the sample would fall from 19,345 to 4,854.505.

··········

EXPECTED RESULT OF THE M QUERY, COMPUTED IN PYTHON.

The reference numbers come from an independent pandas calculation on the same file, which Claude can run in its sandbox when code execution is enabled or you can run locally.

import pandas as pd

raw = pd.read_csv("regional-sales-wide.csv", dtype=str, keep_default_na=False)
raw["Region"] = raw["Region"].str.strip().str.title()
raw["Product"] = raw["Product"].str.strip()

long = raw.melt(id_vars=["Region", "Product"], var_name="Month", value_name="Sales")
long = long[long["Sales"].str.strip() != ""].copy()
long["Sales"] = long["Sales"].str.replace(",", "", regex=False).astype(float)

print(len(long))
print(long.groupby("Region")["Sales"].agg(["sum", "count"]))
print(long["Sales"].sum())

........

Region	Rows	Total Sales
East	5	5,440
North	6	6,815
West	5	7,090
Total	16	19,345

........

By month, the expected totals are 6,825 for Jan, 5,890 for Feb and 6,630 for Mar. The region list must contain exactly three values; a fourth or fifth region (for example "East " with a trailing space next to "East") means the trimming step did not apply to every row. If the blank cells had been kept as zeros, the table would show 18 rows instead of 16 and the average sale per row would drop from 1,209.06 to 1,074.72 with the same total, a difference that no total-based check would catch.

To get the same figures inside Power BI, add a second query that references the first one (named SalesLong here) and groups it by region:

let
    Source = SalesLong,
    RegionCheck = Table.Group(
        Source,
        "Region",
        {
            {"Total Sales", each List.Sum([Sales]), type number},
            {"Rows", each Table.RowCount(_), Int64.Type}
        }
    )
in
    RegionCheck

Table.Group, List.Sum and Table.RowCount are all in Microsoft's M function reference. Microsoft notes that Table.Group does not guarantee the order of the rows it returns, so compare the check query by region name and not by position.

··········

WHAT CLAUDE TYPICALLY GETS WRONG IN POWER QUERY M.

Because Claude never executes the M code, the dangerous errors are the ones that run without complaint and produce a plausible table. Five patterns deserve a check every time.

Hard-coded column lists are the first. A generated query may use Table.Unpivot with the month names written out, or a Table.TransformColumnTypes step that lists every column of the original file. Both work today and break or under-report after the source gains a column.

Missing or wrong culture arguments are the second. A conversion that relies on the default culture can behave differently on a colleague's machine or in the Power BI service than on the machine where the report was built. Ask for the culture explicitly and confirm it appears in the type-conversion step.

Filtering in the wrong order is the third. A query that converts Sales to a number before removing blanks makes the result depend on how the conversion treats empty text, and a "remove errors" step added afterward to tidy the output can also hide real conversion failures, such as a value typed as "1.250,00" in one row.

Invented function names or options are the fourth. Claude can produce plausible names that do not exist in M. Check every function against the Power Query M reference on learn.microsoft.com before you rely on the query; the Advanced Editor will flag syntax problems, but a valid function used with the wrong argument order can still return a table.

Unrequested steps are the fifth. Sorting, renaming or deduplication steps that you did not ask for change row counts. The prompt above asks Claude not to add steps, and the Applied steps pane shows whether it complied.

The review itself takes a few minutes. Paste the code into the Advanced Editor, select each step in Applied steps, and confirm the row count after the unpivot (18), after the blank filter (16) and the totals in the check query (5,440, 6,815 and 7,090). The same row-count discipline applies to cleaning steps done before Power BI, as shown in how to clean and deduplicate a messy CSV with ChatGPT.

··········

DATA PRIVACY WHEN YOU UPLOAD BI EXPORTS TO CLAUDE.

Anthropic's privacy center says that on consumer plans (Free, Pro and Max) chats are used to improve its models when the user allows it in the Model Improvement privacy setting, that Incognito chats are excluded even with that setting on, and that conversations flagged for safety review may also be used. Commercial products such as Claude for Work and the Anthropic API are covered by a separate article and commercial terms, which a company should read before analysts upload customer or financial data.

Anthropic's file-creation documentation also warns that malicious instructions hidden in files or websites could lead Claude to run untrusted code or leak information from its context, and recommends monitoring Claude while it works with files. For BI work, the safer pattern is to upload a sample with the same structure and defects as the real export, let Claude write the M code against the sample, and run the final query on the full data only in Power BI Desktop. Once the long table is loaded, time calculations such as a fiscal year-to-date measure with TOTALYTD can be built on top of it.

··········

POWER QUERY M WITH CLAUDE: WHAT THE ANALYST STILL CONTROLS.

Claude can write the full M query for a wide-to-long reshape, including trimming, proper case, unpivot, blank filtering and culture-aware type conversion, and it can compute the expected result in Python from the same file. The first execution of the M code always happens in Power BI Desktop, and the comparison between the Applied steps preview and the Python figures is the actual test.

Three points depend on your environment and need checking in each tenant or workstation: whether the code execution and file creation capability is enabled for your Claude plan or organization, which per-file size limit applies to your exports, and which culture your source files use. For the sample in this example, a correct query produces 16 rows, three regions and a grand total of 19,345; any other result points to a step that needs to be read again before the table goes into a report.

··········

·····

DATA STUDIOS

·····

datastudios.org