Forum Discussion

lopezbec's avatar
lopezbec
Advocate I
4 years ago
Solved

PBI Report issue when table is empty

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...
Need Help
  • edhans's avatar
    edhans
    4 years ago

    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.

edhans's avatar
edhans
Community Champion
4 years ago

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.

lopezbec's avatar
lopezbec
Advocate I
4 years ago

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's avatar
    edhans
    Community Champion
    4 years ago

    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.

    • lopezbec's avatar
      lopezbec
      Advocate I
      4 years ago

      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's avatar
        edhans
        Community Champion
        4 years ago

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

         

    • lopezbec's avatar
      lopezbec
      Advocate I
      4 years ago

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

    • lopezbec's avatar
      lopezbec
      Advocate I
      4 years ago

      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's avatar
        edhans
        Community Champion
        4 years ago

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