Excel Power Query - Nested JSON and never end Records
- Get link
- X
- Other Apps
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?
Solved! Go to 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
- Get link
- X
- Other Apps
Comments
Post a Comment