Import the User ID that's visible in the Admin Portal URL for each user, into Power BI?

[wad11656]

I need to import the User ID visible in the Admin Portal URL when a user is selected (along with the name associated with that ID):

I need this so I know which Microsoft Planner tasks individuals are assigned to.

I'm able to successfully get a Planner's Tasks using the "OData feed" datasource with URI's formatted like this:
https://graph.microsoft.com/v1.0/planner/plans/ABC123abc123ABC123/tasks

You can expand the "assignments" column to get a bunch of columns with the User ID's of the assignees:

This...

...expands into this:

But I need to know these people's names!

How can I get a table that links the User ID with the user's name?

I can successfully capture these User ID's + Users' names using Graph API's "https://graph.microsoft.com/v1.0/users":

...But when I try to access that endpoint as an OData source in Power BI, I get an "Access is forbidden" error:

So, I tried using the "Active Directory" datasource instead. I successfully pulled in a "user" table from my domain, but I don't see any column with the User ID. (I expanded all the columns):

Does anyone know if it's possible to get the User ID's without Graph API? (Which apparently I'm "forbidden" to use for getting user lists?)

[wad11656 · Accepted solution]

So I finally succeeded in querying the https://graph.microsoft.com/v1.0/users endpoint in Power BI, allowing me to get the User Names for the User IDs. Thanks to this Medium article.

Basically you:

1) Register an app at https://portal.azure.com/ 

1a) Add User.Read.All "Application" (not "Delegated") permissions in the API Permissions tab

1b) Create a Client Secret in the Certificates & secrets tab (Tip: use this article and my comment at the bottom of it to create a secret that's good for 99 years.)

2) In Power BI, in the Power Query Editor window, add 3 new parameters:

- "Azure AD Tenant ID" (Copy and paste the tenant ID from your Azure App's Overview tab.)

- "Azure Application Client ID" (Copy and paste the client ID from your Azure App's Overview tab.)

- "Azure Application Client Secret" (Copy and paste the secret you generated in step 1b)

3) In the Power Query Editor window, click New Source > Blank Query.

4) Right-click the new query and click Advanced Editor. Copy-paste the code from the Medium article (or my simplified version below).

let 
    resource="https://graph.microsoft.com",
    tokenResponse = Json.Document(Web.Contents("https://login.windows.net/",
    [
        RelativePath = #"Azure AD Tenant ID" & "/oauth2/token",
        Content = Text.ToBinary(Uri.BuildQueryString(
            [
                client_id = #"Azure Application Client ID",
                resource = resource,
                grant_type = "client_credentials",
                client_secret = #"Azure Application Client Secret"
            ]
        )),
        Headers = [Accept = "application/json"], ManualStatusHandling = {400}
    ])),
    access_token = tokenResponse[access_token],
    Source = OData.Feed("https://graph.microsoft.com/v1.0/users?$top=120&$select=id, displayName, userPrincipalName", [ Authorization = "Bearer " & access_token ], [ ExcludedFromCacheKey = {"Authorization"}, ODataVersion = 4, Implementation = "2.0" ])
in
    Source

5) Click Edit Credentials... when prompted and choose "Anonymous"

6)  Change both Privacy levels to "Organizational" when prompted.

7) For some reason, I got an error at this point telling me I need to use Anonymous connection, even though I JUST set it to be an anonymous connection. So, I had to click Data source settings, click https://graph.microsoft.com/v1.0/users, Edit Permissions... > Edit and select Anonymous again and save. Then I had to select a previous step in the query on the right-hand column (where the steps for the query are displayed), then click the final step again. THEN it finally loaded the data.