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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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

23 REPLIES 23

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

Thanks very much Dale!

 

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

 

Dale

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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