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

[Anonymous]

@kasiaw29 @v-shex-msft 

I was trying the same but then I landed on to a different issue now.

created function as 

fngettoken which gets using anonymus authentication..

() =>
let
   TenantGUID = ParamTenantID,
   ApplicationID= #"ParamClientID",
   ApplicationSecret= ParamClientSecret,

   OAuthUrl = Text.Combine({"https://login.microsoftonline.com/",TenantGUID,"/oauth2/token"}),
   ClientId = Text.Combine({"client_id",ApplicationID}, "="),
   ClientSecret = Text.Combine({"client_secret", Uri.EscapeDataString(ApplicationSecret)}, "="),
   GrantType = Text.Combine({"grant_type", "client_credentials"}, "="),
   Body = Text.Combine({"resource=https://analysis.windows.net/powerbi/api", ClientId, ClientSecret, GrantType}, "&"),

   AuthResponse= Json.Document(Web.Contents(
       OAuthUrl,
       [
           Content=Text.ToBinary(Body)
       ]
   )),

   AccessToken= AuthResponse[access_token],
   Bearer = Text.Combine({"Bearer", AccessToken}, " ")

in
   Bearer

created function to get users of reports

fngetreportusers

(group as text) =>
    let
        Source = Web.Contents(
                        "https://api.powerbi.com",
                        [
                            RelativePath = "v1.0/myorg/admin/reports/" & group & "/users",
                            //Query = [includeOptional="customFields,nullCustomFields,postalAddresses"], 
                            Headers=[Authorization=fnGetToken()]
                        ]),
        json_doc = Json.Document(Source) ,
    #"Converted to Table" = Table.FromRecords({json_doc}),
    #"Expanded value" = Table.ExpandListColumn(#"Converted to Table", "value"),
    #"Expanded value1" = Table.ExpandRecordColumn(#"Expanded value", "value", {"reportUserAccessRight", "emailAddress", "displayName", "identifier", "graphId", "principalType", "userType"}, {"value.reportUserAccessRight", "value.emailAddress", "value.displayName", "value.identifier", "value.graphId", "value.principalType", "value.userType"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded value1",{{"@odata.context", type text}, {"value.reportUserAccessRight", type text}, {"value.emailAddress", type text}, {"value.displayName", type text}, {"value.identifier", type text}, {"value.graphId", type text}, {"value.principalType", type text}, {"value.userType", type text}})
in
    #"Changed Type"

and then i was able to fetch all reports data 

let
    Source = Json.Document(Web.Contents("https://api.powerbi.com/v1.0/myorg/admin/reports", [Headers=[Authorization=fnGetToken()]])),
    #"Converted to Table" = Table.FromRecords({Source}),
    #"Expanded value" = Table.ExpandListColumn(#"Converted to Table", "value"),
    #"Expanded value1" = Table.ExpandRecordColumn(#"Expanded value", "value", {"id", "reportType", "name", "webUrl", "embedUrl", "datasetId", "appId", "createdDateTime", "modifiedDateTime", "description", "modifiedBy", "createdBy", "endorsementDetails", "sensitivityLabel", "users", "subscriptions", "workspaceId"}, {"value.id", "value.reportType", "value.name", "value.webUrl", "value.embedUrl", "value.datasetId", "value.appId", "value.createdDateTime", "value.modifiedDateTime", "value.description", "value.modifiedBy", "value.createdBy", "value.endorsementDetails", "value.sensitivityLabel", "value.users", "value.subscriptions", "value.workspaceId"}),
    #"Expanded value.endorsementDetails" = Table.ExpandRecordColumn(#"Expanded value1", "value.endorsementDetails", {"endorsement"}, {"value.endorsementDetails.endorsement"}),
    #"Expanded value.sensitivityLabel" = Table.ExpandRecordColumn(#"Expanded value.endorsementDetails", "value.sensitivityLabel", {"labelId"}, {"value.sensitivityLabel.labelId"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded value.sensitivityLabel",{{"@odata.context", type text}, {"@odata.count", Int64.Type}, {"value.id", type text}, {"value.reportType", type text}, {"value.name", type text}, {"value.webUrl", type text}, {"value.embedUrl", type text}, {"value.datasetId", type text}, {"value.appId", type text}, {"value.createdDateTime", type datetime}, {"value.modifiedDateTime", type datetime}, {"value.description", type any}, {"value.modifiedBy", type text}, {"value.createdBy", type text}, {"value.endorsementDetails.endorsement", type any}, {"value.sensitivityLabel.labelId", type any}, {"value.users", type any}, {"value.subscriptions", type any}, {"value.workspaceId", type text}}),
    #"Expanded value.users" = Table.ExpandListColumn(#"Changed Type", "value.users"),
    #"Expanded value.subscriptions" = Table.ExpandListColumn(#"Expanded value.users", "value.subscriptions"),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Expanded value.subscriptions", "value.modifiedBy", Splitter.SplitTextByDelimiter("@", QuoteStyle.Csv), {"value.modifiedBy.1", "value.modifiedBy.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"value.modifiedBy.1", type text}, {"value.modifiedBy.2", type text}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"value.modifiedBy.2"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"value.modifiedBy.1", "P/J#"}}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Renamed Columns",{{"value.createdDateTime", type date}}),
    #"Renamed Columns1" = Table.RenameColumns(#"Changed Type2",{{"value.id", "Report ID"}, {"value.reportType", "Report Type"}, {"value.name", "Report Name"}, {"value.webUrl", "Report Web URL"}, {"value.embedUrl", "Report Embed URL"}, {"value.datasetId", "Dataset ID"}, {"value.appId", "App ID"}, {"value.createdDateTime", "Report Created Date"}, {"value.modifiedDateTime", "Report Modified Date"}, {"value.description", "Report Description"}, {"value.createdBy", "Report Created By"}, {"value.endorsementDetails.endorsement", "Report Endorsment"}, {"value.sensitivityLabel.labelId", "Sensitivity Label"}, {"value.users", "Report Users"}, {"value.subscriptions", "Report Subscriptions"}, {"value.workspaceId", "Report Workspace ID"}}),
    #"Removed Columns1" = Table.RemoveColumns(#"Renamed Columns1",{"@odata.context", "Report Web URL", "Report Embed URL", "@odata.count"})
in
    #"Removed Columns1"

i was able to get all users for the reports which has given access to

Report With Users

let
    Source = Reports,
    #"Removed Other Columns" = Table.SelectColumns(Source,{"Report ID"}),
    #"Kept First Rows" = Table.FirstN(#"Removed Other Columns",#"Top Rows"),
    #"Invoked Custom Function" = Table.AddColumn(#"Kept First Rows", "fnGetReportUsers", each fnReportUsers([Report ID])),
    #"Expanded fnGetReportUsers1" = Table.ExpandTableColumn(#"Invoked Custom Function", "fnGetReportUsers", {"@odata.context", "value.reportUserAccessRight", "value.emailAddress", "value.displayName", "value.identifier", "value.graphId", "value.principalType", "value.userType"}, {"fnGetReportUsers.@odata.context", "fnGetReportUsers.value.reportUserAccessRight", "fnGetReportUsers.value.emailAddress", "fnGetReportUsers.value.displayName", "fnGetReportUsers.value.identifier", "fnGetReportUsers.value.graphId", "fnGetReportUsers.value.principalType", "fnGetReportUsers.value.userType"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded fnGetReportUsers1",{"fnGetReportUsers.@odata.context"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"fnGetReportUsers.value.reportUserAccessRight", "User Access Right"}, {"fnGetReportUsers.value.emailAddress", "Email Address"}}),
    #"Duplicated Column" = Table.DuplicateColumn(#"Renamed Columns", "Email Address", "Email Address - Copy"),
    #"Renamed Columns1" = Table.RenameColumns(#"Duplicated Column",{{"fnGetReportUsers.value.displayName", "Display Name"}}),
    #"Removed Columns1" = Table.RemoveColumns(#"Renamed Columns1",{"fnGetReportUsers.value.identifier"}),
    #"Renamed Columns2" = Table.RenameColumns(#"Removed Columns1",{{"fnGetReportUsers.value.graphId", "Graph ID"}, {"fnGetReportUsers.value.principalType", "Principal Type"}, {"fnGetReportUsers.value.userType", "User Type"}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Renamed Columns2", "Email Address - Copy", Splitter.SplitTextByDelimiter("@", QuoteStyle.Csv), {"Email Address - Copy.1", "Email Address - Copy.2"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Email Address - Copy.1", type text}, {"Email Address - Copy.2", type text}}),
    #"Removed Columns2" = Table.RemoveColumns(#"Changed Type",{"Email Address - Copy.2"}),
    #"Renamed Columns3" = Table.RenameColumns(#"Removed Columns2",{{"Email Address - Copy.1", "P/J #"}}),
    #"Replaced Value" = Table.ReplaceValue(#"Renamed Columns3",null,"Distribution List",Replacer.ReplaceValue,{"User Type"}),
    #"Replaced Errors" = Table.ReplaceErrorValues(#"Replaced Value", {{"P/J #", null}}),
    #"Replaced Errors1" = Table.ReplaceErrorValues(#"Replaced Errors", {{"User Type", null}}),
    #"Replaced Errors2" = Table.ReplaceErrorValues(#"Replaced Errors1", {{"Principal Type", null}}),
    #"Replaced Errors3" = Table.ReplaceErrorValues(#"Replaced Errors2", {{"Graph ID", null}}),
    #"Replaced Errors4" = Table.ReplaceErrorValues(#"Replaced Errors3", {{"Display Name", null}}),
    #"Replaced Errors5" = Table.ReplaceErrorValues(#"Replaced Errors4", {{"Email Address", null}}),
    #"Replaced Errors6" = Table.ReplaceErrorValues(#"Replaced Errors5", {{"User Access Right", null}}),
    #"Replaced Errors7" = Table.ReplaceErrorValues(#"Replaced Errors6", {{"Report ID", null}})
in
    #"Replaced Errors7"

But then when i publish this to power bi service..and trying to schedule then i am getting a error that it has dynamic URL.

I am not able to understand and figure it out though i have created relativepath for the API URL.

Hope the above piece of code helps you to resolve your issue and

Request you to help me on how do i cant resolve this dyanamic URL issue here.

Thanks,

Mohan V.