Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.
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.
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?
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.
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
Get an authentication access token - Power BI | Microsoft Docs
Regards,
Xiaoxin Sheng
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"
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
Check out the November 2023 Power BI update to learn about new features.
Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.
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!