Solved: Re: PBI Report issue when table is empty

lopezbec · ‎03-22-2022

I created a PBI report that is using a SharePoint list. Everything was working fine until I deleted the data from the list and I got a error in PBI that a column was missing, so I added some details in the query to check to see if the table was empty. I also added a message to the report using a measure that if the table was empty, it would display a custom message that no data was available for the report.

When testing it with with data in the list it worked fine, and then deleting the data in the list and refreshing my PBI report, I noticed my table set up in my report would disappear and I would have to recreate the visual.

Any ideas on what I may be doing wrong? Let me know what information would be helpful in aiding my troubleshooting.

edhans · ‎03-28-2022

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.

Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling

Proud to be a Super User!

MCSA: BI Reporting

View solution in original post

v-henryk-mstf · ‎03-25-2022

Hi @lopezbec ,

Whether the advice given by @edhans has solved your confusion, if the problem has been solved you can mark the reply for the standard answer to help the other members find it more quickly. If not, please point it out.

Looking forward to your feedback.

Best Regards,
Henry

edhans · ‎03-22-2022

You'd need to show us how it looks in Power Query with and without data. Or maybe share the M code for that list. I can say for certian that if you are doing some sort of unpivot or pivot operation that will cause then when the table is empty, but there can be other reasons too.

Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling

Proud to be a Super User!

MCSA: BI Reporting

lopezbec · ‎03-22-2022

@edhans

Here is my report before I delete the data in the list.

Selection order:

all values are in the report visual, I originally had a table and changed to a Matrix:

Here is what I get after I delete the data in the table and refresh the report.

Visual set up has been removed.

Measure:

NoData Measure = IF(ISEMPTY('Process Review Status'),"This page was intentionally left blank")

I haven't been able to get this message to show, so I just updated the card fields to display the message you see above.

Main Query:

let
Source = SharePoint.Tables("https:SHAREPOINT SOURCE IS HERE", [Implementation=null, ApiVersion=15]),
#"8e95942a-9584-4215-b2c4-d7d911379cc4" = Source{[Id="8e95942a-9584-4215-b2c4-d7d911379cc4"]}[Items],
//Check if Source is an empty table
//If yes, returns a table with a single row/column "Id=null"
//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
#"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")
in
CheckEmpty

edhans · ‎03-22-2022

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.

Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling

Proud to be a Super User!

MCSA: BI Reporting

lopezbec · ‎03-28-2022

@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"

edhans · ‎03-28-2022

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",

Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling

Proud to be a Super User!

MCSA: BI Reporting

lopezbec · ‎03-28-2022

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"

edhans · ‎03-28-2022

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.

Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling

Proud to be a Super User!

MCSA: BI Reporting

lopezbec · ‎03-25-2022

I will try this when I return to the office on Monday.

lopezbec · ‎03-22-2022

Ok that makes sense, but I am new to this and have googled how to return the empty table, but cannot find how to add that in my query. Any Assistance with that would be appreciated.

edhans · ‎03-22-2022

You are already returning the table. Just don't check for records in Power Query. Get rid of all of this:

//Check if Source is an empty table
//If yes, returns a table with a single row/column "Id=null"
//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

Oh, and that last
in
CheckEmpty

thing too. Your final code should probalby be:

let
Source = SharePoint.Tables("https:SHAREPOINT SOURCE IS HERE", [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")

Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling

Proud to be a Super User!

MCSA: BI Reporting

PBI Report issue when table is empty

Helpful resources

Power BI Monthly Update - October 2024

Microsoft Fabric & AI Learning Hackathon

Fabric Community Update - October 2024