Forum Discussion

cgeraeds's avatar
cgeraeds
Advocate I
7 years ago
Solved

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 ...
Need Help
  • ImkeF's avatar
    ImkeF
    7 years ago

    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's avatar
cgeraeds
Advocate I
7 years ago

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's avatar
ImkeF
Community Champion
7 years ago

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-power-bi-and-power-query/

 

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's avatar
    cgeraeds
    Advocate I
    7 years ago

    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's avatar
      ImkeF
      Community Champion
      7 years ago

      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's avatar
        cgeraeds
        Advocate I
        7 years ago

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