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:
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)”
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:
Don’t get scared by the error-message, but copy the following string into the urlPath-field:
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:
Accept the default-settings:
and click on the expand-arrows of the column:
So here are 4 of the available entities directly connected to the top level data, no need to tie them all together:
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 😉