The view after all transformations looks like this:
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:
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.
Helpful resources
Microsoft Fabric Learn Together
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Power BI Monthly Update - April 2024
Check out the April 2024 Power BI update to learn about new features.
Fabric Community Update - April 2024
Find out what's new and trending in the Fabric Community.
