Parsing JSON with differing children

[SimonKibsgaard]

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

What I would like to output into my table is:

| campaign  | open for sale | name      |

| campaign1 | true          | triangles |

| campaign1 | true          | circles   |

| campaign1 | true          | squares   |

| campaign2 |               | lines     |

How my json looks

{

  "campaign1" : {

    "item1" : {"name" : "triangles", ...},

    "item2" : {"name" : "circles", ...},

    "item3" : {"name" : "squares", ...},

    "openForSale" : true 

  },

  "campaign2": {

    "item17" : {"name" : "lines", ...}

  }

}

How my PowerQuery, that used to work looks (start of it)

let json = Json.Document(Web.Contents("https://thewebsite.com/the.json")),
level0 = Record.ToTable(json),

campaignLevel = Table.AddColumn(level0, "campaignLevel", each Record.ToTable([Value])),
campaignLevelExp = Table.ExpandTableColumn(campaignLevel, "campaignLevel", {"Name", "Value"}, {"CampaignName", "Campaign.Value"}),

itemLevel = Table.AddColumn(campaignLevelExp, "itemLevel", each Record.ToTable([Campaign.Value])),
itemLevel _exp = Table.ExpandTableColumn(itemLevel, "itemLevel", {"Name", "Value"}, {"Team", "itemLevel.Value"})

...

I believe the answer is in the line starting with "itemLevel =" where I should be able filter instead of iterating "each Record...".

Help is very much appreciated!

[SimonKibsgaard · Accepted solution]

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"})