Forum Discussion
rjrn
7 years agoFrequent Visitor
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("...
- 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