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.
I use API requests to retrieve data about events. If there are ongoing events, the source returns a table with columns and rows of data, and if not the API simply returns "null". I also present some visuals on a dashboard that work perfectly while there are ongoing events, but these break when the API returns "null" as they cannot find the columns they visualize. This also breaks the scheduled data refreshes in PBI Service.
My goal is that the visuals just display nothing instead of breaking, i.e. the table visuals shows an empty table and the map has no dots on it. Is this possible?
Example of logic I want to implement is:
if data is available -> show visual with data as normal
if data is not available -> show visual template without values instead of raising an error
Solved! Go to Solution.
@Anonymous You will need to modify your query so that if the API returns "null" that you still return a table with the columns. This should fix your data refresh issue as well as well as your visuals issue, something along the lines of:
let
Source = <some api call>
NextStep = if Source = null then Table.FromRecords( ... ) else Source
<rest of your steps>
in
<last step>
@Anonymous You will need to modify your query so that if the API returns "null" that you still return a table with the columns. This should fix your data refresh issue as well as well as your visuals issue, something along the lines of:
let
Source = <some api call>
NextStep = if Source = null then Table.FromRecords( ... ) else Source
<rest of your steps>
in
<last step>
@Greg_Deckler Thank you for your quick reply. I have now implemented the conditional part to the code. It is working in cases where there are events present, but when there are no events it gives the following error:
"The name 'col1' is already used for a column on table 'Query1'. Choose a different name."
Could it be because this part is considered a separate table? I would have thought that the condition would mean only one of the tables would ever exist at the same time, but the error persists.
Table.FromRecords( ... )
I can see in power query that both cases with and without events is working properly, producing a filled and an empty table respectively. The error appears only when I press close&apply.
@Anonymous Is it possible to share the code from Advanced Editor, probably don't need all of it. I need to see how I can test this.
@Greg_Deckler Turns out the problem was caused by some unrelated spagetti code, and I was able to get it working using your solution. Thank you for your help!
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 |
---|---|
112 | |
100 | |
80 | |
64 | |
57 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |