Microsoft Excel · Power Query bounded introductory track · Intermediate
Clean types, split fields and reshape columns in Power Query
Split a composite field, assign explicit types, and unpivot monthly columns into a verified long-form query result.
Official documentation reviewed; synthetic fixtures checked independently; not executed in Excel · Editorial standards
Quick answer: In Power Query Editor, define your types, split the composite Text column by its delimiter, then unpivot the month columns. Check the number of rows and the preserved total before loading the reshaped table.
Prepare the wide source
Use English Excel for Microsoft 365 desktop on Windows. This is a Windows Power Query Editor workflow. Mac and web support varies by source and feature; do not assume their available transformations and entry points match these steps. Use a local practice workbook, not a live connection.
Enter this block at A1:C3 on a sheet named Source:
Location Jan Feb
North|Shop 10 12
South|Web 20 18
Select A1:C3, create an Excel table with headers, and choose Data > From Table/Range. Power Query Editor opens. The source has two records and four monthly measurements totaling 60. The desired result has one row per location/channel/month combination, not one row per original location.
Set types and split the field
- Select Location and set Home > Data Type > Text. It is a composite label, not a number.
- Select Jan and Feb and set their data types to Whole Number. Confirm that no error values appear. A cell merely formatted to look numeric in Excel is not proof of the query’s type.
- Select Location and choose Home > Split Column > By Delimiter.
- Choose a custom delimiter of
|, and split at each occurrence. This controlled fixture has exactly one delimiter in each record, so the result must be two columns. - Rename the resulting columns Region and Channel. Verify North/Shop and South/Web before moving on.
A missing delimiter or an unexpected extra delimiter changes the shape. Do not blindly accept additional split columns or nulls. Repair the source convention or choose an appropriate left-most/right-most split rule for a real dataset after inspecting its values.
Unpivot the month columns
Select Jan and Feb, then choose Transform > Unpivot Columns. Power Query creates an attribute column containing month names and a value column containing the numbers. Rename them Month and Units. Keep Region, Channel, and Month as Text and Units as Whole Number.
The final four records should be:
| Region | Channel | Month | Units |
|---|---|---|---|
| North | Shop | Jan | 10 |
| North | Shop | Feb | 12 |
| South | Web | Jan | 20 |
| South | Web | Feb | 18 |
The order of preview rows is less important than these exact four combinations. Their total remains 60. If you accidentally unpivot Region or Channel, the attribute list will contain those labels and the output will not match this shape.
Choose Home > Close & Load, and keep the loaded result on a different sheet from Source. Preserve the query steps so the transformation can be repeated rather than manually repairing the output.
Check a changed input and future columns
Change Source!C3 from 18 to 23, then refresh the loaded query. Only South/Web/Feb should become 23; the output still has four rows and now totals 65. Check both facts, because a correct total can conceal duplicated or omitted records.
If a future file adds another month, review the chosen unpivot strategy. Unpivot Other Columns, with the stable identifier columns selected, is designed to include new unselected columns on refresh. That can be useful, but it also means an unexpected notes column may become a measure. Choose it only after deciding which columns are identifiers and what future additions mean.
Troubleshoot and save
Inspect the first failing Applied Step when a type conversion errors. For locale-dependent numbers or dates, use the type menu’s Using Locale option with the source’s actual convention, not your guess. See day/month import ambiguity for why appearance alone is insufficient. Save as XLSX and reopen; verify query steps, four records, and the updated 65 total.