Forum Discussion

vsevolodts's avatar
vsevolodts
Frequent Visitor
8 years ago

Parsing multilevel JSON feed into PowerBI table 2

Hello, I am trying to parse a JSON that has multiple lists of values that needs to be transformed to cells/columns. A sample of the JSON could be obtained at https://shop.etudewines.com/ewinerysolu...
vsevolodts's avatar
vsevolodts
Frequent Visitor
8 years ago

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:

 


vsevolodts's avatar
vsevolodts
Frequent Visitor
8 years ago

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
Table

Step 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
   output

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