Forum Discussion

kasiaw29's avatar
kasiaw29
Resolver II
5 years ago

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:

 

Anyone experienced similar issue? 

 

Need Help

6 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    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 | Microsoft Docs.

    Get an authentication access token - Power BI | Microsoft Docs

    Regards,
    Xiaoxin Sheng

    • kasiaw29's avatar
      kasiaw29
      Resolver II
      4 years ago

      Hey Anonymous 

       

      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"
      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        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

    • kasiaw29's avatar
      kasiaw29
      Resolver II
      4 years ago

      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.