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.
edhans When this was removed, I received this error.
let
Source = SharePoint.Tables("https://inspirewellness.sharepoint.com/sites/SPARQInternal", [Implementation=null, ApiVersion=15]),
#"8e95942a-9584-4215-b2c4-d7d911379cc4" = Source{[Id="8e95942a-9584-4215-b2c4-d7d911379cc4"]}[Items],
#"Renamed Columns" = Table.RenameColumns(#"8e95942a-9584-4215-b2c4-d7d911379cc4",{{"ID", "ID.1"}}),
#"Removed Other Columns" = Table.SelectColumns(#"Renamed Columns",{"Title", "Modified", "Created", "Department", "ReviewDate", "ApprovalStartDate", "ApprovalStatus", "ApprovalDueDate", "DateApproved", "WorkflowComments", "ReminderEmailSent", "DateRejected", "Delete Date", "EscalationCount", "No Reminder Emails", "FieldValuesAsText", "Folder", "ApprovalSentTo", "FinalApprover", "SPARQProcessOwner"}),
#"Changed Type" = Table.TransformColumnTypes(#"Removed Other Columns",{{"Modified", type date}, {"Created", type date}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"ReviewDate"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Removed Columns",{{"ApprovalStartDate", type date}, {"ApprovalDueDate", type date}, {"DateApproved", type date}, {"ReminderEmailSent", type date}, {"DateRejected", type date}, {"Delete Date", type date}, {"EscalationCount", Int64.Type}, {"No Reminder Emails", Int64.Type}}),
#"Expanded FieldValuesAsText" = Table.ExpandRecordColumn(#"Changed Type1", "FieldValuesAsText", {"ID", "Title", "Modified", "Created", "Author", "Editor", "WorkflowInstanceID", "Department", "ReviewDate", "ApprovalStartDate", "ApprovalStatus", "ApprovalSentTo", "ApprovalDueDate", "FinalApprover", "DateApproved", "WorkflowComments", "ReminderEmailSent", "ProcessName", "DateRejected", "Delete_x0020_Date", "EscalationCount", "No_x0020_Reminder_x0020_Emails", "SPARQProcessOwner"}, {"FieldValuesAsText.ID", "FieldValuesAsText.Title", "FieldValuesAsText.Modified", "FieldValuesAsText.Created", "FieldValuesAsText.Author", "FieldValuesAsText.Editor", "FieldValuesAsText.WorkflowInstanceID", "FieldValuesAsText.Department", "FieldValuesAsText.ReviewDate", "FieldValuesAsText.ApprovalStartDate", "FieldValuesAsText.ApprovalStatus", "FieldValuesAsText.ApprovalSentTo", "FieldValuesAsText.ApprovalDueDate", "FieldValuesAsText.FinalApprover", "FieldValuesAsText.DateApproved", "FieldValuesAsText.WorkflowComments", "FieldValuesAsText.ReminderEmailSent", "FieldValuesAsText.ProcessName", "FieldValuesAsText.DateRejected", "FieldValuesAsText.Delete_x0020_Date", "FieldValuesAsText.EscalationCount", "FieldValuesAsText.No_x0020_Reminder_x0020_Emails", "FieldValuesAsText.SPARQProcessOwner"}),
#"Removed Columns1" = Table.RemoveColumns(#"Expanded FieldValuesAsText",{"FieldValuesAsText.ID", "Title"}),
#"Renamed Columns1" = Table.RenameColumns(#"Removed Columns1",{{"FieldValuesAsText.Title", "Title No"}}),
#"Removed Columns2" = Table.RemoveColumns(#"Renamed Columns1",{"Modified", "Created"}),
#"Renamed Columns2" = Table.RenameColumns(#"Removed Columns2",{{"FieldValuesAsText.Modified", "Modified Date"}, {"FieldValuesAsText.Created", "Created Date"}, {"FieldValuesAsText.Author", "Author"}, {"FieldValuesAsText.Editor", "Editor"}}),
#"Removed Columns3" = Table.RemoveColumns(#"Renamed Columns2",{"FieldValuesAsText.WorkflowInstanceID", "Department"}),
#"Renamed Columns3" = Table.RenameColumns(#"Removed Columns3",{{"FieldValuesAsText.Department", "Department"}, {"FieldValuesAsText.ReviewDate", "Review Date"}}),
#"Changed Type2" = Table.TransformColumnTypes(#"Renamed Columns3",{{"Review Date", type date}}),
#"Removed Columns4" = Table.RemoveColumns(#"Changed Type2",{"ApprovalStartDate"}),
#"Renamed Columns4" = Table.RenameColumns(#"Removed Columns4",{{"FieldValuesAsText.ApprovalStartDate", "Approval Start Date"}}),
#"Removed Columns5" = Table.RemoveColumns(#"Renamed Columns4",{"ApprovalStatus"}),
#"Renamed Columns5" = Table.RenameColumns(#"Removed Columns5",{{"FieldValuesAsText.ApprovalStatus", "Status"}, {"FieldValuesAsText.ApprovalSentTo", "Approval Sent To"}}),
#"Removed Columns6" = Table.RemoveColumns(#"Renamed Columns5",{"ApprovalDueDate"}),
#"Renamed Columns6" = Table.RenameColumns(#"Removed Columns6",{{"FieldValuesAsText.ApprovalDueDate", "Due Date"}}),
#"Changed Type3" = Table.TransformColumnTypes(#"Renamed Columns6",{{"Due Date", type date}, {"Approval Start Date", type date}}),
#"Renamed Columns7" = Table.RenameColumns(#"Changed Type3",{{"FieldValuesAsText.DateApproved", "Date Approved"}}),
#"Changed Type4" = Table.TransformColumnTypes(#"Renamed Columns7",{{"Date Approved", type date}}),
#"Renamed Columns8" = Table.RenameColumns(#"Changed Type4",{{"FieldValuesAsText.WorkflowComments", "Comments"}}),
#"Removed Columns7" = Table.RemoveColumns(#"Renamed Columns8",{"DateApproved", "WorkflowComments"}),
#"Renamed Columns9" = Table.RenameColumns(#"Removed Columns7",{{"FieldValuesAsText.FinalApprover", "Approver"}, {"FieldValuesAsText.ReminderEmailSent", "Reminder Date"}, {"FieldValuesAsText.ProcessName", "Description"}, {"FieldValuesAsText.DateRejected", "Rejected Date"}}),
#"Changed Type5" = Table.TransformColumnTypes(#"Renamed Columns9",{{"Rejected Date", type date}}),
#"Removed Columns8" = Table.RemoveColumns(#"Changed Type5",{"ReminderEmailSent", "DateRejected", "Delete Date"}),
#"Renamed Columns10" = Table.RenameColumns(#"Removed Columns8",{{"FieldValuesAsText.Delete_x0020_Date", "Delete Date"}}),
#"Changed Type6" = Table.TransformColumnTypes(#"Renamed Columns10",{{"Delete Date", type date}}),
#"Renamed Columns11" = Table.RenameColumns(#"Changed Type6",{{"FieldValuesAsText.EscalationCount", "Escalation Count"}}),
#"Changed Type7" = Table.TransformColumnTypes(#"Renamed Columns11",{{"Escalation Count", Int64.Type}}),
#"Removed Columns9" = Table.RemoveColumns(#"Changed Type7",{"EscalationCount", "No Reminder Emails"}),
#"Renamed Columns12" = Table.RenameColumns(#"Removed Columns9",{{"FieldValuesAsText.No_x0020_Reminder_x0020_Emails", "No Reminder Emails"}}),
#"Changed Type8" = Table.TransformColumnTypes(#"Renamed Columns12",{{"No Reminder Emails", Int64.Type}}),
#"Renamed Columns13" = Table.RenameColumns(#"Changed Type8",{{"FieldValuesAsText.SPARQProcessOwner", "SPARQ Process Team"}}),
#"Removed Columns10" = Table.RemoveColumns(#"Renamed Columns13",{"ApprovalSentTo", "FinalApprover", "SPARQProcessOwner", "Folder"}),
#"Changed Type9" = Table.TransformColumnTypes(#"Removed Columns10",{{"Reminder Date", type date}})
in
#"Changed Type9"
I don't know why there is no ID column, but you don't need it anyway. Change the first few lines to this:
let Source = SharePoint.Tables("https://inspirewellness.sharepoint.com/sites/SPARQInternal", [Implementation=null, ApiVersion=15]), #"8e95942a-9584-4215-b2c4-d7d911379cc4" = Source{[Id="8e95942a-9584-4215-b2c4-d7d911379cc4"]}[Items], #"Removed Other Columns" = Table.SelectColumns(#"8e95942a-9584-4215-b2c4-d7d911379cc4",{"Title", "Modified",This is still not working. I rebuilt my query to remove all of the extra stuff not needed. When the list is empty, I now get this error below, if I remove that row, it errors on the next.
Query:
let
Source = SharePoint.Tables("https://inspirewellness.sharepoint.com/sites/SPARQInternal", [Implementation=null, ApiVersion=15]),
#"8e95942a-9584-4215-b2c4-d7d911379cc4" = Source{[Id="8e95942a-9584-4215-b2c4-d7d911379cc4"]}[Items],
#"Removed Other Columns" = Table.SelectColumns(#"8e95942a-9584-4215-b2c4-d7d911379cc4",{"FieldValuesAsText"}),
#"Expanded FieldValuesAsText" = Table.ExpandRecordColumn(#"Removed Other Columns", "FieldValuesAsText", {"Modified", "Created", "Department", "ReviewDate", "ApprovalStartDate", "ApprovalStatus", "ApprovalSentTo", "ApprovalDueDate", "FinalApprover", "DateApproved", "WorkflowComments", "ReminderEmailSent", "ProcessName", "DateRejected", "Delete_x0020_Date", "EscalationCount", "No_x0020_Reminder_x0020_Emails", "SPARQProcessOwner", "EscalationDate"}, {"FieldValuesAsText.Modified", "FieldValuesAsText.Created", "FieldValuesAsText.Department", "FieldValuesAsText.ReviewDate", "FieldValuesAsText.ApprovalStartDate", "FieldValuesAsText.ApprovalStatus", "FieldValuesAsText.ApprovalSentTo", "FieldValuesAsText.ApprovalDueDate", "FieldValuesAsText.FinalApprover", "FieldValuesAsText.DateApproved", "FieldValuesAsText.WorkflowComments", "FieldValuesAsText.ReminderEmailSent", "FieldValuesAsText.ProcessName", "FieldValuesAsText.DateRejected", "FieldValuesAsText.Delete_x0020_Date", "FieldValuesAsText.EscalationCount", "FieldValuesAsText.No_x0020_Reminder_x0020_Emails", "FieldValuesAsText.SPARQProcessOwner", "FieldValuesAsText.EscalationDate"}),
#"Renamed Columns" = Table.RenameColumns(#"Expanded FieldValuesAsText",{{"FieldValuesAsText.Department", "Department"}, {"FieldValuesAsText.ReviewDate", "Review Date"}, {"FieldValuesAsText.ApprovalStartDate", "Approval Start Date"}, {"FieldValuesAsText.ApprovalStatus", "Status"}, {"FieldValuesAsText.ApprovalSentTo", "Approval Sent To"}, {"FieldValuesAsText.ApprovalDueDate", "Due Date"}, {"FieldValuesAsText.FinalApprover", "Approver"}, {"FieldValuesAsText.DateApproved", "Approved Date"}, {"FieldValuesAsText.WorkflowComments", "Comments"}, {"FieldValuesAsText.ReminderEmailSent", "Reminder Email"}, {"FieldValuesAsText.ProcessName", "Description"}, {"FieldValuesAsText.DateRejected", "Rejected Date"}, {"FieldValuesAsText.Delete_x0020_Date", "Delete Date"}, {"FieldValuesAsText.EscalationCount", "Escalation Count"}, {"FieldValuesAsText.No_x0020_Reminder_x0020_Emails", "Nbr Reminder Emails"}, {"FieldValuesAsText.SPARQProcessOwner", "Process Team Mbr"}, {"FieldValuesAsText.EscalationDate", "Escalation Date"}}),
#"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Escalation Date", type date}, {"Nbr Reminder Emails", Int64.Type}, {"Escalation Count", Int64.Type}, {"Delete Date", type datetime}, {"Rejected Date", type date}, {"Reminder Email", type date}, {"Approved Date", type date}, {"Due Date", type date}, {"Approval Start Date", type date}, {"Review Date", type date}})
in
#"Changed Type"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.