PowerBI Anonymous authentication - couldn't authenticate credentials provided REST API

[kasiaw29]

Hey community,

I followed a simple 4 step process to set up PowerBI queries in Power Query.

Using REST APIs (https://docs.microsoft.com/en-us/rest/api/power-bi/admin)  to get data on uptake/ utility of PowerBI as a whole in a business. 

1.  In azure create app & create a secret (and copy it!)
2. In azure, create an AAD security group and add this app to it
3. In Power BI tenant settings, enable read only admin API access and add the group you just made in #3
4. In Power BI desktop construct your body string like this

As instructed I did that. 

let
    Source = Json.Document(Web.Contents("https://api.powerbi.com/v1.0/myorg/",
    [
        RelativePath = "admin/groups?$top=500&$expand=datasets,reports,dashboards,users",
        Headers=[Authorization="Bearer " & #"GET Access Token"()]
    ])),
    value = Source[value],
    #"Converted to Table" = Table.FromList(value, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"id", "isReadOnly", "isOnDedicatedCapacity", "capacityMigrationStatus", "type", "state", "name", "datasets", "reports", "dashboards", "users", "description"}, {"id", "isReadOnly", "isOnDedicatedCapacity", "capacityMigrationStatus", "type", "state", "name", "datasets", "reports", "dashboards", "users", "description"}),
    #"Filtered Rows" = Table.SelectRows(#"Expanded Column1", each ([type] = "Workspace") and ([state] = "Active")),
    #"Renamed Columns" = Table.RenameColumns(#"Filtered Rows",{{"name", "workspacename"}})
in
    #"Renamed Columns"

Created a function GET Access Token:

() =>
let
    body = "client_id= my client id 
            &scope=https://analysis.windows.net/powerbi/api/.default
            &client_secret= my secret id
            &grant_type=client_credentials",
    Data= Json.Document(Web.Contents("https://login.microsoftonline.com/ my tenant id /oauth2/v2.0/token/",
    [Headers=[#"Content-Type"="application/x-www-form-urlencoded"],
    Content=Text.ToBinary(body)])),
    access_token = Data[access_token]
in
    access_token

I'm getting following and can't progress it further to do my reporting on it:

Anyone experienced similar issue? 

[Srikanth7Gunnam]

Hello,

Could you please tell me how did you resolve this issue?

Thanks

[kasiaw29]

@Srikanth7Gunnam it's not really resolved yet. I need to switch how I'm passing my authentication...When I try it everything just load for forever and never loads. I need to give this another go, for now, this is parked until I have more time to work on it. 

[v-shex-msft]

Hi @kasiaw29,

I think this should more relate to your access token function, you can take a look at the following code and links if they helped:

let
    body =
        "grant_type=client_credentials&
            client_id='client_id'&
            client_secret='client_secret'&.
            client_credentials='credentials'
            resource='https://analysis.windows.net/powerbi/api'",
    Response =
        Web.Contents(
            "https://login.microsoftonline.com/common/",
            [
                Headers = [
                    #"Content-Type" = "application/x-www-form-urlencoded"
                ],
                Content = Text.ToBinary(body)
            ]
        ),
    access_token = Response[access_token]
in
    access_token

Microsoft identity platform and OAuth 2.0 authorization code flow - Microsoft identity platform | Mi....

Get an authentication access token - Power BI | Microsoft Docs

Regards,
Xiaoxin Sheng

[kasiaw29]

Hey @v-shex-msft 

My access function works just fine as I can invoke it and refresh it and it returns me a new token each time. 

It's when I'm passing it into workspace query that it is having a hard time authenticating. 

This is all I get and cannot solve it 😞

Code in workspace: 

let
    Source = Json.Document(Web.Contents("https://api.powerbi.com/v1.0/myorg/admin/groups?%24top=100", 
        [Headers=[Authorization="Bearer " &#"GET Access Token"()]])),

    #"Converted to Table" = Record.ToTable(Source),
    Value = #"Converted to Table"{2}[Value],
    #"Converted to Table1" = Table.FromList(Value, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table1", "Column1", {"id", "isReadOnly", "isOnDedicatedCapacity", "capacityMigrationStatus", "type", "state", "name", "datasets", "dataflows", "reports", "dashboards", "users", "capacityId"}, {"id", "isReadOnly", "isOnDedicatedCapacity", "capacityMigrationStatus", "type", "state", "name", "datasets", "dataflows", "reports", "dashboards", "users", "capacityId"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Expanded Column1",{"id", "isReadOnly", "isOnDedicatedCapacity", "capacityMigrationStatus", "type", "state", "name", "capacityId", "datasets", "dataflows", "reports", "dashboards", "users"}),
    #"Renamed Columns" = Table.RenameColumns(#"Reordered Columns",{{"id", "workspaceId"}})
in
    #"Renamed Columns"

[v-shex-msft]

HI @kasiaw29,

I think you can try to move the get access token part into the main query table, then it will get the new valid token for the next operations when the query table is processed.
For the data source setting on the power bi service side, you can switch it to 'anonymous' mode because the token and corresponding credentials operations have been integrated into the query table.

let
    //get access token
    body =
        "client_id= my client id 
            &scope=https://analysis.windows.net/powerbi/api/.default
            &client_secret= my secret id
            &grant_type=client_credentials",
    Data =
        Json.Document(
            Web.Contents(
                "https://login.microsoftonline.com/ my tenant id /oauth2/v2.0/token/",
                [
                    Headers = [
                        #"Content-Type" = "application/x-www-form-urlencoded"
                    ],
                    Content = Text.ToBinary(body)
                ]
            )
        ),
    access_token = Data[access_token],
    //other operaitons
    Source =
        Json.Document(
            Web.Contents(
                "https://api.powerbi.com/v1.0/myorg/admin/groups?%24top=100",
                [Headers = [
                    Authorization = "Bearer " & access_token
                ]]
            )
        )
in
    Source

Regards,

Xiaoxin Sheng