Forum Discussion

cgeraeds's avatar
cgeraeds
Icon for Advocate I rankAdvocate 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
Icon for Advocate I rankAdvocate 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
Icon for Community Champion rankCommunity 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
    Icon for Advocate I rankAdvocate 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)))),