Showing results for 
Search instead for 
Did you mean: 
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 :


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.


TNPbx (1).png


You can get sample data Web using PowerBI at :

Can you help me ?

Thank you.


Super User III
Super User III


Find whoever created that structure and slap them in the face with a wet towel. Repeatedly.



    Source = Json.Document(Web.Contents("")),
    #"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"})
    #"Removed Other Columns1"



Helpful resources


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!.