Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Tom-P
Frequent Visitor

PowerBI - REST API - Export and then Import in Flow / Power Automate

Hi all,

 

I've been banging my head against a brick wall for a while here.

 

I've followed a number of the guides for connecting to PowerBI REST APIs using a custom connector in Flow, and generally have it working well...I'm now succesfully using flows to update parameters, refresh datasets etc.

 

Where I have failed is trying to duplicate a report and dataset, by exporting and importing.

 

I'm using the swagger posted here:

 

https://github.com/microsoft/PowerBI-CSharp/blob/master/sdk/swaggers/swagger.json

 

Flow currently looks like this:

 

Capture.PNG

 

Export is using this:

 

https://docs.microsoft.com/en-us/rest/api/power-bi/reports/exportreportingroup

 

This is succesful and if I put the body of this into a compose, it gives me a zip file, which when downloaded and renamed to .pbix, opens properly. The raw output of that action is:

 

 

{"statusCode":200,"headers":{"Pragma":"no-cache","Transfer-Encoding":"chunked","X-PowerBI-FileName":"Test File.pbix","Strict-Transport-Security":"max-age=31536000; includeSubDomains,max-age=31536000; includeSubDomains","X-Frame-Options":"deny,deny","X-Content-Type-Options":"nosniff,nosniff","RequestId":"[ID]","Access-Control-Expose-Headers":"RequestId","request-redirected":"true","home-cluster-uri":"https://wabi-uk-south-redirect.analysis.windows.net/","Cache-Control":"no-store, must-revalidate, no-cache","Date":"Wed, 29 Apr 2020 03:27:50 GMT","Content-Type":"application/zip","Content-Length":"1839288"},"body":{"$content-type":"application/zip","$content":"UEsDBBQAAAAIAHg[...rest of the content]"}}

 

 

 

I'm then trying to take the body of that and use it with the POST import to group function:

 

https://docs.microsoft.com/en-us/rest/api/power-bi/imports/postimportingroup

 

 

I'm getting this error withn a 400 status code:

 

 

 

{
  "Strict-Transport-Security": "max-age=31536000; includeSubDomains",
  "X-Frame-Options": "deny",
  "X-Content-Type-Options": "nosniff",
  "Access-Control-Expose-Headers": "RequestId",
  "request-redirected": "true",
  "home-cluster-uri": "https://wabi-uk-south-redirect.analysis.windows.net/",
  "RequestId": "d248b4cd-7093-4322-893d-ff971cff3b0f",
  "Date": "Wed, 29 Apr 2020 03:01:46 GMT",
  "Content-Length": "0"
}

 

 

 

If i click the "input" of that action, it shows as:

 

 

 

{"host":{"apiId":"subscriptions/[code removed]/providers/Microsoft.Web/locations/uksouth/runtimes/uk-001/apis/pbi-20connector-[code removed]","connectionReferenceName":"shared_pbi-20connector-[code removed]","operationId":"Imports_PostImportInGroup"},"parameters":{"groupId":"[code removed]","datasetDisplayName":"TestCopy.pbix","importInfo":{"$content-type":"application/zip","$content":"UEsDBBQAAAA[...rest of content]"},"nameConflict":"Abort"}}

 

 

 

Any ideas what I'm doing wrong?  Has anyone succesfully managed to do this that could point me in the right direction?

 

Thanks in advance.

 

Tom.

1 ACCEPTED SOLUTION

Hi @Tom-P ,

 

i found the solution.

To import a file, request Headers should include Content-Type: multipart/form-data with the file encoded as form data in the request body.

Flow_PowerBIRestAPI.png

{
  "$content-type": "multipart/form-data",
  "$multipart": [
    {
      "body": @{body('Export_PBIX')}
    }
  ]
}
Did I answer your question?
Please mark my post as solution, this will also help others.
Please give Kudos for support.

Marcus Wegener works as Full Stack Power BI Engineer at BI or DIE.
His mission is clear: "Get the most out of data, with Power BI."
twitter - LinkedIn - YouTube - website - podcast


View solution in original post

8 REPLIES 8

Hi @Tom-P ,

 

i found the solution.

To import a file, request Headers should include Content-Type: multipart/form-data with the file encoded as form data in the request body.

Flow_PowerBIRestAPI.png

{
  "$content-type": "multipart/form-data",
  "$multipart": [
    {
      "body": @{body('Export_PBIX')}
    }
  ]
}
Did I answer your question?
Please mark my post as solution, this will also help others.
Please give Kudos for support.

Marcus Wegener works as Full Stack Power BI Engineer at BI or DIE.
His mission is clear: "Get the most out of data, with Power BI."
twitter - LinkedIn - YouTube - website - podcast


Hey @mwegener 

 

This looks perfect, thank you. however, my connector is different to yours and isn't working, is there any chance you could share the details of your connector?

 

Thanks!

Hi @Tom-P ,

 

I wrote a blog post about it.

https://community.powerbi.com/t5/Power-Platform-Integrations/Archive-Power-BI-Reports-with-Power-Aut...

 

 

Did I answer your question?
Please mark my post as solution, this will also help others.
Please give Kudos for support.

Marcus Wegener works as Full Stack Power BI Engineer at BI or DIE.
His mission is clear: "Get the most out of data, with Power BI."
twitter - LinkedIn - YouTube - website - podcast


Tom-P
Frequent Visitor

Any ideas at all? Spent some more time looking at this and still getting nowhere!

 

Thanks,

Hi @Tom-P ,

I found the very useful blogs yesterday. Maybe they also can help you.

 

Please reference:

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

Calling The Power BI Export API From Power Automate, Part 2: Creating A Flow That Exports A Paginate... 

 

Best Regards,
Xue Ding
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Thanks @v-xuding-msft 

 

These are indeed very helpful, and I've spent a lot of time looking at these to get me as far as I have, but do not help with my specific issue. 

 

Pretty much the only thing I think I am missing, is what do I need to do with the output of the export step, before injecting it into the import step. It's probably as simple as a compose with some text to define something, but I just cannot figure it out.

 

Thanks,

 

Tom.

Does your import step work with a pbix file that you haven't just exported, ie one that you've built yourself in Power BI Desktop?

 

The only other thing I can suggest is to look at this example of how to call the export/import endpoints in PowerShell, which might highlight something that you're doing wrong:

https://github.com/Azure-Samples/powerbi-powershell/blob/master/copyWorkspace.ps1

 

HTH,

 

Chris

Tom-P
Frequent Visitor

Hi @cwebb 

 

Thanks for looking at this.

 

I have indeed tried manually too, still failing. From looking at the powershell it looks like the bit that I'm missing is the conversion to the multipart form data:

 

$packageFileStream = New-Object System.IO.FileStream @($temp_path, [System.IO.FileMode]::Open)
        
	    $contentDispositionHeaderValue = New-Object System.Net.Http.Headers.ContentDispositionHeaderValue "form-data"
	    $contentDispositionHeaderValue.Name = "file0"
	    $contentDispositionHeaderValue.FileName = $file_name
 
        $streamContent = New-Object System.Net.Http.StreamContent $packageFileStream
        $streamContent.Headers.ContentDisposition = $contentDispositionHeaderValue
        
        $content = New-Object System.Net.Http.MultipartFormDataContent
        $content.Add($streamContent)

 

Any ideas how to do that in Flow?

 

Thanks,

 

Tom.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors