Table.ExpandListColumn
Turns each item in a column of lists into a row of its own.
Table.ExpandListColumn(table as table, column as text) as tableReturns: 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
- table: the table with the list column. Required.
- column: the name of the column that holds the lists, in quotes. Required.
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
- A cell that is empty gives an empty list, and the handling of that row depends on the source. Check the row count before and after.
- Expanding multiplies the rows. Be sure the amount column is not repeated in a way that double counts it. Often you divide the amount, or match the lines to a detail table instead.
- Split text into lists before expanding, and trim the pieces.
- Query folding: this step does not fold, so run it after the filters that reduce the data.
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.