cancel
Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

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

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
PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Dashboard in a day with date

Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.