cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
kasiaw29
Resolver II
Resolver II

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

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:

kasiaw29_0-1619449922871.png

 

Anyone experienced similar issue? 

 

5 REPLIES 5
Srikanth7Gunnam
Helper I
Helper I

Hello,

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

 

Thanks

@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
Community Support
Community Support

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

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. 

 

kasiaw29_0-1640267100969.png

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"

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Helpful resources

Announcements
August 1 episode 9_no_dates 768x460.jpg

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power BI Dev Camp Session 24 without aka link and time 768x460.jpg

Ted's Dev Camp - July 28, 2022

Watch Session 24 of Ted's Dev Camp along with past sessions!

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.