Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

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

 

 

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.

 
Need Help

3 Replies

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      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.

  • edhans's avatar
    edhans
    Community Champion
    6 years ago

    Hi Anonymous ,

     

    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.