I am running a query in Power BI on a large SharePoint list of over 10,000 rows. In preview everything works but when I attempt to load the table, I get 404 errors in my data for rows where I know the SharePoint list has data. The 404 errors are being thrown for the columns Last Name, First Name, Account Number, eeee, and ffff. The data in all of these columns are strings and do exist in the SharePoint list the query is referencing so I do not understand these 404 errors. The code for the query is below. Can anyone advise on how to solve this issue?
let
siteurl = "https://***365.sharepoint.us/sites/###-##-^^^/", // use ... /sites/<your site name>/<your subsite name> if applicable
listname = "Reimbursement Tracking",
//Comment in only one of the fieldselect lines below, defining your select and expand columns using the example syntax shown
//fieldselect = "&$top=5000", // all fields with no expansion
fieldselect = "&$top=5000&$expand=^^^Reviewer0,^^^Reviewer,CO_x0020_Approver,Account_x0020_Number,PaymentMDH&$select=Id,Title,Request_x0020_Status,Last_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", // all fields with no expansion
//fieldselect = "&$top=5000&$select=ID,Title,Date,PersonColumn,ChoiceColumn,LookupColumn", // list desired fields (no expansion) -No Spaces!
//fieldselect = "&$top=5000&$select=ID,Title,Date,PersonColumn/LastName,PersonColumn/FirstName,ChoiceColumn,LookupColumn/Title,LookupColumn/Project,LookupColumn/ProjectStatus&$expand=PersonColumn,LookupColumn", //expand list fields - No Spaces!
InitialWebCall = Json.Document(Web.Contents(siteurl, [RelativePath = "_api/web/lists/GetByTitle('" & listname & "')/items?$skipToken=Paged=TRUE" & fieldselect, Headers = [Accept = "application/json"]])),
datalist = List.Generate(() => InitialWebCall, each List.Count([value]) > 0, each try Json.Document(Web.Contents(siteurl, [RelativePath = "_api" & Text.AfterDelimiter([odata.nextLink], "_api"), 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", "^^^_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", "^^^_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"}),
#"Expanded Account_x0020_Number" = Table.ExpandRecordColumn(#"Expanded Column2", "Account_x0020_Number", {"AccountNumber"}, {"AccountNumber"}),
#"Expanded ^^^Reviewer" = Table.ExpandRecordColumn(#"Expanded Account_x0020_Number", "^^^Reviewer", {"Title"}, {"Title.1"}),
#"Expanded CO_x0020_Approver" = Table.ExpandRecordColumn(#"Expanded ^^^Reviewer", "CO_x0020_Approver", {"Title"}, {"Title.2"}),
#"Expanded PaymentMDH" = Table.ExpandRecordColumn(#"Expanded CO_x0020_Approver", "PaymentMDH", {"Title"}, {"Title.3"}),
#"Expanded ^^^Reviewer0" = Table.ExpandRecordColumn(#"Expanded PaymentMDH", "^^^Reviewer0", {"Title"}, {"Title.4"}),
#"Merge Query with ^^^ Provider Reference" = Table.NestedJoin(#"Expanded ^^^Reviewer0", {"^^^_x0020_Provider"}, #"Providers Reference", {"^^^ Providers"}, "Providers Reference", JoinKind.LeftOuter),
#"Expanded Providers Reference" = Table.ExpandTableColumn(#"Merge Query with ^^^ Provider Reference", "Providers Reference", {"^^^ Providers_clean"}, {"Providers Reference.^^^ Providers_clean"}),
#"Replaced Value-^^^ Providers_clean" = Table.ReplaceValue(#"Expanded Providers Reference",null,"No Provider",Replacer.ReplaceValue,{"Providers Reference.^^^ Providers_clean"}),
#"Replaced Value1--Total_x0020_Reimbursement_x0020_ & future_fy" = Table.ReplaceValue(#"Replaced Value-^^^ Providers_clean",null,0,Replacer.ReplaceValue,{"Total_x0020_Reimbursement_x0020_", "future_fy", "Total_x0020_Reimbursement_x0020_0", "Has_prorated_receipts"}),
#"Changed Type" = Table.TransformColumnTypes(#"Replaced Value1--Total_x0020_Reimbursement_x0020_ & future_fy",{{"Total_x0020_Reimbursement_x0020_", Currency.Type}, {"Total_x0020_Reimbursement_x0020_0", Currency.Type}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Id","ID Reference"}, {"Title","^^^ Request ID"}, {"Request_x0020_Status","Request Status"}, {"Last_x0020_Name","Last Name"}, {"^^^_x0020_Provider","^^^ Provider"}, {"Total_x0020_Reimbursement_x0020_","Amount Approved"}, {"Total_x0020_Reimbursement_x0020_0","Total Paid"}, {"Request_x0020_Date","Submitted Date"}, {"DataValidatedDate","Data Validated Date"}, {"ProgramApprovedDate","Program Approved Date"}, {"PaymentPendingDate","Payment Pending Date"}, {"Request_x0020_Completed_x0020_Da","Request Completed Date"}, {"Account_x0020_NumberId","UFMS Account Lookup"}, {"ReviewStatus","ReviewStatus"}, {"Has_prorated_receipts","Has_prorated_receipts"}, {"Prorated^^^RequestID","Prorated^^^RequestID"}, {"Title.3","MDH No."}, {"Title.4","^^^ Approver"}, {"future_fy","future_fy"}, {"Title.1","^^^ Reviewer"}, {"Title.2","Authorized Funds Certifying Official"}, {"AccountNumber","Account Number"}, {"Providers Reference.^^^ Providers_clean", "^^^ Providers_clean"}}),
#"Changed Type--datetime and int columns" = Table.TransformColumnTypes(#"Renamed Columns",{{"Submitted Date", type datetime}, {"Request Completed Date", type datetime}, {"Data Validated Date", type datetime}, {"Program Approved Date", type datetime}, {"Payment Pending Date", type datetime}, {"ID Reference", Int64.Type}, {"future_fy", Int64.Type}, {"Has_prorated_receipts", Int64.Type}}),
#"Removed Duplicates" = Table.Distinct(#"Changed Type--datetime and int columns", {"^^^ Request ID"})
in
#"Removed Duplicates"
