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

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

Anonymous — Thu, 30 Apr 2020 09:12:09 GMT

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:

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.

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

Anonymous — Wed, 29 Apr 2020 14:13:19 GMT

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

Thanks,

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

v-xuding-msft — Thu, 30 Apr 2020 01:50:43 GMT

Hi Anonymous ,

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 Paginated Report To A CSV File

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

Anonymous — Thu, 30 Apr 2020 08:48:44 GMT

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.

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

cwebb — Thu, 30 Apr 2020 12:08:05 GMT

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

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

Anonymous — Thu, 30 Apr 2020 18:26:50 GMT

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.

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

mwegener — Fri, 19 Jun 2020 10:53:44 GMT

Hi Anonymous ,

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.

{ "$content-type": "multipart/form-data", "$multipart": [ { "body": @{body('Export_PBIX')} } ] }

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

Anonymous — Sat, 22 Aug 2020 09:58:16 GMT

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!

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

mwegener — Sat, 22 Aug 2020 12:45:25 GMT

Hi Anonymous ,

I wrote a blog post about it.

https://community.powerbi.com/t5/Power-Platform-Integrations/Archive-Power-BI-Reports-with-Power-Automate/td-p/1207359