Level 5 · Turn a month-per-column report into rows

Thomas’s export was made for people: months across the top, the category written once per group, like a printed report. A model needs it the other way round, one row per fact (subcategory, month, units), so that a single month column can go on an axis and a single units column can be summed.

Fill the gaps first

Fill down: category

The export only writes Decor on the first Decor row; the next three rows are blank (null) but belong to Decor too. Fill Down copies the last value above into each empty cell, until the next value appears. In Power BI: right-click the category header → Fill → Down (or Transform → Fill → Down). The M:

Table.FillDown(Source, {"category"})

Fill Up does the reverse, for reports that write the label at the bottom of a group. Both only fill nulls. If the blanks are empty text, first use Replace Values, finding nothing and replacing with null.

Unpivot: columns into rows

Unpivot columns: Jan, Feb, Mar

In Power BI: select the three month columns, then Transform → Unpivot Columns. Each source row becomes three rows; the column names go into a new Attribute column and the numbers into Value:

category	subcategory	Jan	Feb	Mar
Decor	Candles	80	56	115

becomes

category	subcategory	Attribute	Value
Decor	Candles	Jan	80
Decor	Candles	Feb	56
Decor	Candles	Mar	115

Twenty-five rows become 75. Rename the two new columns so the model reads well:

Rename column: Attribute to month
Rename column: Value to units

Unpivot Other Columns: ready for April

Finance will send Q2 with Apr, May, Jun. A step that names Jan, Feb and Mar won’t pick up the new months. The robust version selects the columns that stay, category and subcategory, and uses Transform → Unpivot Columns → Unpivot Other Columns. Its M names the columns to keep, so any month column the file brings is unpivoted:

Table.UnpivotOtherColumns(#"Filled Down", {"category", "subcategory"}, "Attribute", "Value")

Here, with exactly three months, both give the same table. In Power BI, prefer Unpivot Other Columns for any export that grows sideways.

Pivot: the way back

Transform → Pivot Column does the opposite: it turns the values of one column into column headers. Useful for a small summary table, rarely for a model.

Common mistakes

• Unpivoting first. Blanks move into the long table and the category is lost.
• Leaving Attribute and Value. Nobody knows what Value means in the Data pane.
• Months as text with no order. Jan, Feb, Mar sort alphabetically as Feb, Jan, Mar. Relate the data to the ‘Date’ table, or add a month number to sort by.