Power BI调用SharePoint REST API分页查询出现404错误求助

核心问题排查与修复步骤 #

1. 分页请求逻辑错误（最可能原因） #

当前初始请求手动添加$skipToken=Paged=TRUE不符合SharePoint REST API分页规范，导致后续分页请求丢失字段选择/展开参数，触发404。

• 修改初始请求，移除手动$skipToken，让API自动返回第一页数据和分页链接：

  InitialWebCall = Json.Document(Web.Contents(siteurl, [RelativePath = "_api/web/lists/GetByTitle('" & listname & "')/items?" & fieldselect, Headers = [Accept = "application/json"]])),
  

• 修正分页链接提取逻辑，避免路径拼接错误：

  datalist = List.Generate(() => InitialWebCall, 
                          each List.Count([value]) > 0, 
                          each try Json.Document(Web.Contents(siteurl, [RelativePath = Text.AfterDelimiter([odata.nextLink], siteurl), Headers = [Accept = "application/json"]])) otherwise [value = {}], 
                          each [value]),
  

2. 缺失字段的$select配置 #

你提到的First Name列未在fieldselect中配置，SharePoint REST API仅返回$select指定字段，未指定字段会导致加载错误：

• 找到First Name列的内部名称（SharePoint列表设置→列设置查看，通常为First_x0020_Name），添加到$select参数中：

  fieldselect = "&$top=5000&$expand=^^^Reviewer0,^^^Reviewer,CO_x0020_Approver,Account_x0020_Number,PaymentMDH&$select=Id,Title,Request_x0020_Status,Last_x0020_Name,First_x0020_Name,^^^_x0020_Provider,...",
  

• 同时在Expanded Column2步骤中添加该字段的展开配置，确保后续能映射到First Name列。

3. Lookup/人员列的空值处理 #

当Account Number等Lookup列存在空值时，展开操作可能触发隐性错误，建议添加空值检查：

• 在展开Account_x0020_Number前，替换空记录为默认值：

  #"Replaced Null Records" = Table.ReplaceValue(#"Expanded Column2", null, [AccountNumber = null], Replacer.ReplaceValue, {"Account_x0020_Number"}),
  

4. 验证列内部名称一致性 #

检查eeee、ffff列的内部名称是否与$select配置一致，SharePoint列显示名称和内部名称可能不同（带空格列会被替换为_x0020_），确保$select使用正确的内部名称。

完整修改后的核心代码片段 #

let
siteurl = "https://***365.sharepoint.us/sites/###-##-^^^/", 
listname = "Reimbursement Tracking",
fieldselect = "&$top=5000&$expand=^^^Reviewer0,^^^Reviewer,CO_x0020_Approver,Account_x0020_Number,PaymentMDH&$select=Id,Title,Request_x0020_Status,Last_x0020_Name,First_x0020_Name,^^^_x0020_Provider,Total_x0020_Reimbursement_x0020_,Total_x0020_Reimbursement_x0020_0,Request_x0020_Date,DataValidatedDate,ProgramApprovedDate,PaymentPendingDate,Request_x0020_Completed_x0020_Da,Account_x0020_NumberId,ReviewStatus,Has_prorated_receipts,Prorated^^^RequestID,future_fy,^^^Reviewer0/Title,^^^Reviewer/Title,CO_x0020_Approver/Title,Account_x0020_Number/AccountNumber,PaymentMDH/Title",
InitialWebCall = Json.Document(Web.Contents(siteurl, [RelativePath = "_api/web/lists/GetByTitle('" & listname & "')/items?" & fieldselect, Headers = [Accept = "application/json"]])),
datalist = List.Generate(() => InitialWebCall, 
                        each List.Count([value]) > 0, 
                        each try Json.Document(Web.Contents(siteurl, [RelativePath = Text.AfterDelimiter([odata.nextLink], siteurl), Headers = [Accept = "application/json"]])) otherwise [value = {}], 
                        each [value]),
#"Converted to Table" = Table.FromList(datalist, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandListColumn(#"Converted to Table", "Column1"),
#"Expanded Column2" = Table.ExpandRecordColumn(#"Expanded Column1", "Column1", {"Account_x0020_Number", "^^^Reviewer", "CO_x0020_Approver", "PaymentMDH", "^^^Reviewer0", "Id", "Title", "Request_x0020_Status", "Last_x0020_Name", "First_x0020_Name", "^^^_x0020_Provider", "Total_x0020_Reimbursement_x0020_", "Request_x0020_Date", "Total_x0020_Reimbursement_x0020_0", "Account_x0020_NumberId", "Request_x0020_Completed_x0020_Da", "DataValidatedDate", "ProgramApprovedDate", "PaymentPendingDate", "ReviewStatus", "Has_prorated_receipts", "Prorated^^^RequestID", "future_fy"}, {"Account_x0020_Number", "^^^Reviewer", "CO_x0020_Approver", "PaymentMDH", "^^^Reviewer0", "Id", "Title", "Request_x0020_Status", "Last_x0020_Name", "First_x0020_Name", "^^^_x0020_Provider", "Total_x0020_Reimbursement_x0020_", "Request_x0020_Date", "Total_x0020_Reimbursement_x0020_0", "Account_x0020_NumberId", "Request_x0020_Completed_x0020_Da", "DataValidatedDate", "ProgramApprovedDate", "PaymentPendingDate", "ReviewStatus", "Has_prorated_receipts", "Prorated^^^RequestID", "future_fy"}),
#"Replaced Null Records" = Table.ReplaceValue(#"Expanded Column2", null, [AccountNumber = null], Replacer.ReplaceValue, {"Account_x0020_Number"}),
#"Expanded Account_x0020_Number" = Table.ExpandRecordColumn(#"Replaced Null Records", "Account_x0020_Number", {"AccountNumber"}, {"AccountNumber"}),
// 后续步骤保持不变...
in
#"Removed Duplicates"

内容的提问来源于stack exchange，提问作者Wesley Young