Downloads
Power Query MTable: combine and joinIntermediate

Table.ExpandListColumn

Turns each item in a column of lists into a row of its own.

Syntax
Table.ExpandListColumn(table as table, column as text) as table

Returns: A table with one row for each item in the lists.

What it does

Sometimes one cell holds several values, such as three invoice numbers in one memo field. Table.ExpandListColumn gives each value its own row, and copies the rest of the row alongside it. It is the step that makes a list in one cell into a proper table of items.

Syntax and arguments

Example

A payment memo lists several invoice numbers separated by commas. The memo is split into a list, and then each invoice number gets its own row, so each one can be matched to the invoice list.

let
    Source = Excel.CurrentWorkbook(){[Name = "Payments"]}[Content],
    AsLists = Table.TransformColumns(Source, {{"Invoices", each Text.Split(_, ",")}}),
    OnePerInvoice = Table.ExpandListColumn(AsLists, "Invoices"),
    Trimmed = Table.TransformColumns(OnePerInvoice, {{"Invoices", Text.Trim, type text}})
in
    Trimmed

Each payment now has one row for each invoice it covers. The Text.Trim step removes the spaces that the comma split leaves at the start of the later numbers.

Things to watch

In business terms

Payments that cover several invoices and bills that list several purchase orders are common in accounts receivable and payable. Expanding them into one row per item lets each item be matched and reviewed on its own.

Related functions

All Power Query M functions · View as Markdown · M basics