Post Import in Power BI REST API

[anonymous2]

Hi, I would like to try using the Post Import from this documentation, https://docs.microsoft.com/en-us/rest/api/power-bi/imports/post-import to import an excel file or JSON file to Power BI Service but I'm having trouble creating the request body. 

Below is my code:

Connect-PowerBIServiceAccount
$body = '{
"connectionType": "import",
"filePath" : "Test.xlsx",
}'

Invoke-PowerBIRestMethod -Url 'https://api.powerbi.com/v1.0/myorg/groups/{groupid}/imports?datasetDisplayName=Employee&nameConflict...' -Method Post -Body $body

What else should I add and what is wrong with my script?

Thank you.

@v-shex-msft @Burningsuit @Tutu_in_YYC  @GilbertQ @ibarrau @amitchandak @TomMartens @edhans @arvindsingh802  

[lbendlin]

The authheader contains the bearer token and the Content-type.  My understanding is that "connect" leaves the aexcel file on the SharePoint and creates a reference. Similar to Direct Query. And no, I wasn't successful yet. The only documentation I could find is from 2015 when the API was still in beta.

[anonymous2]

Hi @lbendlin, I used the access token and received this error.

Invoke-RestMethod : Specified value has invalid CRLF characters.
Parameter name: value
At C:\Users\LENOVO\Documents\powerbi.ps1:23 char:1
+ Invoke-RestMethod -Uri "https://api.powerbi.com/v1.0/myorg/groups/816 ...
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (:) [Invoke-RestMethod], ArgumentException
    + FullyQualifiedErrorId : System.ArgumentException,Microsoft.PowerShell.Commands.InvokeRestMethodCommand

[v-janeyg-msft]

Hi, @anonymous2 

I got the same error as yours in my tests, probably due to this Invoke-PowerBIRestMethod api is not stable. 

Best Regards,
Community Support Team _ Janey

[anonymous2]

@v-janeyg-msft Do you change it to anything else?

[lbendlin]

Invoke-PowerBIRestMethod  is syntax sugar.  You can use Invoke-RestMethod instead, which gives you better debugging options.

Invoke-RestMethod -Uri "https://api.powerbi.com/v1.0/myorg/groups/$group/imports?datasetDisplayName=Test" –Headers $authHeader -Method Post -Body $Payload

Looks like if you have an Excel file on a OneDrive you can only use "connect", not "import".  For files on the local computer both modes are possible

although the wording is ambiguous.

So - what are you ultimately trying to achieve - import or connect?

[v-janeyg-msft]

@lbendlin  Hello 

Have you tried successfully importing the OneDrive excel file into service? Can you show the complete code? 

[anonymous2]

@lbendlin, what's in the $authheader body? What's the difference between connect and import?

[v-janeyg-msft]

Hi, @anonymous2 

What error message did you get after following this step? If it is convenient, please provide a screenshot of your operation and error message(mosaic privacy).

Best Regards,
Community Support Team _ Janey

[anonymous2]

Hi @v-janeyg-msft, 

This is the error that I received. Is there something wrong with my request body or request header?

Thank you.

[v-janeyg-msft]

Hi, @anonymous2 

What software did you write it in?

[anonymous2]

Hi @v-janeyg-msft, I use Windows Powershell ISE

[v-janeyg-msft]

Hi, @anonymous2 

I don't really have experience with this, but can to try to set the file to be anonymous.

And I am not clear on the location of the request header.

Janey

[lbendlin]

Powershell.  Replace {groupid}  with the actual GUID for the workspace.

[anonymous2]

Hi @lbendlin, I use the actual GUID in the script. I put the {groudID} as my actual GUID for the workspace but still received errors. Do you have any other way to change it?

Thank you.

[lbendlin]

yes the body is also not specified correctly. Need to use the @ syntax.

[anonymous2]

@lbendlin, What is @ syntax? 

[lbendlin]

Here is a sample syntax that should work for Excel files.  For dataflow JSON the syntax is different.

Login-PowerBI
$Payload =@"
{ "ImportInfo" : { "filePath" : "$file" , "connectionType" : "import" }}
"@
$res=Invoke-PowerBIRestMethod -Url "groups/$group/imports?datasetDisplayName=Test" -Method Post -Body $Payload -ContentType 'application/json'

By the way, your link is for Post Import which only works for your "My Workspace" .  Your code however suggest that you want to work with a shared workspace - that's where you need to specify the Group GUID.

[anonymous2]

@lbendlin 

I would like to work in the shared workspace, that's why I put the group ID. I tried using your script but still received the same error.

[lbendlin]

Your Excel File needs to be on a Onedrive.

[anonymous2]

@lbendlin I copied and pasted the path on Onedrive but still receive the same error