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.
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.