Forum Discussion
PBI Report issue when table is empty
That is likely because that FieldValuesAsText isnt' available when the table is empty - in other words, there is nothing to expand, so SharePoint returns nothing.
You may need to go back to your original query, but if it returns 0 records, you need to return a table using #table() that has all of the fields but with null for each field.
Or, why not build the report with the table populated, and then if there are no records, the refresh will just fail. I don't know if that is acceptable or not. I'd use another report to alert someone that there are no records in the table.
You are getting into some detailed M code here to overcome an empty report, which to me is bad data, and not something a report should be trying to compensate for. Not without being really good in M code to handle all possibilities. None of what you are trying can be done with the Power Query ribbon. All has to be manually edited.
That is because you are returning a 1 column table, so all of the columns vanish.
//If not, does the rest of the code
CheckEmpty = if Table.IsEmpty(#"8e95942a-9584-4215-b2c4-d7d911379cc4")
then
#table(
type table [Id = number],
{{null}}
)
else (
let
Just return the empty table with no records, then an error on the report that the table has 0 records with a COUNTROWS() measure. It will return BLANK(), not zero.
I will try this when I return to the office on Monday.