Excel Power Query - Nested JSON and never end Records

From  Solved: Nested JSON and never end Records - Microsoft Fabric Community


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 guyinacube video (incredibly helpful), but I get to a point where I can't expand my columns any further yet I need the data inside that record (it's actually the data I want).

 

First step: https://gyazo.com/86e411d5de9d7f2f52813f6f66cb3bf9

Convert to table, easy it converts out to this.

 

Second Step: https://gyazo.com/546206f9a6b0459a4a09b2c865c3d902

Expand out, again comfortable. Expand again.

 

Third Step (issue): https://gyazo.com/ffe36ce86fa91f4ae0a435c524d026e5

The column on the far right is the colum which contains the data that i want, but I'm unable to retrieve the data from that column without individually clicking on the links.

 

Any ideas as to how I would be to produce rows or tables from this?

1 ACCEPTED SOLUTION

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

 

View solution in original post

Comments

Popular posts from this blog

Add GitHub Repository to DBeaver CE Secured with SSH Protocol

Keyboard Shortcut to "Toggle Line Comments" in DBeaver

DBeaver Shortcuts