Link User Information List (specific person ID) to user from SharePoint List

[dirkkoch]

Hi, 

I have a Sharepoint List with one column being information type person with multiple choice. 

When I connect the Sharepoint List to Power BI and extract the values from the specific column (shown as List, see Screenshot), I receive one or more ID's. Whenever I look up the specific ID in the User Information List, I see that it is linked to a SharepointGroup Id, but not the specific person Id which I wanted. The specific user(s) are listed in the Sharepoint list by name nonetheless.

How can I see the user specific Id in Power BI and link that to the User information list?

[v-henryk-mstf]

Hi @dirkkoch ,

To see the user specific ID in Power BI and link it to the User Information List, you can follow these steps:

In Power BI, click on the "Get Data" button and select "SharePoint Online List" from the list of data sources.
Connect to your SharePoint site and select the specific list that contains the information you want to retrieve.
In the Navigator window, expand the list and select the column that contains the person information with multiple choice.
Click on the "Transform Data" button to open the Power Query editor.
In the Power Query editor, click on the column that contains the person information with multiple choice and then click on the "Expand" button in the ribbon.
In the "Expand Column" window, select the columns you want to expand and then click on the "OK" button. This will create a new table in the Power Query editor that contains the expanded person information.
To link this table to the User Information List, you can create a new query by clicking on the "New Query" button in the ribbon and then selecting "Blank Query" from the list of options.
In the blank query, click on the "Home" tab in the ribbon and then click on the "Advanced Editor" button.
In the Advanced Editor window, paste the following M code:

let
Source = SharePoint.Tables("<URL of User Information List>"),
#"Filtered Rows" = Table.SelectRows(Source, each [Title] = <Name of user>)
in
#"Filtered Rows"

Replace <URL of User Information List> with the URL of the User Information List, and <Name of user> with the name of the user you want to retrieve the ID for.
Click on the "Close & Apply" button to apply the changes and retrieve the user's ID from the User Information List.
You can then use this query to retrieve the user's ID and link it to the other data in your Power BI report.

If the problem is still not resolved, please provide detailed error information and let me know immediately. Looking forward to your reply.

Best Regards,
Henry

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[dirkkoch]

@v-henryk-mstf  thank you for the response.

The issue is that whenever I expand the column and choose the option to extract its values in Power BI I only receive the ID's shown in Screenshot 2 below. These ID's are are linked to a SharepointGroup ID according to the User Information List and not to the specific persons who are named in the Sharepoint List for this column??? 
So right now I do not have a proper link on person ID basis between the Sharepoint Online List in PBI and the User Information List. The ID is only available for the SharepointGroup ID although there are two specific persons/names in the original Sharepoint list???