Expand rows in one with unique items key in API

[Anonymous]

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.

 

[lbendlin]

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"