Day 3 — Shaping data — split, group, append and merge

Four operations that cover most of the reshaping you will ever need.

Split Column

One column holding several facts is the most common shape problem in exported data. A code like DL-2024-0912 is really a state, a year and a serial number.

Transform → Split Column → By Delimiter choose the delimiter - split at each occurrence

Split by each occurrence and you get three columns. Split at the left-most delimiter and you get two — the state, and everything else. Which you want depends on whether the tail is meaningful.

Split by position is fragile "First 2 characters" works until one code has a three-letter prefix. Splitting by the delimiter survives that. Prefer the delimiter whenever there is one.

Group By

Group By collapses rows. If the source has one row per line item but the report only ever shows order totals, group in Power Query and load a tenth of the rows.

Transform → Group By Group by Region, Month New column Total Amount = Sum of Amount Order Count = Count rows

Group By is a one-way door Once you group, the detail is gone from the model. If anyone might ever ask "which orders make up that number?", do not group — let DAX aggregate instead. Group only when you are certain the detail is never needed.

Append — stacking tables

Twelve monthly files with identical columns. Append puts them end to end into one table.

Home → Append Queries Jan + Feb + Mar ... → one Sales table

Better still, point Power Query at the folder rather than the files. Then next month you drop the new file in and refresh — no edit needed.

Append matches on column names, not position If one file calls it "Amount" and another "Amt", you get two columns, each half empty. Rename before appending.

Merge — joining tables

Merge is Power Query’s join. It brings columns from one table into another, matching on a key.

Home → Merge Queries Sales.ProductID = Product.ProductID Join kind: Left Outer

Join kind	Keeps
Left Outer	Everything from the first table — the safe default
Inner	Only rows that match both sides
Left Anti	Only rows with no match — how you find orphans

Left Anti is a quality check, not a join Merge Sales to Product with Left Anti. Any rows that come back are sales with a product ID that does not exist in the product table. That is a data problem you want to know about before it shows up as a blank row in a visual.

But should you merge at all?

This is the important question of the day. You could merge Product and Region into Sales and end up with one wide flat table. Many people do, because it feels like the Excel they know.

Usually: do not A relationship does the same job without duplicating the product name onto all 48 rows. It uses less memory, it lets a slicer filter several tables at once, and it keeps the model readable. Merge when you need a single value that will never change and never be sliced by. Otherwise, build a relationship — which is tomorrow.

Where the model stands

Sales stayed narrow, the descriptive columns stayed in their own tables:

Six products exist and all six have been sold. If the distinct count came back lower, some product in the catalogue has never sold — which is a genuine business answer, not a data error.

Try these yourself

Describe how you would split "DL-2024-0912" into state, year and serial.
Explain when Group By is the wrong choice.
You have 12 monthly files. Say which is better — Append or a folder source — and why.
Explain what a Left Anti join tells you about your data.
Give one reason to build a relationship instead of merging.

← PreviousDay 2 — Power Query — getting data in and cleaning it Next →Day 4 — The data model and relationships