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

[Tom-P]

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.

[mwegener · 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.

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

[Tom-P]

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!

[mwegener]

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...

[Tom-P]

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

Thanks,

[v-xuding-msft]

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... 

[Tom-P]

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.

[cwebb]

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]

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.