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

The view after all transformations looks like this:

  • v-sihou-msft's avatar
    v-sihou-msft
    Microsoft Employee
    8 years ago

    vsevolodts

     

    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,

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