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

     

astoz's avatar
astoz
Frequent Visitor
4 years ago

Hello, this post is really useful thanks, It helped me solving an issue I had

I'd like to check my understanding of this piece of code in the solution:

if _ is record then {_} else _}

 

Am I right in assuming that here it says if current value is record then store it in an object else go to the next value? I'm asking cause I'm not yet entirely clear on what _ (underscore) means in Power Query