Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Syndicate_Admin
Administrator
Administrator

Rest Power BI API - Get information from users, data sources, tables and fields, and so on.

Hi, I'm trying to query the Power BI REST API information to make a report with it. The intention is to be able to consult each workspace: reports, datasets, users and dashboards.

I have managed to do this with "Groups GetGroupsAsAdmin" but in addition to that information I would like to be able to consult:

  • reports: users and their access permissions.
  • datasets: users, user access permissions, scheduled refresh time, refresh time, and above all: dataset sources and the tables contained in the dataset with its fields.

I have not managed to get that information out in bulk, since the user field of that function and others like "GetDatasetsAsAdmin" or "GetReportsAsAdmin" is empty and I do not know how to get it without having to consult one by one with "GetDatasetUsersAsAdmin" or "GetReportsUsersAsAdmin".

If I use these functions, it asks me for a dataset or report ID and I think it is not enough for me to extract it massively to exploit it in power BI. I have seen that there are other options such as "WorkspaceInfo PostWorkspaceInfo" but it also asks me for ID ...

With the other data I want to extract it also happens to me. Is there a way to do what I want? In the end, I want to have the data in power BI to make a report with it.

1 ACCEPTED SOLUTION
v-kkf-msft
Community Support
Community Support

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”.

 

vkkfmsft_0-1644991283054.png

 

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.

 

vkkfmsft_1-1644991283209.png

 

d) Launch Power BI Desktop and find the Power BI API (Beta) connector.

 

vkkfmsft_2-1644991283158.png

 

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.

 

View solution in original post

10 REPLIES 10
taumirza
Helper IV
Helper IV

Hi @v-kkf-msft ,

 

Any update to this connector?

taumirza
Helper IV
Helper IV

@v-kkf-msft 

Is there a way to add "Date Created" for the artifacts like Reports, Dashboards, Workspaces, Datasets and Dataflows? (admin) tables

 

Questions from my previous post:

1. Can we set dataset schedule refresh if we publish the report created to the service? if yes, can you please help me how ?

2. Can we only scan with Tenant Admin account (Deligated Admin)? (is there a way to use Service Principal (client ID and Secret)

3. Tables inside Experimental folder taking hours and hours to load. "Dataflow Users" table gives "Access Forbidden" error.

 

Thanks in advance.

v-kkf-msft
Community Support
Community Support

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”.

 

vkkfmsft_0-1644991283054.png

 

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.

 

vkkfmsft_1-1644991283209.png

 

d) Launch Power BI Desktop and find the Power BI API (Beta) connector.

 

vkkfmsft_2-1644991283158.png

 

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 There,

 

Thank you for sharing this connector.

 

Can we set dataset schedule refresh if we publish the report created to the service? if yes, can you please help me how ?

 

Can we only scan with Tenant Admin account ? (is there a way to use Service Principal (client ID and Secret)

  

Tables inside Experimental folder taking hours and hours to load. "Dataflow Users" table gives "Access Forbidden" error.

 

Thanks again. This is awsome

 

v-kkf-msft
Community Support
Community Support

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

vkkfmsft_0-1643957686176.png

 

 

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,

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???

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.

vkkfmsft_0-1644221557720.png
2. Create a new application.

vkkfmsft_1-1644221660198.png
3. Create the application secret and copy the value.

 

vkkfmsft_2-1644221749585.png


4. Authorize. In API permissions -> Add a permission -> Power BI Service -> Delegated permissions -> select all options -> Grant admin consent for Contoso

vkkfmsft_4-1644222080964.png  vkkfmsft_5-1644222124877.png

vkkfmsft_6-1644222188107.png  vkkfmsft_7-1644222254401.png

 

5. When the application creation is done, copy the application ID, tenant ID in the overview page.

vkkfmsft_3-1644221832926.png

 

For privacy settings, you need to set it in the permissions of the data source.

 

vkkfmsft_8-1644222622623.png

 

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:

gerardcanals_0-1646901059069.png

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?

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.