Automatically expand column in JSON format

[cgeraeds]

Hello community,

I've stumbled upon many threads about JSON files and how to use them in Power BI, but I can't however find the issue that I am facing.

My JSON has a number of levels which I expand to reach. I than do an unpivot step to reduce the number of columns (which contain records). The column that remains I than have to expand again. However these "columns" have many values which with every new dataset are different. The way I use this now forces me with every refresh to to go into the query editor and select all the columns manually to be able to expand them all. In M language it looks like this:

let
Source = Json.Document(File.Contents("Location of JSON file")),
#"Converted to Table" = Record.ToTable(Source),
#"Expanded Value" = Table.ExpandRecordColumn(#"Converted to Table", "Value", {"afspraken / acties"}, {"afspraken / acties"}),
#"Expanded afspraken / acties" = Table.ExpandRecordColumn(#"Expanded Value", "afspraken / acties", {"actielijnen"}, {"actielijnen"}),
#"Expanded actielijnen" = Table.ExpandRecordColumn(#"Expanded afspraken / acties", "actielijnen", {"werk", "veiligheid en geborgenheid", "ontwikkeling", "werk / daginvulling", "vrije tijd", "samenleven", "financiën", "wonen", "inkomen", "gezondheid", "algemeen"}, {"werk", "veiligheid en geborgenheid", "ontwikkeling", "werk / daginvulling", "vrije tijd", "samenleven", "financiën", "wonen", "inkomen", "gezondheid", "algemeen"}),
#"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Expanded actielijnen", {"Name"}, "Attribute", "Value"),
#"Expanded Value3" = Table.ExpandRecordColumn(#"Unpivoted Columns", "Value", {"administratie op orde brengen", "huishouden op orde brengen", "verwijderen 2", "test", "test verwijderen", "teststete", "x", "xx", "ww", "gsfhsf", "wrtyw5y", "vyudvqlud", "rtrwt", "rotwet", "rtreqteqt eq", "erewr", "huxfdhgiugf iu", "tdifiytf kyf", "ewrw", "erqteqteq tetrert", "ryryryryr"}, {"administratie op orde brengen", "huishouden op orde brengen", "verwijderen 2", "test", "test verwijderen", "teststete", "x", "xx", "ww", "gsfhsf", "wrtyw5y", "vyudvqlud", "rtrwt", "rotwet", "rtreqteqt eq", "erewr", "huxfdhgiugf iu", "tdifiytf kyf", "ewrw", "erqteqteq tetrert", "ryryryryr"}),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Expanded Value3", {"Name", "Attribute"}, "Attribute.1", "Value"),
#"Expanded Value2" = Table.ExpandRecordColumn(#"Unpivoted Other Columns", "Value", {"datum", "beschrijving", "gereed", "wie", "resultaat", "vervolg", "beschrijving-aantal"}, {"datum", "beschrijving", "gereed", "wie", "resultaat", "vervolg", "beschrijving-aantal"})

in

#"Expanded Value2"

The whole dataset only contains dummy data so luckily I can share this, the example is about the table called "actielijnen":

https://www.dropbox.com/s/q4hygm1q8u5etgh/ondersteuningplan_helmond_20190620055729_ozo-test.json?dl=...

Now I'm looking for a way that selecting the columns goes automatically and I don't have to select them first. This is dummy data but the real database contains 10.000's of records and therefore it's not workable to manually select with every refresh.

I thank you kindly in advance for any assistance!

Kind Regards,

Chris

[v-piga-msft]

Hi @cgeraeds ,

I'm afraid that the automatically expand column should be achieved by writing the M Query under Advanced Editor.

For your requirement, please refer to the two similar threads which should be helpful. 

https://stackoverflow.com/questions/48542013/dynamically-expand-all-lists-and-records-from-json

https://blog.crossjoin.co.uk/2014/05/21/expanding-all-columns-in-a-table-in-power-query/

Best Regards,

Cherry

[cgeraeds]

Hello @v-piga-msft ,

I already found both these threads, however they're not exactly the same as my problem and I'm not a M wizard with the capabilities to tweak them to be usable in my situation. 

The difference with the Chris Webb example you posted is that it is with tables while I have records. In the other link, records are used. The other example I can't get working with my data. Is it possible to assist in rewriting the used formula here: https://gist.github.com/Mike-Honey/0a252edf66c3c486b69b?

[ImkeF]

You can use this function to expand your JSON automatically:  https://www.thebiccountant.com/2018/06/17/automatically-expand-all-fields-from-a-json-document-in-po...

But you have to decide what to do with the list-fields in your records: Write out separate tables of combine them into one text field. Anyway: The result of my function should give you a good startingn point to build your tables by further pivoting and grouping.

[cgeraeds]

Hello @ImkeF ,

Thanks for your reply!

I tried the code on my JSON but I then get the following which I don't know how to work with:

From the same JSON I create multiple tables to be used. In only a number of them I experience the issue as previously explained. How with your code can I select the appropriate table?

Could you please guide me through this solution?

Thanks!

[ImkeF · Accepted solution]

Hi @cgeraeds ,

it looks as if I've misread your request.

Please try the following code:

let
Source = Json.Document(File.Contents("Location of JSON file")),
#"Converted to Table" = Record.ToTable(Source),
#"Expanded Value" = Table.ExpandRecordColumn(#"Converted to Table", "Value", {"afspraken / acties"}, {"afspraken / acties"}),
#"Expanded afspraken / acties" = Table.ExpandRecordColumn(#"Expanded Value", "afspraken / acties", {"actielijnen"}, {"actielijnen"}),
#"Expanded actielijnen" = Table.ExpandRecordColumn(#"Expanded afspraken / acties", "actielijnen", Record.FieldNames(Record.Combine(List.Select(Table.Column(#"Expanded afspraken / acties", "actielijnen"), (x) => x <> null)))),
#"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Expanded actielijnen", {"Name"}, "Attribute", "Value"),
#"Expanded Value3" = Table.ExpandRecordColumn(#"Unpivoted Columns", "Value", Record.FieldNames(Record.Combine(List.Select(Table.Column(#"Unpivoted Columns", "Value"), (x) => x <> null)))),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Expanded Value3", {"Name", "Attribute"}, "Attribute.1", "Value"),
#"Expanded Value2" = Table.ExpandRecordColumn(#"Unpivoted Other Columns", "Value", Record.FieldNames(Record.Combine(List.Select(Table.Column(#"Unpivoted Other Columns", "Value"), (x) => x <> null)))),
OptionalConsolidation = Table.AddColumn(#"Expanded Value2", "AllColumns", each Table.FromColumns(Record.FieldValues(Record.RemoveFields(_, {"Name", "Attribute", "Attribute.1"})), Record.FieldNames(Record.RemoveFields(_, {"Name", "Attribute", "Attribute.1"}))))
in
OptionalConsolidation

It expands all record fields automatically. I'm using a syntax that makes copy-pasting easy, please see the bolded parts of the code.

[cgeraeds]

That worked perfectly!

Thanks Imke.

I don't understand what happens exactly but this part does the trick:

Table.ExpandRecordColumn(#"Expanded afspraken / acties", "actielijnen", Record.FieldNames(Record.Combine(List.Select(Table.Column(#"Expanded afspraken / acties", "actielijnen"), (x) => x <> null)))),