Power Query pattern turns key-value pairs into relational records.
Using List.Accumulate, record merging, and a smart boundary detection rule, hierarchical data is converted into a normalized table.

let
Source = Excel.CurrentWorkbook(){[Name="Table4"]}[Content],
Group = Table.Group(Source,"Company", {"New", each List.Skip(List.Accumulate(Table.ToRows(Table.RemoveColumns(_,"Company")), {[]}, (s,c) => if Record.HasFields( List.Last(s), c{0}) or c{0} = "Type" then s & {Record.FromList({c{1}},{c{0}})} else List.RemoveLastN(s,1) & {List.Last(s,1) & Record.FromList({c{1}},{c{0}})})) }),
Result = Table.FillDown(Table.ExpandRecordColumn(Table.ExpandListColumn(Group,"New"),"New", List.Distinct(Source[Classification])), {"Type"})
in
Result
We need to propagate each Type down to the subsequent Sub type / Price records until the next Type appears.
Each company's records are processed independently.
For MSFT, the accumulator receives:
{
{"Type","Software"},
{"Sub type","Home Office"},
{"Price",220},
{"Sub type","MS Project"},
{"Price",450},
{"Type","Hardware"},
{"Sub type","Surface"},
{"Price",1500},
{"Sub type","Mouse pad"}
}
The accumulator:
(s,c) =>
s = state (list of records built so far)
c = current row
For a row such as:
{"Type","Software"}
We create:
[Type="Software"]
using
Record.FromList({c{1}},{c{0}})
Decide whether to start a new record:
if Record.HasFields(List.Last(s), c{0})
or c{0} = "Type"
A new record begins when:
Another Type appears
{"Type","Hardware"}
or
A field already exists in the current record
Example:
[Type="Software", Sub type="Home Office", Price=220]
Encountering
{"Sub type","MS Project"}
means Sub type already exists, so a new output row should begin.
Intermediate result for MSFT
{
[Type="Software", Sub type="Home Office", Price=220],
[Sub type="MS Project", Price=450],
[Type="Hardware", Sub type="Surface", Price=1500],
[Sub type="Mouse pad"]
}
Notice the missing Type values in rows 2 and 4.
Expand and Fill Down:
Result =
Table.FillDown(
Table.ExpandRecordColumn(
Table.ExpandListColumn(Group,"New"),
"New",
List.Distinct(Source[Classification])
),
{"Type"}
)
After expansion:
Company | Type | Sub type | Price |
MSFT | Software | Home Office | 220 |
MSFT | null | MS Project | 450 |
MSFT | Hardware | Surface | 1500 |
MSFT | null | Mouse pad | null |
Then
Table.FillDown(..., {"Type"})
Produces
Company | Type | Sub type | Price |
MSFT | Software | Home Office | 220 |
MSFT | Software | MS Project | 450 |
MSFT | Hardware | Surface | 1500 |
MSFT | Hardware | Mouse pad | null |
which is the expected output.
What I like most about this approach:
The entire transformation is driven by this condition:
Record.HasFields(List.Last(s), c{0}) or c{0}="Type"
It effectively treats:
Type as a parent node
repeated fields as record boundaries
making the solution completely dynamic. If tomorrow another attribute such as "Color" appears, the code still works without modification. This is a very elegant use of List.Accumulate, Record.FromList, and record merging.




Comments