Forum Discussion
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("https://SharePoint/List/", [ApiVersion = 15]),
#"22acf0c7-df1f-4e36-b373-97f0aba7dd61" = Source{[Id="22acf0c7-df1f-4e36-b373-97f0aba7dd61"]}[Items],
#"Renamed Columns" = Table.RenameColumns(#"22acf0c7-df1f-4e36-b373-97f0aba7dd61",{{"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 "MCLA"),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "AI", each "MCLA3 - Top Deals sniper mode")
in
#"Added Custom1"The error is raised on the line #"Renamed Colums", since the Source on the line above returns no records.
Please help.
Regards, RN
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
3 Replies
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
