Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
ExpandAll function for Power Query or Power BI - expands all table-type columns recursively
// From Chris Webb's blog - http://blog.crossjoin.co.uk/2014/05/21/expanding-all-columns-in-a-table-in-power-query/
let
//Define function taking two parameters - a table and an optional column number
Source = (TableToExpand as table, optional ColumnNumber as number) =>
let
//If the column number is missing, make it 0
ActualColumnNumber = if (ColumnNumber=null) then 0 else ColumnNumber,
//Find the column name relating to the column number
ColumnName = Table.ColumnNames(TableToExpand){ActualColumnNumber},
//Get a list containing all of the values in the column
ColumnContents = Table.Column(TableToExpand, ColumnName),
//Iterate over each value in the column and then
//If the value is of type table get a list of all of the columns in the table
//Then get a distinct list of all of these column names
ColumnsToExpand = List.Distinct(List.Combine(List.Transform(ColumnContents,
each if _ is table then Table.ColumnNames(_) else {}))),
//Append the original column name to the front of each of these column names
NewColumnNames = List.Transform(ColumnsToExpand, each ColumnName & "." & _),
//Is there anything to expand in this column?
CanExpandCurrentColumn = List.Count(ColumnsToExpand)>0,
//If this column can be expanded, then expand it
ExpandedTable = if CanExpandCurrentColumn
then
Table.ExpandTableColumn(TableToExpand, ColumnName,
ColumnsToExpand, NewColumnNames)
else
TableToExpand,
//If the column has been expanded then keep the column number the same, otherwise add one to it
NextColumnNumber = if CanExpandCurrentColumn then ActualColumnNumber else ActualColumnNumber+1,
//If the column number is now greater than the number of columns in the table
//Then return the table as it is
//Else call the ExpandAll function recursively with the expanded table
OutputTable = if NextColumnNumber>(Table.ColumnCount(ExpandedTable)-1)
then
ExpandedTable
else
ExpandAll(ExpandedTable, NextColumnNumber)
in
OutputTable
in
Source
@JP-DL

This comment has been minimized.

Copy link

JP-DL commented Jul 12, 2019

Hi Mike, I am working on getting this to working using GraphQL API. I am running into an issue where the function is not expanding all the records.

I am creating a JSON object from the api, which is being returned to M as a record. I am converting that record to a table; in that step a table is created with two columns. The second column is called [value] and it contains one record that is a LIST. The function does not iterate over the list, and does not expand all of the columns.

I am not sure exactly why this is happening, but it appears that the function is running into the list as an object, and not just finding records in the table.

Any advice on how to alter the M so deal with that list in the [Value] column of my table created out of the original JSON record?

Thank you for posting this!

@JP-DL

This comment has been minimized.

Copy link

JP-DL commented Jul 12, 2019

I should mention, I also used your other script that you mentioned was for JSON where you iterate over Records instead of Tables, but I had the same issue.

@Mike-Honey

This comment has been minimized.

Copy link
Owner Author

Mike-Honey commented Jul 13, 2019

I would just use the UI to expand the List. That's a simple step that cant be recursive, so doesnt need to call a function.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.