EXPAND MULTIPLE COLUMNS POWER BI

Question

I´ve been struggling with this:

enter image description here

My table shows 3 records but when expanding there are like 100 columns. I used this code:

#"Expanded Data" = Table.ExpandTableColumn(#"Source", "Document", List.Union(List.Transform(#"Source"[Document]), each Table.ColumnNames(_))),

but it's not working. How can I expand simultaneously all columns? Also, inside those columns there are even more, for example I expand the first time end then those new columns have more records inside.

What could I do? Thanks in advance!

score 0 · Answer 1 · answered Mar 13 '21 at 08:55

0

Try this ExpandAllRecords function - it recursively expands every Record-type column:

https://gist.github.com/Mike-Honey/0a252edf66c3c486b69b

answered Mar 13 '21 at 08:55

Mike Honey

14,523
1
24
40

score 0 · Answer 2 · answered Nov 12 '22 at 11:59

This should work for Records Columns.

let
 ExpandIt = (TableToExpand as table, optional ColumnName as text) =>
 let
      ListAllColumns = Table.ColumnNames(TableToExpand),
      ColumnsTotal = Table.ColumnCount(TableToExpand),
      CurrentColumnIndex = if (ColumnName = null) then 0 else  List.PositionOf(ListAllColumns, ColumnName),
      CurrentColumnName = ListAllColumns{CurrentColumnIndex},
      CurrentColumnContent = Table.Column(TableToExpand, CurrentColumnName),
      IsExpandable = if List.IsEmpty(List.Distinct(List.Select(CurrentColumnContent, each _ is record))) then false else true,
      FieldsToExpand = if IsExpandable  then Record.FieldNames(List.First(List.Select(CurrentColumnContent, each _ is record))) else {},
      ColumnNewNames = List.Transform(FieldsToExpand, each  CurrentColumnName &"."& _),
      ExpandedTable = if IsExpandable  then Table.ExpandRecordColumn(TableToExpand, CurrentColumnName, FieldsToExpand, ColumnNewNames) else TableToExpand,
      NextColumnIndex = CurrentColumnIndex+1,
      NextColumnName = ListAllColumns{NextColumnIndex},
      OutputTable = if NextColumnIndex > ColumnsTotal-1 then ExpandedTable else @fx_ExpandIt(ExpandedTable, NextColumnName)
 in
      OutputTable
in
 ExpandIt

This basically takes Table to Transform as the main argument,and then one by one checks if the Column Record is expandable (if column has "records" in it, it will expand it, otherwise move to next column and checks it again). Then it returns the Output table once everything is expanded. This function is calling the function from inside for each iteration.

EXPAND MULTIPLE COLUMNS POWER BI

2 Answers2