Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to 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"})
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"})
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
97 | |
85 | |
70 | |
61 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |