SharePoint Column Names Appear Corrupted When Importing to PBI

[llhanley]

Microsoft Power BI Desktop Version: 2.57.5068.501 64-bit (April 2018)

I am working with SharePoint Online lists. I have 2 lists in one site that I want to import. The first list imports fine. The second list is coming in to preview with random column names. Power BI is giving me 4 character random names for the columns rather than the actual column names. The person who created the list created the with actual column names.

What's going on here? I did a query in Excel and the columns are correct there. I don't have the ability to get to the list in Power BI Online - it's blocked by our security team. I can only access them on PBI Desktop.

I ran Windows Update. I rebooted the machine. I'm still getting the same weird 'corrupted' column names.

Thanks!

[v-yuezhe-msft]

@llhanley,

I am unable to reproduce your issue, please connect to the specific online list using OData feed entry in Power BI Desktop and check if the issue still persists. You can use the OData URL: https://yoursharepointsite/_vti_bin/listdata.svc/ListName. 

Regards,
Lydia

[llhanley]

Hi Lydia,

It appears that my company has access via OData blocked.

Sorry...

Regards,

Lori

[llhanley]

I re-created the list and that one imports fine. The other is still showing the weird 4 character 'random' column names. Same ones as before, but not the actual column names. I had a coworker try to connect and she got the same result with that particular list. Other lists in the site are fine. I don't get it.

[v-yuezhe-msft]

@llhanley,

Does this list contain special columns? How about you use Power BI Desktop May release?

Regards,
Lydia

[llhanley · Accepted solution]

Hi Lydia,

It's a SharePoint problem. I need to go post this on their board and let them figure out what it's doing.  Something might have gotten corrupted when the list was being created. To explain how I got here - My URL is showing the corrupted display names for those columns rather than the ones showing in the browser when I pull up the list. My Location column is showing Field=uwbz (what I'm getting in PBI) rather than Field=Location (displaying in browser).

For everyone else...  if you experience a similar problem, to tell if it's PBI or SharePoint, look at the URL when you edit the column.

SharePoint columns have an "internal" and a "display" name. This allows you to name columns like a DBA would and then go back and add a more friendly name for your users later. For example, 'Loc' is the internal name for my 'Location' column.  In PBI, you will see the 'internal' name when you import the SharePoint list. This is true for both an on-premise or a hosted SharePoint environment. This is where you'd see 'Loc' instead of 'Location' in PBI.

Let me deconstruct the URL.

1. In O365, you have your tenant FQDN and then .sharepoint.com. In my case, it's my company name in the sharepoint.com domain.

2. Next is an OOB site collection or host-named site collection name.

3. This is followed by the Site Name.

4. /layouts/15 is where all of the core pages for the SharePoint site live on the server. In this case, it's the page to edit the field (FldEdit.aspx).

5. This is followed by the GUID for the List in curly brackets. %7B and %7D are the hex codes for curly brackets in web publishing.

6. Once SharePoint knows which list you're editing, it pulls up the field for you by its Display Name. In this case, it's 'Location.'

https://mycompany.sharepoint.com/sitecollection/sitename/_layouts/15/FldEdit.aspx?List=%7BGUIDGUID-G...Field=Location

If Field= shows anything other than your column's display name, there's a problem in SharePoint.

For More Info 

O365 URLs and Site Addresses

https://support.office.com/en-us/article/office-365-urls-and-ip-address-ranges-8548a211-3fe7-47cb-ab...

SharePoint URLs for Administrators

https://www.fpweb.net/sharepoint-blog/your-sharepoint-url-list-an-administrators-guid/

W3Schools HTML URL Encoding Reference

https://www.w3schools.com/tags/ref_urlencode.asp