Parsing Heavily Nested JSON file - new to Power Query/M - help please?

[TedElk]

Hi,

I'm new to Power Query and the M language. I've been struggling for some time now to parse the "print_history", "waste_tracking_events", and "other_waste_tracking" data from the json file's structure (link to test file here). The 3rd layer, "systems", is in the way and I don't know how to get around it using the standard GUI functions. Here is the M code generated automatically when trying to grab all of of the "print_history" records. You can see that it will only retrieve the rows for 1 "system" instead of giving me the whole file's worth of data: 

let
Source = Json.Document(File.Contents("C:\Users\ted\20.json")),
data = Source[data],
systems = data[systems],
#"14012" = systems[14012],
print_history = #"14012"[print_history],
#"Converted to Table" = Table.FromList(print_history, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"location_name", "location_id", "item_name", "item_category", "barcode", "sku", "net_weight", "qr_code", "custom_field", "cogs", "list_price", "ingredients", "allergens", "unit_of_measure", "date_prepped", "date_prepped_timezone", "date_prepped_timezone_offset", "date_saved", "date_saved_timezone", "date_saved_timezone_offset", "qty", "expiration_date", "expiration_date_timezone", "expiration_date_timezone_offset", "prepped_qty", "waste_qty", "waste_units", "labels_printed", "sold_quantity", "total_cogs", "gross_value", "gross_profit_dollars", "gross_profit_percent", "srp_after_royalty", "net_profit_dollars", "net_profit_percent", "waste_dollars", "waste_percent", "waste_events"}, {"Column1.location_name", "Column1.location_id", "Column1.item_name", "Column1.item_category", "Column1.barcode", "Column1.sku", "Column1.net_weight", "Column1.qr_code", "Column1.custom_field", "Column1.cogs", "Column1.list_price", "Column1.ingredients", "Column1.allergens", "Column1.unit_of_measure", "Column1.date_prepped", "Column1.date_prepped_timezone", "Column1.date_prepped_timezone_offset", "Column1.date_saved", "Column1.date_saved_timezone", "Column1.date_saved_timezone_offset", "Column1.qty", "Column1.expiration_date", "Column1.expiration_date_timezone", "Column1.expiration_date_timezone_offset", "Column1.prepped_qty", "Column1.waste_qty", "Column1.waste_units", "Column1.labels_printed", "Column1.sold_quantity", "Column1.total_cogs", "Column1.gross_value", "Column1.gross_profit_dollars", "Column1.gross_profit_percent", "Column1.srp_after_royalty", "Column1.net_profit_dollars", "Column1.net_profit_percent", "Column1.waste_dollars", "Column1.waste_percent", "Column1.waste_events"})
in
#"Expanded Column1"

Do "systems", "print_history", "waste_tracking_events", and "other_waste_tracking" need to be parsed separetly and into different staging tables? It would appear that way, but wanted to make sure. I hope this is enough to go off of and thanks a lot for taking a look! Any nudge in the right direction would be much appreciated.

[TedElk]

I was being a n00b but i think i figured it out. thanks!

[lbendlin · Accepted solution]

Yes, you basically wandered into the wrong path.  This here will give you all the records under "other"

let
    Source = Json.Document(File.Contents("C:\Users\xxx\Downloads\TestData.json")),
    #"Converted to Table" = Table.FromRecords({Source}),
    #"Expanded data" = Table.ExpandRecordColumn(#"Converted to Table", "data", {"systems"}, {"data.systems"}),
    #"Expanded data.systems1" = Table.ExpandRecordColumn(#"Expanded data", "data.systems", {"other_waste_tracking"}){0}[other_waste_tracking][systems]
in
    #"Expanded data.systems1"

if you wanted to combine them with the other system then you would need to indicate how you want to do that as one of them has "print_history" and the others have "waste_history"