How do I get Power BI REST API data into a Power BI report?

[jarwest]

Hey all,

I've been tasked with replacing a report that gets data from the Power BI REST API into a Power BI Report to display information on all our Power BI resources, such as dataflows, reports, and workspaces. The original report no longer seems to function as of January this year, if not earlier, stating 'Expression.Error: The import PowerBIRESTAPI.Navigation matches no exports. Did you miss a module reference?'. Is there a way for me to get this to work all in Power BI, or do I need to make a process that calls the API and drops it somewhere I can reference with a Power BI report?
The Advanced Editor of the original report reads as follows. I'm not sure how this original file was configured, but I do remember it working at some point.

let
    Source = PowerBIRESTAPI.Navigation(),
    AppWorkspace = Source{[Key="AppWorkspace"]}[Data],
    Dataflows1 = AppWorkspace{[Key="Dataflows"]}[Data]
in
    Dataflows1

Any and all help is appreciated.

[v-veshwara-msft]

Hi @jarwest ,

May I ask if the solution provided has addressed your needs? If so, please consider marking it as Accepted Solution to help others with similar queries.

If you need any further assistance, feel free to reach out.

Thank you.

[v-veshwara-msft]

Hi @jarwest ,

Following up to see if your query has been resolved. If any of the responses helped, please consider marking the relevant reply as the 'Accepted Solution' to assist others with similar questions.

If you're still facing issues, feel free to reach out.

Thank you.

[v-veshwara-msft]

Hi @jarwest ,

Just checking in to see if you query is resolved and if any responses were helpful. If so, kindly consider marking the helpful reply as 'Accepted Solution' to help others with similar queries. 

Otherwise, feel free to reach out for further assistance.

Thank you.

[v-veshwara-msft]

Hi @jarwest ,
Thanks for posting in Microsoft Fabric Community.

As mentioned by @Jai-Rathinavel , the PowerBIRESTAPI.Navigation() function in your report is from a custom connector that uses a .mez file to access the Power BI REST API. This connector isn’t part of the standard Power BI setup, so without the .mez file in the right location, Power BI will throw the error you’re seeing.You can download the .mez file from here:
Custom Connector link 

Once downloaded, place the file in the following folder on your machine:
Documents\Power BI Desktop\Custom Connectors

Also, make sure to allow custom connectors in Power BI Desktop under File > Options and Settings > Options > Security > Data Extensions by selecting (Not recommended) Allow any extension to load without validation or warning.

You should be able to see the Power BI API Custom Connector in your Get Data Window:

For more information on how to create your own custom connectors folder, please visit the official documentation from Microsoft Power BI Custom Connectors.

If you prefer not to use the custom connector, the alternative is to use Web.Contents() in Power Query to call the Power BI REST API directly, which is refreshable in the Power BI Service. This method requires setting up a service principal for authentication.

Web.Contents - PowerQuery M | Microsoft Learn

Embed Power BI content in an embedded analytics application with service principal and an applicatio...

Hope this helps. Please reach out for further assistance.
If this post helps, then please consider to give a kudos and Accept as the solution to help the other members find it more quickly.

Thank you.

[jarwest]

Hello! I accepted a solution on this, sorry for the delay. Jai's solution works great, the only issue I'm running into now is that I can't get the resource to refresh in PBI service. Do I need to install anything onto a gateway or anything?

[Jai-Rathinavel · Accepted solution]

@jarwest 

1. You have two ways of connecting to power query one is using a Power BI Connector which will be present as a mez file in your local directory but cannot be refreshed in service without a gateway. Refer the below link

Power BI REST API Connector — The Power User

2. The other way is refreshable in service and you will need a service principal with necessary scopes to call the API. Please check the below blog on how to call Power BI Rest APIs within power query using Service Principal

Connect with Power BI to the Power BI REST API with a Service Principal – PBI Guy

Thanks,

Jai Rathinavel | LinkedIn

[jarwest]

Hey Jai, I think the first solution is actually the solution being used to build this old report, thanks for finding this! I don't know if this solution works anymore, I'm having trouble authenticating, and I'd imagine that's why this report started failing to begin with. Does the person who sets this up need to be a PBI admin?

If I can't get it working I'll explore the second option. Thanks!

[Jai-Rathinavel]

@jarwest  The first method will only work If you have the .mez file in your local directory. 

[jarwest]

Hey Jai, sorry for the delayed response, but this did end up working. I already had it in the proper folder I just didn't have proper permissions for the API itself. I'm currently struggling with getting it to refresh in PBI service, would it just be an issue of getting the data source into a gateway?

Thanks again! I did go ahead and mark your answer as the solution