Error when SharePoint list is empty

[rjrn]

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

[rjrn · Accepted solution]

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

[lopezbec]

@rjrn I am new to PBI.  I am having the same issue, but looking at your text above, I am unsure how to edit my query.  Where do you get the APIVersion for the list as well as the list ID and the ID field number?

[lopezbec]

@rjrn actually I figured it out after I did some clean up on my table.