cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
DaleHohm Regular Visitor
Regular Visitor

How to retrieve "correct" column names from SharePoint lists

When you load data from a SharePoint list, the columns are named using an "Internal" name rather than the "Display" name that exposed when interacting with SharePoint. 

 

Is there a PowerQuery recipe that can be used to retrieve the display names and overwrite the internal names while importing into Power BI?

 

For example, when I retrieve a list from SharePoint, the names of the columns often do not match the column names displayed in SharePoint.  For example:

 

"Primary Geography Country" is brought in as "Primary Geography Co"

"Launch: Overall Readiness" is brought in as "Launch: Overall_x002"

"Complete" gets brought is as "AllDone" (because I had originally called the column AllDone and then changed it)

"Introduction" gets brought in as "Title" (because I changed Title to Introduction)

 

I am hopeful that the display names are actually in the data, so I'm looking for a recipe to drill into the nested lists and records in what is returned for the list to get the display names.  My hope is that these are stored consistently in the data structures, regardless of the type of data each column holds.

 

Dale

 

p.s. This is a repost of a question in a thread that took a wrong turn: https://community.powerbi.com/t5/Desktop/SharePoint-Lists-how-to-get-Display-names/m-p/483977#M22534...

6 REPLIES 6
Community Support Team
Community Support Team

Re: How to retrieve "correct" column names from SharePoint lists

Hi Dale,

 

I have reported this issue to the Product Team. I will update here later.

 

Best Regards,

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Community Support Team
Community Support Team

Re: How to retrieve "correct" column names from SharePoint lists

Hi Dale,

 

The report ID is CRI 79818387.

 

Best Regards,

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
DaleHohm Regular Visitor
Regular Visitor

Re: How to retrieve "correct" column names from SharePoint lists

Thanks very much Dale!

 

Is there a way for me to watch this CRI or get feedback if/when it is updated?

 

Dale

Community Support Team
Community Support Team

Re: How to retrieve "correct" column names from SharePoint lists

Hi Dale,

 

I will update the latest information here.

 

Best Regards,

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
DaleHohm Regular Visitor
Regular Visitor

Re: How to retrieve "correct" column names from SharePoint lists

OK, I found the column display name information... it is in ContentType, Fields, Title (really?  obviously the last place I looked).

 

Power Query and Power BI should make it easy to surface these names!

 

let
    //SPListURL is the URL of sharepoint site
    Source = SharePoint.Tables(SPListURL, [ApiVersion = 15]),
    //ListTitle is the name of Sharepoint list
    SPList = Source{[Title=ListTitle]}[Items],
    #"Removed Other Columns" = Table.SelectColumns(SPList,{"ContentType"}),
    ContentType = #"Removed Other Columns"{0}[ContentType],
    Fields = ContentType[Fields],
    #"Removed Other Columns1" = Table.SelectColumns(Fields,{"InternalName", "Title"})
in
    #"Removed Other Columns1"
Mixylplyx Regular Visitor
Regular Visitor

Re: How to retrieve "correct" column names from SharePoint lists

For those that are interested, I use this tool extensively. It shows the InternalName and Title fields side-by-side, along with TypeAsString. It's invaluable to me in using Power BI and SharePoint O365 lists. I simply copy the Title field from the tool and paste it into Power BI that used the InternalName for the column name. It also is a big help in assigning the correct data type to the column, since the default in Power BI is 'Any' for SP data.

If only Power BI would utilize the InternalName behind the scenes to keep columns straight, but use the Title field as the actual column name displayed. The tool makes it easy, but it's still laborious to copy and paste for every column. And while I'm wishing, it be nice to auto-map the data types as well.