Forum Discussion
Nested JSON and never end Records
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
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?
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?
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 _})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
Hey ImkeF thanks for mentioning that ;)
More posts/queries are coming. I do my identation manually, because I still can't make my visual studio code extension to work properlly. Once I get one in a more via ble stage I'll give you a heads up ;)