cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Post Patron
Post Patron

Building SharePoint List Item Id in Power Query

Hi,

 

 

 

I'm trying to get to the list item ID for each list item in sharepoint to build a URL in power query. Each list item ends with a unique string. Some examples are

 

 

 

ID=2&e=vM6J6,

ID=5&e=xaq3R

leharkapil_0-1597134794599.png

 

 

Which column in Power Query should I expand to get this ID?

 

Note: I'll appreciate if instead of pasting links I can get an explanation of how to do it.

I have already seen all the possible links on the internet related to this.

 
3 REPLIES 3
Super User III
Super User III

Hi @leharkapil ,

 

When you first connect to a SharePoint List, there is a field usually 2nd from the left simply called [Id] and it is an integer. It would be in the ID= part of the URL below.

 

 

 

https://TENANTNAME.sharepoint.com/sites/TheSiteName/Lists/The%20List%20Name/DispForm.aspx?ID=11&e=PjYBb3

 

 

 

I do not know how to retrieve the "e=PjYBb3" part of the URL, however I believe that is just opening it up in an embedded format in the list itself, so when I just access the "https://TENANTNAME.sharepoint.com/sites/TheSiteName/Lists/The%20List%20Name/DispForm.aspx?ID=11" part of the URL, it opens that list item in a separate page. That last part may be dynamically generated. I am still investigating, but that is what I've found so far.

 

Is that what you are looking for?

EDIT: I have to believe that if that data actually exists in the SharePoint list records, it is in the [FirstUniqueAncestorSecurableObject] field, but that field has dozens of tables, records, lists, and subtables, sublists, and subrecords. And I cannot find that string at all. 

You may want to consult with a SharePoint expert on this forum as this is really getting into the dark depths of SharePoint vs a Power Query/Power BI question. Once you know the field and sub-item (table/record/list) getting that into PQ is a snap. Again, if it even exists in the SP list record.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting
Super User IV
Super User IV

@amitchandak I think you are just trying to reply to every question on the community even if you don't happen to know the solution.

I specifically mentioned that I don't want links, I have searched all the internet and only then I came here to know if someone has experience around the same.

 

I would appreciate it if you can remove your answers with links and let someone who has experience around the subject answer it.

Helpful resources

Announcements
secondImage

Happy New Year from Power BI

This is a must watch for a message from Power BI!

December Update

Check it Out!

Click here to read more about the December 2020 Updates!

Community Blog

Check it Out!

Click here to read the latest blog and learn more about contributing to the Power BI blog!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors