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/ utilit...
Need Help
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

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    kasiaw29 Anonymous 

    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"}, {"[email protected]", "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",{"[email protected]"}),
    #"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.