Forum Discussion

rjrn's avatar
rjrn
Frequent Visitor
7 years ago
Solved

Error when SharePoint list is empty

I am pulling data from a SharePoint list (on premisse SharePoint), but when the list has no lines, I get an error "The column 'ID' of the table wasn't found".   let Source = SharePoint.Tables("...
  • rjrn's avatar
    7 years ago

    After a lot of digging and testing, I found a solution to my issue. Sharing here for everybody else.

     

    let
        Source = SharePoint.Tables("https://SharePoint/List/", [ApiVersion = 15]),
    
        //Define the list ID
        #"SPSource" = Source{[Id="22acf0c7-df1f-4e36-b373-97f0aba7dd61"]}[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(#"SPSource") then #table( type table [Id = number], {{null}} ) else ( let #"Renamed Columns" = Table.RenameColumns(#"SPSource",{{"ID", "ID.1"}}), #"Expanded FieldValuesAsText" = Table.ExpandRecordColumn(#"Renamed Columns", "FieldValuesAsText", {"AssignedTo"}, {"FieldValuesAsText.AssignedTo"}), #"Removed Other Columns" = Table.SelectColumns(#"Expanded FieldValuesAsText",{"Id", "Title", "Modified", "Created", "Status", "Comment", "Priority", "StartDate", "DueDate", "PercentComplete", "FieldValuesAsText.AssignedTo"}), #"Added Custom" = Table.AddColumn(#"Removed Other Columns", "Region", each "XXXX"), #"Added Custom1" = Table.AddColumn(#"Added Custom", "AI", each "XXXXAINAME") in #"Added Custom1" ) in CheckEmpty