cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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
Super User II
Super User II

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 work at KUMAVISION AG , one of the world's largest
implementation partners for Microsoft Dynamics. #
"Get the most out of data, with Power BI."
twitter - LinkedIn - YouTube - website


View solution in original post

8 REPLIES 8
Super User II
Super User II

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 work at KUMAVISION AG , one of the world's largest
implementation partners for Microsoft Dynamics. #
"Get the most out of data, with Power BI."
twitter - LinkedIn - YouTube - website


View solution in original post

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 work at KUMAVISION AG , one of the world's largest
implementation partners for Microsoft Dynamics. #
"Get the most out of data, with Power BI."
twitter - LinkedIn - YouTube - website


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

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
secondImage

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

secondImage

Power BI Women

Join our monthly meetings and learning sessions.

secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors