Forum Discussion

ElliotP's avatar
ElliotP
Post Prodigy
9 years ago
Solved

Nested JSON and never end Records

Afternoon,   I have a json file (https://1drv.ms/u/s!At8Q-ZbRnAj8hkJLL1cyU4t_hoHC) which has many nested arrays and I'm unsure of how to extract all of the records into powerbi.   I've watch the ...
Need Help
Show and Tell
Tutorial Requests
  • hugoberry's avatar
    hugoberry
    9 years ago

    Here is the final result that you can follow

     

    let
    json= Json.Document(File.Contents("D:\Downloads\Xero Datapowerbiforum")),
    json_tab = Table.FromList(json, Splitter.SplitByNothing()),
    expand_1 = Table.ExpandRecordColumn(json_tab, "Column1", {"JournalID", "JournalDate", "JournalNumber", "CreatedDateUTC", "SourceID", "SourceType", "JournalLines"}),
    expand_2 = Table.ExpandRecordColumn(expand_1, "JournalLines", {"JournalLine"}),
    journal_line_transform = Table.TransformColumns(expand_2, {"JournalLine", each if _ is record then {_}  else _}),
    expand_3 = Table.ExpandListColumn(journal_line_transform, "JournalLine"),
    expand_4 = Table.ExpandRecordColumn(expand_3, "JournalLine", {"JournalLineID", "AccountID", "AccountCode", "AccountType", "AccountName", "Description", "NetAmount", "GrossAmount", "TaxAmount", "TaxType", "TaxName"}, {"JournalLineID", "AccountID", "AccountCode", "AccountType", "AccountName", "Description", "NetAmount", "GrossAmount", "TaxAmount", "TaxType", "TaxName"})
in
    expand_4

     

ImkeF's avatar
ImkeF
Community Champion
9 years ago

Hi ElliotP,

yes, it's very unlikely that it makes sense to expand 2 different table structures into one column.

So you can add a column to your current query that you can use as a filter wit this:  

 

Value.Is([Column1.JournalLines.JournalLine], type table)

 

This checks if the type of the value is table and returns true or false.

Then you reference this table once and set a filter on true, and in a second query a filter on false.

 

Check out hugoberry's function, it is very promising.

 

hugoberry: Kudos to your function repository! Yesterday I've discovered your Matrix-multiplication - that's a real beauty! How do you do the indentation in your code? Manually or do you use a program?

hugoberry's avatar
hugoberry
Responsive Resident
9 years ago

Hi ElliotP, my initial reply was quite generic. Only now I had a chance to look at your JSON.

I've managed to drill down to the data that you were after. If I understand right the format of your data, at the step where the column becomes either a list or a record you have to apply a transofrmation of cell contents and cast them into a list, and then use standard expand procedures to expand the list values in the table.

Here is the step that will help:

= Table.TransformColumns(step, {"Column1.JournalLines.JournalLine", each if _ is record then {_}  else _})
  • hugoberry's avatar
    hugoberry
    Responsive Resident
    9 years ago

    Here is the final result that you can follow

     

    let
    json= Json.Document(File.Contents("D:\Downloads\Xero Datapowerbiforum")),
    json_tab = Table.FromList(json, Splitter.SplitByNothing()),
    expand_1 = Table.ExpandRecordColumn(json_tab, "Column1", {"JournalID", "JournalDate", "JournalNumber", "CreatedDateUTC", "SourceID", "SourceType", "JournalLines"}),
    expand_2 = Table.ExpandRecordColumn(expand_1, "JournalLines", {"JournalLine"}),
    journal_line_transform = Table.TransformColumns(expand_2, {"JournalLine", each if _ is record then {_}  else _}),
    expand_3 = Table.ExpandListColumn(journal_line_transform, "JournalLine"),
    expand_4 = Table.ExpandRecordColumn(expand_3, "JournalLine", {"JournalLineID", "AccountID", "AccountCode", "AccountType", "AccountName", "Description", "NetAmount", "GrossAmount", "TaxAmount", "TaxType", "TaxName"}, {"JournalLineID", "AccountID", "AccountCode", "AccountType", "AccountName", "Description", "NetAmount", "GrossAmount", "TaxAmount", "TaxType", "TaxName"})
in
    expand_4