Forum Discussion
Rest Power BI API - Get information from users, data sources, tables and fields, and so on.
Hi Syndicate_Admin ,
I find the Power BI API (Beta) custom connector that you can use to call the Power BI Rest API without writing code.
a) Get the .mez file from this link: https://github.com/migueesc123/PowerBIRESTAPI/raw/master/Power%20BI%20API.mez
b) Place the connector. mez file in the [Documents]\Power BI Desktop\Custom Connectors folder. If this folder does not exist, create it.
In my example, the full path is “C:\Users\Administrator\Documents\Power BI Desktop\Custom Connectors folder”.
c) In Power BI Desktop, select File > Options and settings > Options > Security. Under Data Extensions, select (Not Recommended) Allow any extension to load without validation or warning. Click OK, and restart Power BI Desktop.
d) Launch Power BI Desktop and find the Power BI API (Beta) connector.
If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi Syndicate_Admin ,
I wrote some code to get the metadata for the workspace. The logic is:
1. Obtain dynamic access token.
() =>
let
body = "grant_type=password&&resource=https://analysis.windows.net/powerbi/api&&response_type=code&&client_id=xxxxxxxxx&&client_secret=xxxxxxxxx&&username=xxxxxxxxx&&password=xxxxxxx",
Data=Json.Document(Web.Contents("https://login.microsoftonline.com/common/oauth2/token/",
[Headers=[#"Content-Type"="application/x-www-form-urlencoded"], Content=Text.ToBinary(body)])),
access_token = Data[access_token]
in
access_token
2. Call Groups GetGroupsAsAdmin api to return the list of workspaces and then merge those IDs.
3. Call WorkspaceInfo PostWorkspaceInfo api and reference the workspace IDs from the previous step and return the scan ID.
() =>
let
Source = Json.Document(Web.Contents("https://api.powerbi.com/v1.0/myorg/admin/groups?$top=3", [Headers=[Authorization="Bearer "& GetAccessToken() ]])),
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", "description", "type", "state", "name"}, {"id", "isReadOnly", "isOnDedicatedCapacity", "capacityMigrationStatus", "description", "type", "state", "name"}),
GroupID = """" & Text.Combine(#"Expanded Column1"[id],""",""") & """",
body = "{ ""workspaces"": [ " & GroupID & " ]}",
Data=Json.Document(Web.Contents("https://api.powerbi.com/v1.0/myorg/admin/workspaces/getInfo?datasourceDetails=True&datasetSchema=True&datasetExpressions=True&getArtifactUsers=True",
[Headers=[Authorization="Bearer "& GetAccessToken(),#"Content-Type"="application/json"], Content=Text.ToBinary(body)])),
scanID = Data[id]
in
scanID
4. Call WorkspaceInfo GetScanStatus and reference the scan ID from the previous step. if the status value of the response is "Succeeded", proceed to the next step.
() =>
let
Data=Json.Document(Web.Contents("https://api.powerbi.com/v1.0/myorg/admin/workspaces/scanStatus/" & PostWorkspaceInfo(),
[Headers=[Authorization="Bearer "& GetAccessToken()]])),
Scan_ID = if Data[status] = "Succeeded" then Data[id] else "Scan failed"
in
Scan_ID
4. Call WorkspaceInfo GetScanResult to return the metadata of the workspaces.
let
Source = Json.Document(Web.Contents("https://api.powerbi.com/v1.0/myorg/admin/workspaces/scanResult/" & GetScanStatus(), [Headers=[Authorization="Bearer "& GetAccessToken() ]]))
in
Source
If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hello! Thank you very much Winnz! A super complete answer!
I'm trying to do it, the steps of the rest API are very clear to me, but I'm having trouble getting the dynamic access token.
I understand that I have that, from the code you passed me, I have to modify only the fields:
- client_id=xxxxxxxxx
- client_secret=xxxxxxxxx
- username=xxxxxxxxx
- password=xxxxxxx
I didn't know where to get the client_id and followed the information in this post to get it:
https://docs.microsoft.com/en-us/power-bi/developer/embedded/register-app?tabs=customers%2CAzure
I don't know if what I've done is correto or useful to me. Once those steps are done, I get a application_id and a application_secret, but I don't know if it's the same as client_id and client_secret.
Do I understand that the usuername and password are those of my power BI account?
After filling in those fields, I find that I have not been able to authenticate with the credentials provided.
Is it possible that the information I have put is erroneous? If the client_id is not the aplication_id, how can I get it?
If all of the above is correct, is there any more field that you should modify?
On the other hand, I have seen that in the configuration of tenants there are some points related to the rest API of power BI, I have enabled the mayodría, but I do not know if I also have to activate the one of:
Grant service principals permissions to use Power BI APIs
Also, when I try to invoke one of the functions, I get the following error:
you are accessing data sources with levels of privacy that cannot be used together. Regenerate this combination of data.
Hi Syndicate_Admin ,
Please follow the steps to create an application.
1. Select the App registrations.
2. Create a new application.
3. Create the application secret and copy the value.
4. Authorize. In API permissions -> Add a permission -> Power BI Service -> Delegated permissions -> select all options -> Grant admin consent for Contoso5. When the application creation is done, copy the application ID, tenant ID in the overview page.
For privacy settings, you need to set it in the permissions of the data source.
If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.Hello @Syndicate_Admin ,
The application had already created it and now when modifying the permissions, it already allows me to obtain the token, but it still gives me problems:
Formula.Firewall: Query 'Invoked Function' ('Source' step) is gaining access to data sources with privacy levels that cannot be used together. Regenerate this combination of data.
I think the problem is in those functions in which the origin is constructed with the result of a previous function, for example:
https://api.powerbi.com/v1.0/myorg/admin/workspaces/scanStatus/" & PostWorkspaceInfo()
This source does not appear to me in:
So I can't change the level of privacy.
Is there a way to define it in those cases?
On the other hand, I am getting errors in the postWorkspaceInfo step I have realized that if the number of work areas exceeds 100 the function does not work. I need to extract the information from all the work areas of my organization, is there a way to do it?
Hi,
I tried your code and it`s alright on Power Query, when I click on Close & Apply i get Error 400 - Bad Request on ScanStatus geting scan failed message. Could you help me??
v-kkf-msft can you help me???
