Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Be one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now

Reply
Anonymous
Not applicable

How to retrieve "correct" column names from SharePoint lists

‎08-08-2018 01:36 PM

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

Labels:
Message 1 of 27
63,195 Views
1 ACCEPTED SOLUTION
Anonymous
Not applicable
In response to Anonymous

‎05-14-2020 01:28 PM

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

Message 10 of 27
59,673 Views
26 REPLIES 26
Anonymous
Not applicable
In response to Anonymous

‎08-01-2020 04:16 PM

@Anonymous  I've been struggling with this for a while and your solution looks like a good one. Would it be possible for you to break down the steps required to apply this solution for someone who has very little experience with creating functions? 

 

Many thanks in advance if you can!

Message 12 of 27
51,800 Views
j_d4vid
Regular Visitor
In response to Anonymous

‎07-22-2020 07:35 PM

Hello,

 

It is possbile to explain how to execute this function?


Thanks and br, 

Message 11 of 27
51,843 Views
0
v-jiascu-msft
Microsoft Employee
Microsoft Employee

‎08-09-2018 11:29 PM

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.
Message 2 of 27
52,110 Views
v-jiascu-msft
Microsoft Employee
Microsoft Employee
In response to v-jiascu-msft

‎08-10-2018 03:13 AM

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.
Message 3 of 27
45,768 Views
0
Anonymous
Not applicable
In response to v-jiascu-msft

‎08-10-2018 11:21 AM

Thanks very much Dale!

 

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

 

Dale

Message 4 of 27
34,903 Views
v-jiascu-msft
Microsoft Employee
Microsoft Employee
In response to Anonymous

‎08-13-2018 05:44 PM

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.
Message 5 of 27
33,664 Views
0

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.

ArunFabCon

Microsoft Fabric Community Conference 2025

Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.

December 2024

A Year in Review - December 2024

Find out what content was popular in the Fabric community during 2024.

View All