Forum Discussion
Parsing multilevel JSON feed into PowerBI table 2
In this scenario, if you want to extract values from each list and crossjoin the Name column, you just need to expand the Value column into New Rows.
Regards,
Hi v-sihou-msft, Thank you for the reply.
Expanded columns are giving me key-valuespairs as column cells; I want to transform the first column into headers and expand lists to columns (each list contains values, each list has the same length).
Here is an example of what I am trying to get:

- vsevolodts8 years agoFrequent Visitor
I found a very non-elegant solution using recommendation of Eric_Zhang from this post. The idea is to create indexed array from each list and merge them using index as a key.
Step 1: create a base data set from external JSON:let Source = Json.Document(Web.Contents("https://shop.etudewines.com//ewinerysolutionsproductfeed?startrow=0&maxrows=2000")), DATA = Source[DATA], #"Table" = Record.ToTable(DATA) in TableStep 2: Create data sets from lists (one per list you want to expand) and assign index.
let Source = Source, Value = Source{1}[Value], #"Converted to Table" = Table.FromList(Value, Splitter.SplitByNothing(), null, null, ExtraValues.Error), #"Added Index" = Table.AddIndexColumn(#"Converted to Table", "Index", 1, 1), Winery = Table.RenameColumns(#"Added Index",{{"Column1", "Winery"}}) in Winery
Step 3: Merge all data sets from Step 2 using Index as key one-to-one:let Source = Table.NestedJoin(Winery,{"Index"},Type,{"Index"},"Type",JoinKind.Inner), typex = Table.ExpandTableColumn(Source, "Type", {"Type"}, {"Type"}), inventory = Table.NestedJoin(typex,{"Index"},Inventory,{"Index"},"Inventory",JoinKind.Inner), inv = Table.ExpandTableColumn(inventory, "Inventory", {"Inventory"}, {"Inventory"}), inventory_allocated = Table.NestedJoin(inv,{"Index"},Allocated,{"Index"},"Allocated",JoinKind.Inner), allocated = Table.ExpandTableColumn( inventory_allocated, "Allocated", {"Allocated"}, {"Allocated"}), scu = Table.NestedJoin(allocated,{"Index"},SKU,{"Index"},"SKU",JoinKind.Inner), scu_= Table.ExpandTableColumn( scu, "SKU", {"SKU"}, {"SKU"}), name = Table.NestedJoin(scu_,{"Index"},Name,{"Index"},"Name",JoinKind.Inner), name_= Table.ExpandTableColumn( name, "Name", {"Name"}, {"Name"}), priceb = Table.NestedJoin(name_,{"Index"},Price_bottle,{"Index"},"Price, bottle",JoinKind.Inner), pricec = Table.NestedJoin(priceb,{"Index"},Price_case,{"Index"},"Price, case",JoinKind.Inner), #"Expanded Price, bottle" = Table.ExpandTableColumn(pricec, "Price, bottle", {"Price"}, {"Price, bottle"}), #"Expanded Price, case" = Table.ExpandTableColumn(#"Expanded Price, bottle", "Price, case", {"Price, Case"}, {"Price, case"}), output = Table.ReorderColumns(#"Expanded Price, case",{"Index", "Winery", "SKU", "Name", "Type", "Inventory", "Allocated", "Price, bottle", "Price, case"}) in outputAfter all manipulations you will have a new table:
I don't like this solution because instead of a loop you need to hardcode every data set manually.
Please advice if it possible to build a function and then just pass values to itterate over the array of lists.