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

     

ElliotP's avatar
ElliotP
Post Prodigy
9 years ago

I know all of the records will have the same structure (columns names and data types). Would it be best to somehow create their own table and expand if possible somehow there?