Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
hi,
first post here, wanted to thank for the chance to do it
im pretty new to power query but found it to do exactly what we need here - basically we are building dashboard for campaign stats where all the stats are recieved through the marketing platform rest API
what i tried to do is to build dynamic queries in power query that each time a user choose spesific campaign in the worksheet - it will query that spesific campaign stats from the API
the issue i encounter is that after i call the API and start expanding each column each step has unique id of his own and this ID`s also change between campaign, and when user change campaign i need the Table.ExpandRecordColumn to dynamic to exapnd the relevant ID`s to that campaign and im not able to achieve that
any idea how i can achieve this? (maybe there is better way to retrieve the JSON in first place?)
(JSON example and query code are below)
{ "data": { "name": "something", "total_stats": { "revenue": 0, "conversions": 94, "conversions1": 197, "conversions_by_entry_time": 192, "conversions1_by_entry_time": 203, "entries": 1056 }, "step_stats": { "067a4e67-c919-4ddb-b15f-a2fc25078690": { "name": "something", "revenue": 0, "conversions": 6, "conversions1": 14, "conversions_by_entry_time": 9, "conversions1_by_entry_time": 14, "messages": { "webhook": [ { "sent": 412, "unique_recipients": 412 } ] } }, "a7c49caf-ff07-46f2-a170-490f4bae2464": { "name": "something", "revenue": 0, "conversions": 0, "conversions1": 1, "conversions_by_entry_time": 0, "conversions1_by_entry_time": 1, "messages": { "webhook": [ { "unique_recipients": 1056, "sent": 1056 } ] } }, "0829260f-cfa7-42f2-8f6d-2cdc5964561f": { "name": "something", "revenue": 0, "conversions": 6, "conversions1": 30, "conversions_by_entry_time": 14, "conversions1_by_entry_time": 30, "messages": { "webhook": [ { "sent": 644, "unique_recipients": 644 } ] } }, "081cf255-20ab-40d6-a733-0010fa574722": { "name": "something", "revenue": 0, "conversions": 82, "conversions1": 152, "conversions_by_entry_time": 169, "conversions1_by_entry_time": 158, "messages": { "email": [ { "unique_recipients": 941, "sent": 941, "delivered": 937, "bounces": 9, "clicks": 40, "unique_clicks": 31, "reported_spam": 0, "opens": 210, "unique_opens": 146 } ] } } } }, "message": "success" }
full code for query:
let Source = Excel.CurrentWorkbook(){[Name="Canvas_starting_datesTable"]}[Content], #"Invoked Custom Function" = Table.AddColumn(Source, "steps stats", each #"Dynamic Canvas steps details"([Relevant Canvas], [formated creation date], [formated current date])), #"Filtered Rows" = Table.SelectRows(#"Invoked Custom Function", each ([index row] = null)), #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Relevant Canvas", "created date", "Today"}), #"Expanded steps stats" = Table.ExpandRecordColumn(#"Removed Columns", "steps stats", {"data"}, {"steps stats.data"}), #"Expanded steps stats.data" = Table.ExpandRecordColumn(#"Expanded steps stats", "steps stats.data", {"name", "total_stats", "variant_stats", "step_stats"}, {"steps stats.data.name", "steps stats.data.total_stats", "steps stats.data.variant_stats", "steps stats.data.step_stats"}) in #"Expanded steps stats.data"
Solved! Go to Solution.
According to your description, you already get data from API into Excel workbook. Now we want to dynamically select item then expand the corresponding records. Right?
In this scenario, you can retrieve the list of all user campaigns first. Convert it into a list.
Then you can create a query parameter with that list.
Now you can use Query Parameter to filter rows, and then expand the corresponding records. Please refer to link below:
POWER BI DESKTOP QUERY PARAMETERS, PART 1
Regards,
Wow both
JSON Formatter and validatter are working for me. It helped me for the website of family wellness
According to your description, you already get data from API into Excel workbook. Now we want to dynamically select item then expand the corresponding records. Right?
In this scenario, you can retrieve the list of all user campaigns first. Convert it into a list.
Then you can create a query parameter with that list.
Now you can use Query Parameter to filter rows, and then expand the corresponding records. Please refer to link below:
POWER BI DESKTOP QUERY PARAMETERS, PART 1
Regards,
Many many thanks
always good to learn something new
User | Count |
---|---|
102 | |
90 | |
80 | |
71 | |
70 |
User | Count |
---|---|
114 | |
100 | |
97 | |
72 | |
71 |