Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
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.
Solved! Go to 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"
I was being a n00b but i think i figured it out. thanks!
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"
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
70 | |
66 | |
24 | |
18 | |
13 |