Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
cgeraeds
Advocate I
Advocate I

Automatically expand column in JSON format

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

 

1 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.

 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

View solution in original post

6 REPLIES 6
v-piga-msft
Resident Rockstar
Resident Rockstar

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

 

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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?

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.

 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

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:

image.png

 

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!

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.

 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

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)))),

 

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.