Data not loading from Sharepoint List to PowerBI for 2 columns

[Flip89]

Dear all,

I have columns that are showing errors in Power Query in Desktop PowerBI as shown below. I have tried cleaning access permisions. Did not help.

HPPP SPOC field type is "Single choice Person" and I get 404 error.

It seems URL is wrong, As it shows in xml when I past "OData__x006a_is1Id" in the browser. In Advanced Editor I have "OData_jis1Id" so I am lost why the URL is reffering to field without "Id" at the end and how to change that. If I change column name to "OData__x006a_is1Id" in Advanced Editor of PowerQuery it throws me "column not found error". Chicken and egg... Attaching Advanced Editor source at the bottom. List xml too long. I may paste it in another post if needed.

Action owner email field type is "Multiple choice Person" and throws error 500. Looks similar... I have data under "Action_x0020_owner_x0020_emailId" and it looks under Action_x0020_owner_x0020_email.

Advanced Editor:

let
Source = SharePoint.Tables("XXXXXXXXXXX", [ApiVersion = 15]),
#"f4a67e47-b7b2-44ec-af3b-aeb6ff11ef0f" = Source{[Id="f4a67e47-b7b2-44ec-af3b-aeb6ff11ef0f"]}[Items],
#"Removed Columns" = Table.RemoveColumns(#"f4a67e47-b7b2-44ec-af3b-aeb6ff11ef0f",{"FileSystemObjectType", "Id", "ServerRedirectedEmbedUri", "ServerRedirectedEmbedUrl", "l2rtId", "l2rtStringId", "OData_jis1Id", "jis1StringId", "ContentTypeId", "ComplianceAssetId", "ID", "Modified", "Created", "AuthorId", "EditorId", "OData__UIVersionString", "Attachments", "GUID", "FirstUniqueAncestorSecurableObject", "RoleAssignments", "AttachmentFiles", "ContentType", "GetDlpPolicyTip", "FieldValuesAsHtml", "FieldValuesAsText", "FieldValuesForEdit", "File", "Folder", "LikedByInformation", "ParentList", "Properties", "Versions", "Author", "Editor", "MSM owner emailId", "MSM owner emailStringId", "Period1 of reduced_x", "Period1 % of_x", "Period1 last order_x", "Period2 of reduced_x", "Period2 % of_x", "Period2 last order_x", "Period3 of reduced_x", "Period3 % of_x", "Period3 last order_x", "Period1 of transport", "Period1 transportation_x00", "Period1 last ship_x0", "Period1 first ship_x", "Period2 of transport", "Period2 transportation_x00", "Period2 last ship_x0", "Period2 first ship_x", "Period3 of transport", "Period3 transportation_x00", "Period3 last ship_x0", "Period3 first ship_x", "Action owner emailId", "Action owner emailStringId"}),
#"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"l2rt", "MSM Owner"}, {"Title", "Supplier"}, {"blabla", "Email"}, {"Shave Care0", "Shave Care"}, {"mjwu", "Risk flagged?"}, {"Test", "Reduction periods and capacity"}, {"OData_qhn4", "Reason for cap reduction"}, {"OData_pve4", "How will ensure coverage?"}, {"fpfn", "Additional info cap red"}, {"Transportation constraints", "Transportation constraints and plants impacted"}, {"Last and first_x0020", "Last & First delivery dates"}, {"v4ll", "Reason for transp constraint"}, {"ftio", "How sup will ensure on-time deliveries?"}, {"OData_kbd8", "Additional information"}, {"ryhw", "Risk description"}, {"vmvz", "Risk assessment"}, {"hysr", "Mitigation action description"}, {"OData_sgs5", "Action owner role"}}),
#"Expanded MSM Owner" = Table.ExpandRecordColumn(#"Renamed Columns", "MSM Owner", {"Title", "EMail"}, {"MSM Owner.Title", "MSM Owner.EMail"}),
#"Changed Type" = Table.TransformColumnTypes(#"Expanded MSM Owner",{{"Added line?", type text}, {"Flow to PSC Sh", type text}, {"Action due date", type date}, {"Marktheidenfeld", type text}})
in
#"Changed Type"

[Flip89]

Hi Ed!

Not sure what y mean? It is showing me exactly as in screenshots above, no matter if I click on "Record" or "Table" or white space within same field.

Data is there. Find below extract of list item from web. "L2rt" being "MSM Owner email" column from above screenshot - column that works:

So when I check "l2rt" online I get the object as below:

When I do the same for "OData__x00a_is1" I get an error though data is there:

Similar for the Action owner email column. Will not paste here as I assume problem is of similar nature.

Cheers!

Filip

[edhans]

If you click on the white area next to the yellow word Record or Table, it will show you the contents of that record/table below. 

Either way, this doesn't appear to be a Power BI issue, but something you need to get with your SharePoint admin and see what is going on with these fields in the list.

[edhans]

Can you click on the white area next to the yellow "Record" label? It will show you in the window below what is in that record specifically. If there is nothing, there is your issue. If there is something, then it gets more complex as it could e that record has data, but another record is returning the error message.