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,
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
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 | |
100 | |
75 | |
73 | |
49 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |