Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
Anonymous
Not applicable

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...

1 ACCEPTED SOLUTION
Anonymous
Not applicable

I've now encapsulated this into a function.  See the comments for usage example:

 

fnRealSpColumnNames

(sharePointSite, listName)=>
let
    // After connecting to a SharePoint list, pass in the SharePoint site name and list name to this function inside a Table.RenameColumns call.
    // The function returns the InternalName and DisplayName of each of the list column names.
    // For example:
    //  = Table.RenameColumns(#"previous step name", fnRealSpColumnNames("https://xxx.sharepoint.com/teams/yoursite/", "Your ListName"), MissingField.Ignore)
    Source = OData.Feed(sharePointSite & "/_api/web/lists/GetByTitle('" & listName & "')/Fields?$select=Title,InternalName", null, [Implementation="2.0"]),
    recs = Table.ToRecords(Source),
    lsts = List.Transform(recs, each Record.ToList(_))
in
    lsts

 

View solution in original post

26 REPLIES 26
angel4199
Frequent Visitor

easier way:
let
Source = OData.Feed("https://yoursite/_vti_bin/ListData.svc", null, [Implementation="2.0"]),
List1 = Source{[Name="yourlist",Signature="table"]}[Data]
in
List1

Anonymous
Not applicable

Fortunately, Power BI has a solution to this now. Just select Implementation as "2.0" while extracting the list.

You are my hero! Thank you soooo much

 

MattRo
Frequent Visitor

Be careful with 2.0. I have found that the connection will not work with 2.0 if the list gets over 5000 items. It works beautifully, and then when it crosses 5000, error. And then I have to switch to 1.0.

This is PERFECT Thanks

works perfectly, use SharePoint Online List and then 2.0

 

thx

@srs1111 , It seems that SharePoint Online List 2.0 has a 5000 row limit. Have you found that? My import worked fine, until the list grew larger than 5000 rows. Now I get an error:

DataSource.Error: Web.Contents failed to get contents from 'https://sharepoint site.com/_api/Web/Lists(guid'dac00115-cc34-4d41-8583-xxxxxxxx')/RenderListDataAsStream' (500): Internal Server Error

 

I can switch to 1.0 and the connection can be made, but 1.0 returns different columns and junks my whole set of applied step.

v-chuncz-msft
Community Support
Community Support

@Anonymous 

 

SharePooint API provides only internal columns names and we use this names in queries.

If a user create a column with exact name and later decide to change it - we could not be able to display the new name. SharePoint API will still return the original name.

Here could be a workaround: create a new column with desired name, copy data to it and remove the old column.

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

I am experiencing the same issue, please do the following steps.

 

Edit Query-->Source

Change ApiVersion = 14

= SharePoint.Tables("your sharepoint site address", [ApiVersion = 14])

 

To get the correct column name of sharepoint listTo get the correct column name of sharepoint list

This worked!  My sharepoint online list pulled names in PBI were all scrambled.  changing the source to  [ApiVersion = 14]) and repicking my list in the applied steps (somehow) unscrambled the column names.  Not smooth, but this did create a work around!

How did this work?
I tried and didn't understand what next to do.

go to your pulled list (transform data area) in powerbi. click source (cog wheel) in applied steps....change the apiversion source to 14.  as you step back up in your steps, you may have to repick your SP list.  that is what i did and names from SP list loaded proper. 

Thanks abdul_Kochi for this easy solution. 😊👍

Anonymous
Not applicable

@abdul_Kochi spot on thanks!

Anonymous
Not applicable

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.

Anonymous
Not applicable

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"
Anonymous
Not applicable

Using an OData query, I've found a another way of getting the display name (aka Title) and the InternalName of list columns.  This works even with Survey lists (my previous solution did not).

 

Create an OData query of the form:

https://xxxx.sharepoint.com/your-site/_api/web/lists/GetByTitle('your-list')/Fields?$select=Title,InternalName

 

use $select=* to see all of the metadata available.

 

Dale

I there,  could you please provide more detail on how to do this?

 

Thanks

Anonymous
Not applicable

@Hayleysea I'll presume you have already brought in your SharePoint List into Power Query.

 

Create a new query called RealNames using the OData connector and the Odata query as I defined before. This will bring you a table with columns InternalName and Title (aka RealName).  Transform this into a list or records, then the records into lists.  The items in this final list become the name-value pairs for your rename operation.

 

let
Source = OData.Feed("your-tenant/yoursite/_api/web/lists/GetByTitle('yourlist')/Fields?$select=Title,InternalName", null, [Implementation="2.0"]),
recs = Table.ToRecords(Source),
lsts = List.Transform(recs, each Record.ToList(_))
in
lsts

 

Then back in your query where you brought in the SharePoint list, append this line:

 

= Table.RenameColumns(#"your previous step name", RealNames, MissingField.Ignore)

 

This will replace the names of any column whose internal name matches and ignore the others.

 

Dale

Anonymous
Not applicable

I've now encapsulated this into a function.  See the comments for usage example:

 

fnRealSpColumnNames

(sharePointSite, listName)=>
let
    // After connecting to a SharePoint list, pass in the SharePoint site name and list name to this function inside a Table.RenameColumns call.
    // The function returns the InternalName and DisplayName of each of the list column names.
    // For example:
    //  = Table.RenameColumns(#"previous step name", fnRealSpColumnNames("https://xxx.sharepoint.com/teams/yoursite/", "Your ListName"), MissingField.Ignore)
    Source = OData.Feed(sharePointSite & "/_api/web/lists/GetByTitle('" & listName & "')/Fields?$select=Title,InternalName", null, [Implementation="2.0"]),
    recs = Table.ToRecords(Source),
    lsts = List.Transform(recs, each Record.ToList(_))
in
    lsts

 

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.