Forum Discussion
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/ewinerysolutionsproductfeed?startrow=60&maxrows=10; an example below is also provided under the spoiler.
I was able to simplify the view to a table that contains the names of parameters in the first column and the list of values in the second one:
let
Source = Json.Document(Web.Contents("https://shop.etudewines.com//ewinerysolutionsproductfeed?startrow=60&maxrows=10")),
DATA = Source[DATA],
#"Table" = Record.ToTable(DATA)
in
TablePlease advise how to fill the transponed table columns with values from the list
Spoiler
{
"ROWCOUNT":10,
"COLUMNS":[
"ROWNUMBER",
"OWNER",
"TYPE",
"INVENTORY",
"INVENTORY_OUTOFSTOCKMESSAGE",
"INVENTORY_OUTOFSTOCKOPTIONID",
"INVENTORY_COUNT",
"INVENTORY_ALLOCATED",
"INVENTORY_OUTOFSTOCKAT",
"INVENTORY_DISPLAYQUANTITYLEFTAT",
"COOKIE_DOMAIN",
"BOTTLESINACASE",
"MINPERORDER",
"MAXPERORDER",
"PRODUCTID",
"ISACTIVE",
"PRODUCTKEYID",
"PRODUCTNAME",
"SHORTNAME",
"PLAINNAME",
"MARKETINGURL",
"BRANDID",
"COSTPERBOTTLE",
"COSTPERCASE",
"COGS1",
"COGS2",
"PRODUCTTYPEID",
"PRICEUNIT2",
"KITBOTTLESIZEID",
"PRODUCTSKU",
"PRICE1",
"SALEPRICE1",
"PRICE2",
"SALEPRICE2",
"PRICEUNIT1",
"WEIGHT",
"PRODUCTPRICEID",
"ISSELLASBOTTLE",
"ISSELLASCASE",
"WINEBOTTLESIZE",
"WINEBOTTLESIZEID",
"WINEPRICECOUNT",
"PRICES"
],
"DATA":{
"ROWNUMBER":[
"60",
"61",
"62",
"63",
"64",
"65",
"66",
"67",
"68",
"69"
],
"OWNER":[
"Etude Wines",
"Etude Wines",
"Etude Wines",
"Etude Wines",
"Etude Wines",
"Etude Wines",
"Etude Wines",
"Etude Wines",
"Etude Wines",
"Etude Wines"
],
"TYPE":[
"ThirdPartyWine",
"ThirdPartyWine",
"ThirdPartyWine",
"ThirdPartyWine",
"ThirdPartyWine",
"ThirdPartyWine",
"ThirdPartyWine",
"ThirdPartyWine",
"ThirdPartyWine",
"ThirdPartyWine"
],
"INVENTORY":[
"0",
"0",
"0",
"0",
"0",
"306",
"305",
"7",
"70",
"55"
],
"INVENTORY_OUTOFSTOCKMESSAGE":[
"Temporarily out of stock",
"This item is temporarily unavailable.",
"This item is temporarily unavailable.",
"This item is temporarily unavailable.",
"This item is temporarily unavailable.",
"This product is currently unavailable.",
"This product is currently unavailable.",
"This item is temporarily unavailable.",
"This item is temporarily unavailable",
"This item is temporarily out of stock"
],
"INVENTORY_OUTOFSTOCKOPTIONID":[
"7ED94E06-16AF-44D5-83C3-815027891F0A",
"7ED94E06-16AF-44D5-83C3-815027891F0A",
"7ED94E06-16AF-44D5-83C3-815027891F0A",
"7ED94E06-16AF-44D5-83C3-815027891F0A",
"7ED94E06-16AF-44D5-83C3-815027891F0A",
"7ED94E06-16AF-44D5-83C3-815027891F0A",
"7ED94E06-16AF-44D5-83C3-815027891F0A",
"7ED94E06-16AF-44D5-83C3-815027891F0A",
"7ED94E06-16AF-44D5-83C3-815027891F0A",
"7ED94E06-16AF-44D5-83C3-815027891F0A"
],
"INVENTORY_COUNT":[
"0",
"0",
"0",
"0",
"0",
"306",
"305",
"14",
"70",
"58"
],
"INVENTORY_ALLOCATED":[
"0",
"0",
"0",
"0",
"0",
"0",
"0",
"7",
"0",
"3"
],
"INVENTORY_OUTOFSTOCKAT":[
"0",
"0",
"0",
"0",
"0",
"0",
"0",
"0",
"0",
"0"
],
"INVENTORY_DISPLAYQUANTITYLEFTAT":[
"12",
"0",
"0",
"0",
"0",
"0",
"0",
"0",
"0",
"0"
],
"COOKIE_DOMAIN":[
"etudewines.com",
"etudewines.com",
"etudewines.com",
"etudewines.com",
"etudewines.com",
"etudewines.com",
"etudewines.com",
"etudewines.com",
"etudewines.com",
"etudewines.com"
],
"BOTTLESINACASE":[
"12",
"12",
"12",
"12",
"12",
"12",
"12",
"12",
"12",
"12"
],
"MINPERORDER":[
"0",
"0",
"0",
"0",
"0",
"0",
"0",
"0",
"0",
"0"
],
"MAXPERORDER":[
"0",
"0",
"0",
"0",
"0",
"0",
"0",
"0",
"0",
"0"
],
"PRODUCTID":[
"E62EC10C-FD91-D899-22BE-5C7C6F0EA37F",
"513E8171-BB7C-EB92-F8FA-05C299FC9C38",
"E62EDEB9-046C-37D6-AD84-FD89ACC627B9",
"1E37D222-AF23-B81C-5F6A-F1FC08681E96",
"3EA8EDE7-D46F-42E1-B614-6478B5299628",
"B1CCE701-B490-4EC3-BEDA-E3EECB9E3E37",
"C5360AAE-7163-4413-B497-479CC7D2F274",
"26ACDACB-06B1-481C-8F55-BCC0DB43F596",
"C8DAAA8F-2381-4E73-A494-2B5913F26A85",
"C1B19CF3-ED47-46DB-BF79-08398CB8C87F"
],
"ISACTIVE":[
"1",
"1",
"1",
"1",
"1",
"1",
"1",
"1",
"1",
"1"
],
"PRODUCTKEYID":[
"E628E26D-CE8C-2B55-CC3C-B37EE55ED977",
"51053E0E-CAB7-191A-471F-07E9A0798949",
"E62A2E7B-F03E-6D6A-CF8F-113B83E17747",
"1E35AC74-CEE2-588A-0687-3CFC135BA997",
"6B7CBF9E-0B7E-453F-A668-F0D66C61504A",
"7E84AE98-5A5A-4982-B5D1-0711798ACD8B",
"DDCB5CEE-7209-415F-95D2-84BED4937745",
"EDE5F755-EBE1-41CC-9746-4EEAB9625FBF",
"9442007F-48D8-447F-AEE2-F8CF8711411E",
"7F62A94E-2EA1-4397-8EE2-2BFFCB50246D"
],
"PRODUCTNAME":[
"2012 Etude Pinot Noir Fiddlestix Santa Rita Hills",
"2012 Etude Pinot Noir Lyric Santa Barbara County",
"2012 Etude Pinot Noir North Canyon Santa Maria Valley",
"2012 Etude Pinot Noir Rose Carneros",
"2012 Etude Temblor Pinot Noir Carneros",
"2013 Etude Cabernet Sauvignon Napa Valley",
"2013 Etude Cabernet Sauvignon Oakville Napa Valley",
"2013 Etude Carneros Chardonnay",
"2013 Etude Carneros Pinot Blanc",
"2013 Etude Carneros Pinot Noir"
],
"SHORTNAME":[
"2012 Etude Pinot Noir Fiddlest",
"2012 Etude Pinot Noir Lyric Sa",
"2012 Etude North Canyon Pinot ",
"2012 Etude Pinot Noir Rose Car",
"2012 Etude Temblor Pinot Noir ",
"53981",
"2013 Etude Cabernet Sauvignon ",
"2013 Etude Carneros Chardonnay",
"5644",
"5639"
],
"PLAINNAME":[
"",
"",
"",
"",
"",
"",
"",
"",
"",
""
],
"MARKETINGURL":[
"2012-Etude-Pinot-Noir-Fiddlestix",
"",
"2012-Etude-Pinot-Noir-Santa-Maria-Valley",
"2012-Etude-Pinot-Noir-Rose-Carneros",
"",
"",
"2013_Etude_Cabernet_Sauvignon_Oakville_Napa_Valley",
"2013-Chardonnay-Carneros",
"",
"2013-Carneros-Pinot-Noir"
],
"BRANDID":[
"",
"",
"",
"",
"",
"",
"",
"",
"",
""
],
"COSTPERBOTTLE":[
"0",
"",
"0",
"0",
"0",
"0",
"0",
"0",
"0",
"0"
],
"COSTPERCASE":[
"0",
"",
"0",
"0",
"0",
"0",
"0",
"0",
"0",
"0"
],
"COGS1":[
"0",
"0",
"0",
"0",
"0",
"0",
"0",
"0",
"0",
"0"
],
"COGS2":[
"0",
"0",
"0",
"0",
"0",
"0",
"0",
"0",
"0",
"0"
],
"PRODUCTTYPEID":[
"DBB58DC2-65C8-4FCC-BD38-1190FC5B52FA",
"DBB58DC2-65C8-4FCC-BD38-1190FC5B52FA",
"DBB58DC2-65C8-4FCC-BD38-1190FC5B52FA",
"DBB58DC2-65C8-4FCC-BD38-1190FC5B52FA",
"DBB58DC2-65C8-4FCC-BD38-1190FC5B52FA",
"DBB58DC2-65C8-4FCC-BD38-1190FC5B52FA",
"DBB58DC2-65C8-4FCC-BD38-1190FC5B52FA",
"DBB58DC2-65C8-4FCC-BD38-1190FC5B52FA",
"DBB58DC2-65C8-4FCC-BD38-1190FC5B52FA",
"DBB58DC2-65C8-4FCC-BD38-1190FC5B52FA"
],
"PRICEUNIT2":[
"Case",
"Case",
"Case",
"Case",
"Case",
"Case",
"Case",
"Case",
"Case",
"Case"
],
"KITBOTTLESIZEID":[
"",
"",
"",
"",
"",
"",
"",
"",
"",
""
],
"PRODUCTSKU":[
"444474",
"446222",
"449662",
"429615",
"441178",
"453315",
"454616",
"448042",
"448041",
"449145"
],
"PRICE1":[
"45",
"25",
"45",
"28",
"75",
"85",
"125",
"32",
"28",
"65"
],
"SALEPRICE1":[
"0",
"",
"0",
"0",
"0",
"0",
"0",
"0",
"0",
"0"
],
"PRICE2":[
"540",
"300",
"540",
"336",
"900",
"1020",
"1500",
"384",
"336",
"780"
],
"SALEPRICE2":[
"0",
"",
"0",
"0",
"0",
"0",
"0",
"0",
"0",
"0"
],
"PRICEUNIT1":[
"Bottle",
"Bottle",
"Bottle",
"Bottle",
"Bottle",
"Bottle",
"Bottle",
"Bottle",
"Bottle",
"Bottle"
],
"WEIGHT":[
"0",
"0",
"0",
"0",
"0",
"1",
"1",
"1",
"1",
"1"
],
"PRODUCTPRICEID":[
"",
"",
"",
"",
"",
"",
"",
"",
"",
""
],
"ISSELLASBOTTLE":[
"1",
"1",
"1",
"1",
"1",
"1",
"1",
"1",
"1",
"1"
],
"ISSELLASCASE":[
"1",
"1",
"1",
"1",
"1",
"1",
"1",
"1",
"1",
"1"
],
"WINEBOTTLESIZE":[
"750 ml",
"750 ml",
"750 ml",
"750 ml",
"750 ml",
"750 ml",
"750 ml",
"750 ml",
"750 ml",
"750 ml"
],
"WINEBOTTLESIZEID":[
"98B06108-668E-46C6-9359-8721F290051D",
"98B06108-668E-46C6-9359-8721F290051D",
"98B06108-668E-46C6-9359-8721F290051D",
"98B06108-668E-46C6-9359-8721F290051D",
"98B06108-668E-46C6-9359-8721F290051D",
"98B06108-668E-46C6-9359-8721F290051D",
"98B06108-668E-46C6-9359-8721F290051D",
"98B06108-668E-46C6-9359-8721F290051D",
"98B06108-668E-46C6-9359-8721F290051D",
"98B06108-668E-46C6-9359-8721F290051D"
],
"WINEPRICECOUNT":[
"0",
"0",
"0",
"0",
"0",
"0",
"0",
"0",
"0",
"0"
],
"PRICES":[
"",
"",
"",
"",
"",
"",
"",
"",
"",
""
]
}
}Thank you
4 Replies
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:
