top of page

Excel Arena

This is the place, where we can test the boundaries of Excel functions.

Power Query pattern turns key-value pairs into relational records.

11 minutes ago
2 min read

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.

Recent Posts

See All

Comments


  • LinkedIn
  • Facebook
  • Twitter
  • Instagram
bottom of page