Forum Discussion

SimonKibsgaard's avatar
SimonKibsgaard
Helper I
9 years ago
Solved

Parsing JSON with differing children

Hi I have a PowerQuery that consumes a json feed. This usally works really well. But, now I have added a new property to my json ("openForSale") that is a simple true/false and not an item like the ...
Need Help
  • SimonKibsgaard's avatar
    SimonKibsgaard
    9 years ago

    yay!

    I got it working.

     

    let json = Json.Document(Web.Contents("https://thewebsite.com/the.json")),
    organizationLevel = Record.ToTable(Source),
    campaignLevel = Table.AddColumn(organizationLevel , "Campaign", each Record.ToTable([Value])),
    campaignLevelExp = Table.ExpandTableColumn(campaignLevel, "Campaign", {"Name", "Value"}, {"CampaignName", "Campaign.Value"}),
    itemLevel = Table.ExpandRecordColumn(campaignLevelExp, "Campaign.Value", {"item1"}, {"item1"}),
    itemLevelExp = Table.ExpandRecordColumn(itemLevel, "item1", {"openForSale", "items"}, {"openForSale", "items"}),

    itemsToList = Table.TransformColumns(itemLevelExp,{{"items", Record.ToList}}),
    itemsToListExp = Table.ExpandListColumn(itemsToList, "items"),

    allItems = Table.ExpandRecordColumn(itemsToListExp, "items", {"name"}, {"name"})

SimonKibsgaard's avatar
SimonKibsgaard
Helper I
9 years ago

yay!

I got it working.

 

let json = Json.Document(Web.Contents("https://thewebsite.com/the.json")),
organizationLevel = Record.ToTable(Source),
campaignLevel = Table.AddColumn(organizationLevel , "Campaign", each Record.ToTable([Value])),
campaignLevelExp = Table.ExpandTableColumn(campaignLevel, "Campaign", {"Name", "Value"}, {"CampaignName", "Campaign.Value"}),
itemLevel = Table.ExpandRecordColumn(campaignLevelExp, "Campaign.Value", {"item1"}, {"item1"}),
itemLevelExp = Table.ExpandRecordColumn(itemLevel, "item1", {"openForSale", "items"}, {"openForSale", "items"}),

itemsToList = Table.TransformColumns(itemLevelExp,{{"items", Record.ToList}}),
itemsToListExp = Table.ExpandListColumn(itemsToList, "items"),

allItems = Table.ExpandRecordColumn(itemsToListExp, "items", {"name"}, {"name"})