Export data from Power BI using Microsoft Flow

Edit 5th May 2019: Unfortunately this method will not work in the Power BI service!

In my last 2 posts I’ve described a way to automatically validate attachments from incoming E-mails. Microsoft Flow would watch for incoming E-mails, that match certain criteria and move their attachments to a dedicated folder. Then it would trigger a refresh of a Power BI dataset, that has been designed to check for errors in those attachments. Data driven alerts in Power BI would indicate if there are errors and trigger a Flow that sends an E-mail back to the sender, informing him that his attachments didn’t meet the agreed criteria.

In this article I will now explain how not just a trigger about the existence of a faulty attachment could be passed back to Flow, but also the corresponding data itself. Therefore I write a query that exports data from Power BI to Flow. But watch out: This is not suitable for very big tables. I experienced timeouts at tables with 300k rows already.

Send data from Power BI to Microsoft Flow

As Chris Webb described in this article, Power Query can create POST requests to a webservice, thereby passing values in the body of the call to the webservice. This allows to export the data from Power BI. With Flow, it is very easy to setup a webservice: Just create a Flow whose trigger is a “When a HTTP request is received” (no further inputs in that step) and add a “Compose”-action with the Body-element in it. Then save the Flow and copy the generated URL:

Setup webservice in Flow that listens for incoming data (export from Power BI)

Power BI

In Power BI, you perform the following steps:

  1. Transform your error-table into a list of records (as that is the format of a JSON-array that Flow likes):
    • row 3: Table.ToRecords(<YourTableName>)
  2. Transform that into a JSON-binary that the Web.Contents-function can digest:
    • row 4: Json.FromValue(<YourListOfRecordsFromAbove>)
  3. Make the Web-call to the copied URL (use Anonymus credentials):
    • row 5-7: Web.Contents(<PasteTheURLFromFlowHere>, [Content=<JsonFromAbove>])
  4. Parse the result and load this query to the datamodel (this is very important, because otherwise the WebCall wouldn’t be made if the dataset is refreshed!):
    • row 8: Lines.FromBinary(<WebCallFromAbove>)

Code to export a table from Power BI to Flow

Back in Flow

Modify the “Compose”-step to parse out the table from the JSON like this:

Next step is to create a csv table:

And export it to OneDrive:

Save csv to OneDrive for Business

I’ve passed the FolderPath and FileName dynamically from Power BI here, but that’s not necessary. Just make sure that the folder is different than the one that contains the original attachments. A trigger will be set on this folder that sends the e-mail back to the sender. So the saving of the original attachment mustn’t trigger this return-email back to the sender already.

Now this sub-flow is completed. In the next steps you will adjust the main flow (like described in the previous post) to attach the generated file to the email.

Adjust main Flow

Task is to attach the stored csv-file to the email that will be sent out to the sender of the attachment. There are a couple of new steps (red) and an adjustment in the “Send email”-step:

Adjustments for the return-attachment

As the query, which calls the web-service will probably be executed twice, I’ve added a wait-step. The time has to fit to the table length, so you might test this for your specific file behaviour. Then the content from the saved file has to be fetched and added in the attachment:

Adjustments in main Flow: Attach e-mail

The last 2 new steps in the flow are similar to the previous ones and simply delete the files from the “return-attachment”-folder. Of course, if you want to keep them in a folder somewhere, you can instead implement a step that moves the file over there, instead of deleting.

Outlook

As soon as Power Query will be fully integrated in Flow, these tasks don’t need any Power BI-involvement any more: You can do the validations directly in the editor there. Although: Given the current lack of support to parse csv-files in Flow (and the terrible performance of the current workaround), I’m wondering if it wouldn’t be better, if the Power Query connector in Flow would save it’s results to csv-files instead of pushing it back to Flow (a bit like the new dataflows are doing it now). This would allow for mass-data-transformations and forwarding its results, without congesting the Flow-service.

What are your thoughts on this?

Enjoy & stay queryious 😉

Comments (20) Write a comment

  1. Got this error in data source credentials when published to Power BI service.

    Failed to update data source credentials: Web.Contents failed to get contents from ‘https://prod-56.westeurope.logic.azure.com/workflows/346eea9b6717462695ca367562e49f1a/triggers/manual/paths/invoke?api-version=2016-06-01&sp=%2Ftriggers%2Fmanual%2Frun&sv=1.0&sig=e6iSqgGlmOJGOUcEGeuslyUvoAXgzrtL9lyOpnuWPv4’ (400): Bad RequestHide details
    Activity ID: eee3fbcd-d6cb-4639-a701-8ef3108ffad4
    Request ID: 9fd6a5f4-1056-3ae2-d083-abaa425e8f84
    Status code: 400
    Time: Wed May 08 2019 00:35:47 GMT+0500 (Pakistan Standard Time)
    Version: 13.0.9264.166
    Cluster URI: https://wabi-west-europe-redirect.analysis.windows.net

    Reply

  2. Actually it is an Data source credentials error. everything works fine in Power BI desktop but when I upload it to Power BI service and tries to refresh, I got this error. (I used Anonymous credentials for HTTP request.)

    Reply

    • I’m very sorry, just recognized that I didn’t test this in the service. Unlike the data driven alert, that allows to sign in to SharePoint, there is not such an option for this method.
      That was a bad overlook from my side. Will have to check if it’s possible by using a custom connector for it.
      Very sorry for wasting your time,
      Imke

      Reply

  3. Hi Imke,

    Thank you for the suggestion, and I got that working in the desktop and in the service. Very cool and useful stuff!

    I also have a need to export from Power BI to an Excel file (xlsx). I’m struggling to adapt the export to csv concept to fit the need to export to Excel. Any idea what that script or syntax would look like in the Power Query editor?

    Reply

  4. As Chris Webb described in this article, Power Query can create POST requests to a webservice, thereby passing values in the body of the call to the webservice. This
    …i do not understand the very first step…i have did it till let
    Source = #” Tracker – SAP”,
    ToArray = Table.ToRecords(Source),
    ToJsonBinary = Json.FromValue(ToArray)
    in
    ToJsonBinary

    but how to go further
    shree

    Reply

    • You have to add the steps “WebCall” and “LinesFromBinary” like described in the article.

      Reply

  5. Could you please specify the steps for webcall method to call power bi datasets as i am unable to get the steps for it

    Reply

    • You’re missing the steps 3) and 4) from the listing in the article above. That’s the last 2 steps in this query:

      let
      Source = #”1_SourceTable”,
      ToArray = Table.ToRecords(Source),
      ToJsonBinary = Json.FromValue(ToArray),
      WebCall = Web.Contents(
      “https://prod-123.westeurope.logic.azure.com:443/workflows/….xxxxx….geQ”,
      [Content = ToJsonBinary]),
      LinesFromBinary = Lines.FromBinary(WebCall)

      in
      LinesFromBinary

      Where “https://prod-123.westeurope.logic.azure.com:443/workflows/….xxxxx….geQ” needs to be replaced by URL from Flow (“When a HTTP request is received” -> “HTTP POST URL”

      /Imke

      Reply

      • I am entering below mentioned code in “When a HTTP request is received” in flow to generate URL
        let
        Source = #”Tracker – SAP”,
        ToArray = Table.ToRecords(Source),
        ToJsonBinary = Json.FromValue(ToArray),

        [Content = ToJsonBinary]),
        LinesFromBinary = Lines.FromBinary(WebCall)
        in
        LinesFromBinary

        but getting error message in the first line as expecting JSON object.

        My question is how to generate this “HTTP POST URL” in flow
        1st step
        Create Instant flow
        2nd Step
        Select “When a HTTP request is received”
        3rd
        I am entering above mentioned code to get URL
        Kindly correct me below
        how to generate this “HTTP POST URL” in flow

        Reply

  6. Hello,
    Actually my simple question ..how you are connecting to power bi using http post request..is it that you are doing schema export or or calling power bi web service as this url requires proper codes to call and your codes says it is invalid json code..please assist

    Reply

Leave a Reply

Die Datenschutzbestimmungen finden Sie hier: / Please find the privacy policy here: https://wp.me/P6lgsG-Rz