Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
TedElk
New Member

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

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.

 

1 ACCEPTED SOLUTION
lbendlin
Super User
Super User

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"

 

lbendlin_0-1699109230662.png

 

View solution in original post

2 REPLIES 2
TedElk
New Member

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

lbendlin
Super User
Super User

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"

 

lbendlin_0-1699109230662.png

 

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors