Skip to content

Instantly share code, notes, and snippets.

@Hugoberry
Last active May 20, 2024 18:38
Show Gist options
  • Save Hugoberry/4ad49f4301edf47fffe2ef06aed61513 to your computer and use it in GitHub Desktop.
Save Hugoberry/4ad49f4301edf47fffe2ef06aed61513 to your computer and use it in GitHub Desktop.
JSON to Table in Power Query M
(json) =>
let
//List the expandable columns
expandableColumns = (_) => List.Accumulate(
Table.ColumnNames(_),
{},
(s,c)=>s&(if Type.Is(Value.Type(Record.Field(_{0},c)), type record)
or Type.Is(Value.Type(Record.Field(_{0},c)), type list)
then {c}
else {})
),
// Do the record columns have a consistent structure?
columnHasConsistentType = (T,Cname) =>
let
columnTypes = List.Accumulate(Table.Column(T,Cname),
{},
(s,c)=>s&{Type.RecordFields(Value.Type(c))})
in not (List.Count(List.Distinct(columnTypes)) = List.Count(columnTypes)),
// Expand the consistent typed column based on their primitive type
expandConsistentColumn = (T,Cname) => if Table.Column(T,Cname){0} is record
then expandRecordColumn(T,Cname)
else Table.ExpandListColumn(T,Cname),
// Expand the record column
expandRecordColumn = (T,Cname) => let
fields = Record.FieldNames(Table.Column(T,Cname){0})
in Table.ExpandRecordColumn(T,Cname,fields),
// expand the table column with prefixing the column names with the parent column name
expandTableColumn = (T,Cname) => Table.ExpandTableColumn(
Table.TransformColumns(T,{Cname,Record.ToTable}),
Cname,{"Name","Value"},{Cname&".Name",Cname&".Value"}),
// expand all available columns in the table T
expand = (T) => List.Accumulate(expandableColumns(T),T,(s,c)=> if columnHasConsistentType(s,c)
then expandConsistentColumn(s,c)
else expandTableColumn(s,c)),
// initialize the table structure based on the JSON object
table_ini = if json is record
then Record.ToTable(json)
else Table.FromList(json, Splitter.SplitByNothing()),
// kick off the iteration to expandd all columns form the table based JSON object
iterator = List.Generate(
()=> [expandable = true,
table_chain = table_ini],
each [expandable],
each [expandable = List.Count(expandableColumns([table_chain]))>0,
table_chain = expand([table_chain])],
each [table_chain]),
// output only the last iteration
out = List.Last(iterator)
in
out
@hohlick
Copy link

hohlick commented Feb 28, 2017

Hi Hugoberry!
Have seen you comment on Power BI Community, trying to check this function, but it fails with this file: https://yadi.sk/d/5-I7ZFur3CBXiZ

Also, instead of this code:
table_ini = if json is record then Record.ToTable(json)
should be
table_ini = if json is record then Table.FromRecords({json})
because expandableColumns cannot return correct result for columns "Name" and "Value" of former variant.
And then, if there is a record as json input, in any case the "columnHasConsistentType" function returns an error.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment