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.
I have the following problem :
I have a list of orders in API REST :
[ {"order_id":1505277, "products": {"856805359": {"item_id":"856805359", "product_id":6, "price":5}, "2662034394": {"item_id":"2662034394", "product_id":2, "price":12} } }, {"order_id":1505278, "products": {"856805359": {"item_id":"856815359", "product_id":4, "price":3}, "2662034394": {"item_id":"2662134394", "product_id":5, "price":5} } }]
The item key "812147904" is different each time depending the orders so I cannot expand rows easily to create a table. I get multiple columns instead of one.
My goal is to have a table of Orders (any) in Power Query / Power BI where I have order_id, item_id, product_id and price.
You can get sample data Web using PowerBI at : https://5f96867311ab98001603ac53.mockapi.io/orders
Can you help me ?
Thank you.
Find whoever created that structure and slap them in the face with a wet towel. Repeatedly.
let
Source = Json.Document(Web.Contents("https://5f96867311ab98001603ac53.mockapi.io/orders")),
#"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"order_id", "products"}, {"order_id", "products"}),
#"Added Custom" = Table.AddColumn(#"Expanded Column1", "Custom", each Record.ToTable([products])),
#"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Name", "Value"}, {"Name", "Value"}),
#"Expanded Value" = Table.ExpandRecordColumn(#"Expanded Custom", "Value", {"item_id", "product_id", "price"}, {"item_id", "product_id", "price"}),
#"Removed Other Columns1" = Table.SelectColumns(#"Expanded Value",{"order_id", "item_id", "product_id", "price"})
in
#"Removed Other Columns1"
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
14 | |
2 | |
2 | |
1 | |
1 |