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
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?
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 ;)
