cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
New Member

Expand rows in one with unique items key in API

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.

 

i24IR.png

 

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.

 

TNPbx (1).png

 

You can get sample data Web using PowerBI at : https://5f96867311ab98001603ac53.mockapi.io/orders

Can you help me ?

Thank you.

 

1 REPLY 1
Super User III
Super User III

 

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"

 

 

Helpful resources

Announcements
secondImage

Happy New Year from Power BI

This is a must watch for a message from Power BI!

December Update

Check it Out!

Click here to read more about the December 2020 Updates!

Community Blog

Check it Out!

Click here to read the latest blog and learn more about contributing to the Power BI blog!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.