Calling The Power BI Export API From Power Automate, Part 3: Creating An Alerting Solution

In my last two posts (see here and here) I showed you how you can call the new endpoints in the Power BI REST API for exporting a report. There are some obvious, extremely useful applications for this such as emailing PDF exports of a report out to large groups of users. In this post, however, I’ll show you how you can use this for something different: creating a Power BI alerting solution.

Now I know what you’re thinking: we already have alerts in Power BI and we can also trigger a Power Automate flow when an alert fires. This functionality is a bit limited though: you can only create an alert on a dashboard, not a report, and then only on some visuals; what’s more you can only use fairly simple rules to trigger an alert. You may need something more sophisticated, but while Power Automate would seem to be the perfect tool for building an alerting solution there’s another problem: it isn’t possible to query a Power BI dataset from Power Automate… until now.

How? The new export endpoints in the Power BI REST API allow you to export a paginated report to a number of different formats, one of which is XML. This means you can write any DAX query you want, use it in a table in a paginated report, export the paginated report to XML in Power Automate and bingo – you have the output of the query in a format that Power Automate can read and do something useful with.

Let’s see an example. Take the following table of data in a Power BI dataset:

Source data

It shows sales for different countries, and let’s say that if the sales value exceeds the threshold given threshold then you want to send an email to the address given in the last column.

The following DAX query filters this table to return all the rows where sales is greater than the threshold:

EVALUATE
FILTER(
'Sales',
'Sales'[Sales]>'Sales'[Threshold]
)

Query output

It’s quite easy to create a basic paginated report in Power BI Report Builder with just a single tablix to display the output of this query:

Paginated report output

Now, let’s take the Power Automate flow that I described in my last post and alter it slightly.

First of all, instead of exporting to CSV as I did last time, you need to change the action that calls the Export To File endpoint to export the report to XML:

XML export option

More substantial changes are needed at the end of the flow, where the exported report is returned. Here’s what this part of the flow looks like at a high level after the changes:

Overview End

The first action shown here, Download the exported report file, gets the XML returned from Power BI. The SSRS documentation has a lot of detail about how a report gets rendered to XML here, but by keeping the report very basic it’s easy to understand the format of the XML. Here’s what gets returned in this case:

<?xml version="1.0" encoding="UTF-8"?>
<Report 
xmlns="AlertsPaginatedReport" 
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
xsi:schemaLocation="AlertsPaginatedReport 
https://redacted" 
Name="AlertsPaginatedReport"> 
<Tablix1> 
<Details_Collection> 
<Details Country="UK" Sales="5" 
Threshold="2" Email="chris@mycompany.com" /> 
<Details Country="New Zealand" Sales="6" 
Threshold="1" Email="phil@mycompany.com" /> 
<Details Country="Netherlands" Sales="5" 
Threshold="2" Email="kasper@mycompany.com" /> 
</Details_Collection> 
</Tablix1> 
</Report>

The approach I’ve used to consume this XML in Power Automate is basically the one described in this post I found. The Compose action uses an XPath query to return an array containing one item for each row in the query results; writing the XPath query was quite painful but I finally got it working. Here’s the expression from the action:

xpath(

xml(body('Download_the_exported_report_file')),

'/*[local-name() = ''Report'']/*[local-name() = ''Tablix1'']/*[local-name() = ''Details_Collection'']/*[local-name() = ''Details'']')
Next, an Apply to each is used to iterate over each item in this array:
Apply to each

 

Finally, inside this loop, another Compose action retrieves the email address from the current iteration and this is then used to send an email:

Get email

Here’s the expression used in the Get Email Address action:

xpath(

xml(item()),

'string(/*[local-name() = ''Details'']/@Email)')
And that’s it. It’s a bit of a convoluted workaround, I admit, but it does the job; please also bear in mind the limitations of the export API listed here.
I’m sure there a lots of other things apart from alerting with the ability to consume the output of a DAX query in Power Automate, so if you have any good ideas please let me know in the comments!

Calling The Power BI Export API From Power Automate, Part 2: Creating A Flow That Exports A Paginated Report To A CSV File

In my last post I showed how to create a Power Automate custom connector for the new endpoints in the Power BI REST API for exporting a report to a file. In this post I’ll show you how to use this custom connector in a flow in Power Automate.

The three endpoints in the Power BI API that need to be called to export a report (and which have been included in the custom connector from my last post) are as follows:

  • Export To File In Group must be called first, to start the process of exporting either a Power BI report or a paginated report to a file. Exporting may take some time, though, so the exported file doesn’t get returned at this point. In the headers of the response there is a retry-after value in seconds telling you how long you must wait initially, before…
  • …calling Get Export To File Status In Group to find out whether the export is ready or not. If it isn’t ready you again need to wait the number of seconds specified in the retry-after header before checking the status again. If it is ready, you can then…
  • …call Get File Of Export To File In Group to download the export file.

This is all described in the C# example code here, so the challenge is to translate this into a flow. In my case, I want to export the following paginated report to a CSV file:

Paginated report

Here’s what my flow looks like at the top level:

High level view

To make things easy to understand I’m using a manual trigger to start the flow and hard-coding the report I want to export. The second Action calls Export To File In Group:

Start export

As I said, in the response from this first API call the retry-after header tells you how many seconds you should wait before checking the export status for the first time. This value is used in the next Action, which adds the delay:

First delay

Once this delay has passed there’s a Do loop that will call the Get Export To File Status In Group endpoint up to ten times to check if the export is ready:

Do loop

This call uses the export id found in the response from the call to Export To File In Group:

Check status

If this call returns that the status is Running or NotStarted, then it will again wait for the number of seconds specified in the retry-after header; if not then there is no delay:

Second delay

The expression on the Do loop breaks the loop if the export status is Succeeded or Failed:

@or(
equals(body(‘Call_Export_Status_endpoint_to_check_if_export_is_ready’)?[‘status’], ‘Succeeded’),
equals(body(‘Call_Export_Status_endpoint_to_check_if_export_is_ready’)?[‘status’], ‘Failed’)
)

After the loop, there is a check to see if the export status is Succeeded:

Last condition

If the export status is Succeeded, then the file is downloaded and saved to OneDrive for Business:

Save file

Otherwise, the flow sends a failure notification email:

Failure email

And here’s the exported CSV file viewed in Excel Online:

Excel

And that’s it. I know this flow isn’t as robust as it could be, but I hope it gives you an idea of how to use the custom connector to export Power BI reports in Power Automate; as I said last time, I have very little Power Automate experience so please forgive any newbie mistakes! If you can see a way to improve on what I’ve done here, please leave a comment.

Automated exports from a Power BI paginated report to a CSV file is already very useful but there are other fun things we can do with this – and in my next post I’ll show you another, less obvious example.

Calling The Power BI Export API From Power Automate, Part 1: Creating A Custom Connector

Recently, a new set of endpoints in the Power BI REST API for exporting Power BI reports and paginated reports to files went into public preview (see the main announcement here and the paginated reports announcement here). Since there are all kinds of cool things you can do with this I thought I would write a few posts on how to use these endpoints in Power Automate. In this post I’ll talk about setting up a custom connector in Power Automate; in the next post I’ll talk about how to use this custom connector in Power Automate; and after that I’ll show you some less obvious uses for all this.

Before we go any further, and before you get too excited, you should read the limitations of the public preview listed here, especially those around the number of report pages that can be exported per hour and the number of pages in a report that can be exported. Also, this functionality is only available with Power BI Premium or Power BI Embedded.

Creating a Power Automate custom connector for the Power BI REST API is something that several people have blogged about already in detail. Konstantinos Ioannou has a very detailed walkthrough here; Jese Navaranjan has a video walkthrough here; and I blogged about how you could use the Swagger definition of the Power BI REST API to create a custom connector here. I’m not going to go over all these steps again but there are a few specifics that need pointing out if you want to build your custom connector manually. If you’re lazy, I exported my custom connector to a Swagger file and you can download it here; you should be able to import it and create your own custom connector very easily.  I don’t pretend to be a Power Automate expert so please excuse any newbie mistakes!

The three endpoints that you’ll need to use to export to a file (unless your report is in your My Workspace, in which case there are three other equivalent endpoints) are Export To File In Group, Get Export To File Status In Group and Get File Of Export To File In Group. There’s good documentation for regular Power BI reports here and paginated reports here, but in summary Export To File In Group starts the export process but because this might take a long time, doesn’t return the exported file; Get Export To File Status In Group allows you to check the status of the export; and Get File Of Export To File In Group returns the file once the export is ready.

Creating a custom connector in Power Automate is a four-step process and steps 1, 2 and 4 (“General”, “Security” and “Test”) are well covered in the guides above. Step 3 (“Definition”) is where you need to create three Actions for the three endpoints above.

After filling in the General and Security pages, go to the Definition page and click the New action button to create an Action for the Export To File In Group endpoint. You first need to fill in the information in the General section:

ExportFileGeneral

Then in the Request section click the Import from sample button, select POST and paste

https://api.powerbi.com/v1.0/myorg/groups/{groupId}/reports/{reportId}/ExportTo

…into the URL box and the sample payload here into the Body box:

ExportFileRequest

Setting up the other two endpoints is similar except that you need to select GET instead of POST and you don’t need to paste anything into the Body box. Here are the two templatised URLs to use for them:

Get Export To File Status In Group: https://api.powerbi.com/v1.0/myorg/groups/{groupId}/reports/{reportId}/exports/{exportId}

Get File Of Export To File In Group: https://api.powerbi.com/v1.0/myorg/groups/{groupId}/reports/{reportId}/exports/{exportId}/file

There’s more to do on this page, but at this point you should go to the Test page and test these three new Actions. For Export To File In Group you need to at least enter a groupId, a reportId and a format:

TestBox

The groupId and reportId can be found by navigating to your report in the Power BI portal; you can extract the groupId and reportId from the URL like so:

URLgroupsreports

The valid values for format (and the other parameter values) are in the docs.

Assuming that it all works, you will be able to scroll down and see the response. Copy all the JSON in the Body section and save it somewhere temporarily.

ExportId

Do the same thing for the other two Actions you have created; these two actions take a third parameter called exportId, which is the id value in the response that I’ve highlighted in the screenshot immediately above.

Once you have the Body text from Export To File In Group and Export To File Status In Group (the response from Get File Of Export To File In Group should be your report export), go back to the Definitions page and for these two Actions scroll down to the Response section and click on Add default response.

ResponseSample

There are two things to do here for both Actions. First, paste the responses you got on the Test pane into Body; next, paste:

retry-after 30

…into the Headers section and then click Import. This will make the output of the Actions much easier to consume in Power Automate later on.

Your custom connector is now ready, and in part 2 of this series I’ll show you how to use it in Power Automate.

[Thanks to Jaime Tarquino and Chris Finlan for their help getting this working]

 

Using OpenApi.Document() To Create A Power BI Custom Connector For The Power BI REST API

The idea of creating a Power BI custom connector for the Power BI REST API is not a new one: Miguel Escobar wrote one earlier this year (see here for the source code and documentation). However the Power BI REST API has a lot of endpoints so writing code to support them all would be extremely time-consuming, and there is another option: using the new OpenApi.Document() M function – only available for custom connectors, and not in Power BI Desktop or Excel – to read the API definition from an Open API (aka Swagger) definition file.

Full documentation and samples for using OpenApi.Document are available here, and an Open API definition file for the Power BI REST API is available here. The Power BI REST API uses Azure Active Directory authentication and handling AAD authentication in custom connectors is well documented here; Miguel’s sample shows how to adapt this code for Power BI.

After that, just download the Power BI REST API definition, save it as a .json file and add it to your Visual Studio custom connector project and set the Build Action property of the file to Compile:

image

You can then use the Extension.Contents() M function to read the contents of the file in code and pass that to OpenApi.Document(). The last problem to solve is that OpenApi.Document() only supports basic and anonymous authentication by default, so support for OAuth2 has to be handled using the ManualCredentials option. Here’s the M function definition from my demo project showing how to do this:

[DataSource.Kind="OAuth2Demo", Publish="OAuth2Demo.Publish"]
shared OAuth2.OpenAPIDemo = () =>
    let
        OAuthCredential = Extension.CurrentCredential(),
        OAuthToken = OAuthCredential[access_token],        
        SecurityHeaders = [ Authorization = "Bearer " & OAuthToken],
        PBISwagger = Extension.Contents("PBISwagger.json"),
        CallWebService = 
 	 OpenApi.Document(
	  PBISwagger,
	  [ Headers = SecurityHeaders, ManualCredentials = true ]
	 )
    in
        CallWebService;

Once you build your custom connector, you see all of the endpoints (or at least those that support GET and POST requests) exposed as functions in the Navigation table:

image

It looks super-easy to build a custom connector this way, but is it a actually good idea? I can see a few pros and cons:

  • As you’ll find if you try this, not everything ‘just works’ – there are a few functions that return errors.
  • In this case I embedded the Open API definition file in the project but it’s also possible to download it dynamically using Web.Contents(). This means that whenever the published Open API definition file is updated the custom connector also updates automatically. Are you sure the published Open API definition file will get updated when the API changes, though? What if it doesn’t, or there is an error in it?
  • Connecting to an API is all very well but it’s very important that a custom connector delivers data in a format that is modelled appropriately for Power BI, and very few APIs do this on their own. Pretty much every custom connector I have built has a lot of code in it to transform the data output by the API into something like a star schema, and the need to do this cancels out all the magic automatic stuff that OpenApi.Document() does for you.

Calling The Power BI REST API From Microsoft Flow, Part 3: Finding All Data Sources Used In A Power BI Tenant

[Part 1 of this series can be found here and part 2 here]

The problem with self-service BI is that you never quite know what your users are up to. For example, what data sources are they using? Are there hundreds of Excel files being used as data sources for reports that you don’t know about? If so, where are they? Could they and should they be replaced by a database or something else more robust? In this post I’ll show you how you can use Microsoft Flow and the Power BI REST API (see part 1 to find out how to create a Flow custom connector to call the Power BI API) to get the details of all the data sources used in all of the workspaces of your Power BI tenant.

I’ll admit that doing this turned out to be a bit trickier than I had expected. My plan was to use the GetDatasetsAsAdmin endpoint to get a list of all datasets, loop over each one and then call the (undocumented, but in the REST API’s Swagger file and therefore in my custom connector) GetDatsourcesAsAdmin endpoint to get the datasources in each dataset. Both these endpoints require administrative permissions to call, so I made sure my custom connector had the correct permissions (at least Tenant.Read.All – you can check this in the Azure Portal on the app you registered in Azure Active Directory) and I ran the Flow as a user with Power BI Admin permissions. But I kept getting 404 errors when requesting the data sources for certain datasets . Eventually I realised that GetDatasetsAsAdmin returns a list of all of the datasets that have ever been created in a tenant, including ones that have been deleted, with no indication as to which ones are the deleted ones, and the errors were the result of asking for the datasources from deleted datasets. Hmm. Once I realised I could ignore these errors the Flow was fairly easy to build.

Here’s the top-level view:

image

Step-by-step:

  • I use a button to trigger the flow here, but if you want to create an automated report you should probably run this Flow on a schedule.
  • Initialize variable initialises an array called datasources to hold the details of each datasource:
    image
  • Return a list of datasets for the organisation calls the GetDatasetsAsAdmin endpoint. As I said, if there was any indication as to whether the dataset had been deleted (if you look at the results returned by the GetGroupsAsAdmin endpoint it tells you whether a group has been deleted or not) then it would be possible to use $filter to declare an OData filter to remove them. But it isn’t.
    image
  • Parse list of datasets parses the JSON containing the list of datasets returned by the previous action. I generated the schema from a sample payload I got from calling the endpoint in the “test” section of the edit screen for the Flow custom connector. I had to edit it to make sure only the “id” and “name” properties are listed as required, as shown below.
    image
  • Apply to each dataset loops over each dataset, and I’ll describe that in more detail below.
  • Create CSV table takes the contents of the array variable defined above and turns it into a CSV file.
    image
    One very important point to note is that I had to click the “Configure run after” option and state that this action should run even after Apply to each dataset had failed, which it would inevitably after the 404 errors mentioned above. Just because one iteration fails doesn’t mean the whole Flow should be stopped.
    image
  • Create file simply saves the resulting CSV file into a folder in OneDrive for Business.
    image

 

Going back to the Apply to each dataset action, here’s what it does:

image

  • It loops over every dataset returned by the call to GetDatasetsAsAdmin
  • Return a list of datasources for the specified dataset calls GetDatasourcesAsAdmin to get the datasources in each dataset; as I said, this fails when the dataset has been deleted.
    image
  • Parse datasources parses the JSON returned by the previous action. Once again I generated the schema using a sample payload, and in this case I edited it manually to ensure that only “datasourceType”, “connectionDetails” and “datasourceId” were required.image
  • Apply to each datasource then loops over each datasource (one dataset can have many datasources) and Append to array variable appends some information about each datasource to the datasources array.
    image
    The connectionDetails property for a datasource returns details such as connection strings and file paths, but since I also wanted the type of the data source and the name and id of the dataset I used the following expression to create an array to append with all this information in:
    addProperty(
    addProperty(
    addProperty(
    items('Apply_to_each_datasource')?['connectionDetails'], 
    'datasetname', 
    items('Apply_to_each_dataset')?['name']), 
    'datasetid', 
    items('Apply_to_each_dataset')?['id']), 
    'datasourceType', 
    items('Apply_to_each_datasource')?['datasourceType'])
    

It turned out that my Power BI tenant had almost 190 datasets, both deleted and not deleted, and the Flow took eleven minutes to run.

The output of this Flow is a rather messy CSV file when you open it in Excel Online:

image

Once you load the data into Power BI though, you can build some interesting reports such as this one which shows the number of files used as datasources in my tenant, broken down by file type:

image

Calling The Power BI REST API From Microsoft Flow, Part 2: Refreshing A Dataset When a Data Source Changes

For a while now I’ve had an idea stuck in my head: wouldn’t it be cool to build a Power BI solution where a user could enter data into an Excel workbook and then, as soon as they had done so, they could see their new data in a Power BI report? It would be really useful for planning/budgeting applications and what-if analysis. I had hoped that a DirectQuery model using the CData Excel custom connector (mentioned here) might work but the performance wasn’t good enough; using Flow with the Power BI REST API (see Part 1 of this series for details on how to get this set up) gets me closer to my goal, even if there’s still one major problem with the approach. Here’s how…

First of all, I have two Excel files saved in a OneDrive For Business folder:

image

The contents of each Excel file is more or less the same, a table containing budget data for four quarters:

image

These are the files that my hypothetical users will be editing. I also have a very basic Power BI dataset that imports the data from these two files, and a report to display the data:

image

Now for the Flow. For the trigger I use a OneDrive For Business “When a file is modified” that fires when a file in the above folder is changed:

image

I had to turn on Concurrency Control for the trigger and set the Degree of Parallelism property to 1, to make sure only one instance of the Flow ran at any one time – there’s no point trying to refresh the dataset if another instance of the Flow is already running.

image

Next I have an action from the Power BI custom connector created in my last post. It calls the Power BI API’s Get Refresh History In Group endpoint and gets details of the last refresh of the dataset:

image

This returns a JSON value containing the details of the last refresh; the JSON goes to a Parse JSON action so later on I can get the Status of the last refresh. This action requires a schema, which I generated from a sample payload given in the Power BI REST API documentation. I had to delete endTime from the list of required properties in the schema because it won’t be returned if the most recent refresh is currently in progress:

image

Next there’s an Apply to each to loop over each refresh returned by the API; since only the most recent refresh is returned I know this will only ever execute once.

image

Inside the Apply to each I have a Condition that looks at the Status property of the most recent refresh and checks whether the Status is Completed:

image

If the Status is indeed Completed, then the Flow calls the Refresh Dataset In Group endpoint to refresh the dataset:

image

If the Status of the last refresh is not Completed (either because it failed for some reason, or because there is already a refresh in progress), there is a delay of one minute and the Flow then starts itself again:

image

As I’ve said before, I’m not an expert on Flow but this seems to work reliably with the limited testing I’ve done on it. Here it is in action:

ExcelBudgetDemo

So what is the problem with this approach? It’s that if you are using Power BI Pro you can only refresh a dataset eight times a day and if you are using Power BI Premium you can only refresh a dataset forty-eight times a day. There have been rumours that this forty-eight times a day refresh limit for Premium has been lifted, or might be lifted soon, or doesn’t apply when you refresh via the API, but I specifically tested this and got the following error message: “Invalid dataset refresh request. Number of refresh requests in last 24 hours exceeded limit” (interestingly this didn’t appear as a failed refresh in the refresh history of the dataset). Since it’s reasonable to assume that any Excel file will be updated more than forty-eight times per day in a busy period I don’t think this approach is usable in the real world; all the same I’m happy with this as a proof-of-concept showing what might be possible if the refresh limit is lifted in the future though.

Calling The Power BI REST API From Microsoft Flow, Part 1: Creating A Flow Custom Connector

Playing around with Microsoft Flow recently, I was reminded of the following blog post from a few months ago by Konstantinos Ioannou about using Flow to call the Power BI REST API to refresh a dataset:

https://medium.com/@Konstantinos_Ioannou/refresh-powerbi-dataset-with-microsoft-flow-73836c727c33

I was impressed by this post when I read it, but don’t think I understood quite how many exciting possibilities this technique opens up for Power BI users until I started to use it myself. The Power BI dev team are making a big investment in the API yet most Power BI users, myself included, are not developers and can’t easily write code (or PowerShell scripts) to call the API. With Flow, however, you can use the API without writing any code at all and solve a whole series of  common problems easily. In this series of blog posts I’m going to show a few examples of this.

Before that, though, there is something to add to what Konstantinos says about creating the custom connector. In his post he describes how you can add individual actions to the custom connector, but with Flow you can also create a custom connector from an OpenAPI definition and it turns out that there is an OpenAPI definition file for the Power BI REST API available here. As a result you can create a Flow custom connector with (at the time of writing) a whopping 116 actions by following the instructions in Konstantinos’s post, but instead of using the “Create from blank” option using the “Import an OpenAPI file” option instead and uploading the Power BI REST API OpenAPI definition file. You’ll find that the text for the summaries and descriptions of many of the actions needs cleaning up – in particular, Flow doesn’t allow actions with names that finish with a dot so you’ll have to go through each one and remove that character – but doing this is much faster and easier than adding the actions manually.

image

And one last thing to point out: as Konstantinos mentions in his post, if you get a 404 error while testing the connector, just wait a few minutes!

%d bloggers like this: