Power BI administration made easy with Power BI REST API custom connector

Today I read the (as always) great article by Matthew Roche “Governing Power BI just got a little easier” and couldn’t find a description on how to get to this promising window with all the admin goodness. So here it comes how to build your Power BI REST API custom connector then 😉 :

Create a custom connector for the Power BI REST API

Miguel Escobar has done a fantastic job to make it super-easy for you here.

Edit 30th September 2019: This repo has just been updated and includes a version with API secret. So if you’ve downloaded that content before and got an authorization error, please get the new files.

Get data from your connector

After you’ve stored the .mez-file in the correct folder (C:\Users\<YourUserNameGoesHere>\Documents\Power BI Desktop\Custom Connectors) and open Power BI Desktop again, you will be greeted with this warning message:

Warning for custom connectors

This tells you that bad things could happen, if you import connectors from untrustworthy sources. You can read more about it here. It’s up to you to decide if you take that risk. But if you click OK, you can go on and change the security settings like described in the article.

In the Get Data search-field, type in “Power” and select “Power BI API (Beta)”

Select the Power BI REST API custom connector

You might be prompted to sign in with your Power BI user credentials.

Then you’ll see the Navigator pane and select “fxGETData” in the Functions-Folder:

Choose function for Power BI REST API custom connector

Don’t get scared by the error-message, but copy the following string into the urlPath-field:

admin/Groups?$top=50&$expand=users,reports,dashboards,datasets

This will return the top 50 groups from your enviroment with user, reports, dashboards and datasets nicely connected already. Just adjust the number as needed.

Click Apply and you should see something like this:

Make sure to click “Transform Data” and not the yellow “Load”, as there’s till some work to do on the data.

Next click on the List in the value-field:

Transform the list into a table:

Transform to table

Accept the default-settings:

Accept default settings

and click on the expand-arrows of the column:

All the goodness at your fingertips

So here are 4 of the available entities directly connected to the top level data, no need to tie them all together:

Expand to your heart’s content

Further material

Check out the API documentation, and see which other entities you can include and how to use further URI-parameters like $filter and $skip for example.

For the refresh in the service, you have to include the connector in your gateway: https://docs.microsoft.com/en-us/power-bi/service-gateway-custom-connectors .

Edit September 25th 2019: As it turns out, dragging all the tables above into one table might create some duplicates/cartesian products. So you might narrow that down a bit as needed / feasible. But I hope that getting the connector up and running will help you now retrieve the data that you actually need.

Enjoy and stay queryious 😉

Comments (20) Write a comment

  1. Good stuff as always – but expanding reports and datasets could lead to some duplication as it introduces a cartesian product instead of just relating reports that are aligned to datasets.

    Shared datasets across workspaces also complicates this set up as reports based on shared datasets are included in the expanded reports but the dataset is not included

    Reply

    • Good catch Jordan!
      So one has to find the right balance of how many tables/entities to combine here.
      I just hope that the article helps to bridge the last mile for user to retrieve the data for admins with Power BI.
      /Imke

      Reply

  2. Just need some help! I have followed all the steps but some how Power Bi desktop doesnt show the custom connector. Saved the .mez file in Documents\Power BI Desktop\Custom Connectors, without any luck.

    We do use MFA!!

    Reply

  3. Hi,

    I am trying to configure the PowerBI Rest API for my environment and have completed the App registration steps. I have updated the AppID and Secret file with the required values. When I open up PowerBI, I see ‘PowerBIRESTAPI.Navigation(Beta)’ under Online Services.
    After I select it and click Connect, it says under an ‘Organizational account’ tab that I am not Signed in and presents me with a ‘Sign In’ button. I am logged in using my organization ID in PowerBI and permissions are OK in Azure as well.

    When I click ‘Sign In’, it returns a message [Expression.Error] We couldn’t find the file ‘AppID.txt’.

    I have ensured that the updated ‘.mez’ file in the correct folder (C:\Users\\Documents\Power BI Desktop\Custom Connectors)

    Is there something I can check?

    I have the PowerBI Version: 2.73.5586.984 64-bit (September 2019)

    Thanks,
    Sagar

    Reply

    • That sounds as if you have zipped the folder instead of the single files. So when zipping your adjusted files, make sure you select all files and zip them and not the folder (it’s a level above and that’s why the navigation fails).
      Cheers, Imke

      Reply

  4. Hi Imke

    I’ve got as far as fx GETData and pasting in the url path.

    But then I get, “We couldn’t authenticate with the credentials provided. Please try again”. Tried signing in again, but same message.

    I have multiple accounts, but I’m definitely signed in with the correct one. Honestly.

    Any ideas where the problem might be?

    Thanks

    Farhan

    Reply

      • I am having the same problem. I enter the following in the URL Path “admin/Groups?$top=50&$expand=users,reports,dashboards,datasets”. Select Apply, Then i just get an error about “We couldnt authenticate with the credentials provided”. Please try again.

        I have signed in again where it accepts my logins, but then it resorts back to the same error :s

        Reply

          • hi, I corrected the error by removing = GETData(“Groups?$top=50&$expand=users,reports,dashboards,datasets”)

  5. Thank you for such comprehensive instructions. We have been looking for details of the process to leverage the ‘hidden’ metadata and this worked perfectly.

    Reply

  7. Hi and thank you for a great post 🙂

    I have followed the steps outlined, and gets stuck, when I pick the Power BI API connector in Power BI after having clicked Get Data: Exception of type ‘Microsoft.Mashup.Engine.Interface.ResourceAccessAuthorizationException’ was thrown.

    Any ideas why this happens? I have tried with two different tenants, one where we use MFA and one where we don’t. Same result.

    Thanks in advance.

    Reply

Leave a Reply