cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Sokon Member
Member

Power BI REST API via Powershell: Create Datasource

I'm trying to automate gateway data source management using PowerShell. Sadly, the new MicrosoftPowerBIMgmt-Cmdlets don't provide commands for the Gateway-REST-APIs. Instead there's Invoke-PowerBIRestMethod that I managed to use for almost all of the Gatewaway-APIs.

 

However, the most important commands "Create Datasource" and "Update Datsource" resist my efforts. As seems to be the way of REST APIs, I don't get any useful error information, even when using Resolve-PowerBIError.

 

Here is my Powershell code that tries to create a Dummy Web DataSource without any credentials (should be easy, right?):

 

#Import-Module -name microsoftpowerbimgmt
Connect-PowerBIServiceAccount
######## Create Datasource ########
$gatewayId = "<your GatewayId goes here>"
$URL = "https://api.powerbi.com/v1.0/myorg/gateways/$gatewayId/datasources"
$Body = @{
    dataSourceType = "Web"
    datasourceName = "Dummy Web Connection"
    connectionDetails = "{`"path`":`"https://de.wikipedia.org/wiki/Liste_der_L%C3%A4nder_nach_Bruttoinlandsprodukt_pro_Kopf`"}"
    credentialDetails = @{
        credentialType = "Anonymous"
        credentials = "{`"credentialData`":`"`"}"
         encryptedConnection = "Encrypted"
        encryptionAlgorithm = "None"
        privacyLevel = "None"
    }
} | ConvertTo-Json
$Body
$response = Invoke-PowerBIRestMethod -Url $URL -Method POST -Body $Body

The $Body objects holds the following JSON which has the same format like shown in this example:

 

 

{
    "datasourceName":  "Dummy Web Connection",
    "credentialDetails":  {
                              "encryptedConnection":  "Encrypted",
                              "credentialType":  "Anonymous",
                              "credentials":  "{\"credentialData\":\"\"}",
                              "privacyLevel":  "None",
                              "encryptionAlgorithm":  "None"
                          },
    "dataSourceType":  "Web",
    "connectionDetails":  "{\"path\":\"https://de.wikipedia.org/wiki/Liste_der_L%C3%A4nder_nach_Bruttoinlandsprodukt_pro_Kopf\"}"
}

However, I always get a "Bad Request" answer.

 

Does anybody know how to fix my code to get the example running?

 

Any hint is greatly appreciated! Thanks!

 

 

10 REPLIES 10
Ronnie7 Regular Visitor
Regular Visitor

Re: Power BI REST API via Powershell: Create Datasource

Hi, i know this is an old post... did you ever get it resolved?

I'm facing the exact same issue and have been begging for help, but no success...

Sokon Member
Member

Re: Power BI REST API via Powershell: Create Datasource

@Ronnie7: This is actually a brand new post! Smiley Wink

Nothing changed since, apart from a new idea: If we knew the URL to the YAML file we could use Swagger ourselves to get a possibly updated documentation...

 

Ronnie7 Regular Visitor
Regular Visitor

Re: Power BI REST API via Powershell: Create Datasource

@Sokon i'm trying to create a new data source on a gateway via REST API  POST Method.

Here's the body of the request in JSON format:

 

$body2='{
"dataSourceType": "SQL",
"connectionDetails": "{\"server\":\"test2\",\"database\":\"MyDatabase\"}",
"datasourceName": "Sample Datasource",
"credentialDetails": {
"credentialType": "Basic",
"credentials": "{\"credentialData\":[{\"name\":\"username\", \"value\":\"Myuser\"},{\"name\":\"password\", \"value\":\"***\"}]}",
"encryptedConnection": "Encrypted",
"encryptionAlgorithm": "None",
"privacyLevel": "None"
}
}'


{
Invoke-PowerBIRestMethod -url "https://api.powerbi.com/v1.0/myorg/gateways/$GatewayID/datasources" -Method post -body $body2

 

I get error bad request 400... when i try to add this data source manually from the PowerBI administration GUI it's working.
If i'm using the GET method it's working as well and i can see all the data sources on that gateway... 

 

Any ideas?

Sokon Member
Member

Re: Power BI REST API via Powershell: Create Datasource

@Ronnie7 There are some things I learned from experimenting with the (now working) PS-Code for the "Add Datasource Users"-Command I can share:

  • Parameters seem to be case sensitive. "READ" is wrong "Read" worked. When you run the "Get Datasource" command, it will return "Sql" as dataSourceType and not "SQL". 
  • I can add Datasource Users only if the email addresses were valid. Fake or dummy addresses provoked an error. Perhaps that's the same with datasources. That's the reason why a tried to create a Web datasource with an anonymous authentication
  • Here is a similar thread where someone wants to use C# instead of PowerShell. Nevertheless, look at the part/discussion about the credentials-tag: It's some kind of encrypted string that needed some clarification. Perhaps you understand that better than me?
Ronnie7 Regular Visitor
Regular Visitor

Re: Power BI REST API via Powershell: Create Datasource

@Sokon thanks for the info. 

I have seen that thread that you mentioned, and I also struggle to understand it. Looks like we are missing something there...
I'll actually try to create a new web data source with anonymouts login and see if that will work. 

if so, i'll share the code. 

nd028509 Frequent Visitor
Frequent Visitor

Re: Power BI REST API via Powershell: Create Datasource

@Sokon @Ronnie7 did this work please share the code if yes i m facing similar issue

Sokon Member
Member

Re: Power BI REST API via Powershell: Create Datasource

No progress here, sorry. But I posted an idea where you can vote for the documentation to be updated!

nd028509 Frequent Visitor
Frequent Visitor

Re: Power BI REST API via Powershell: Create Datasource

{
"datasourceType": "WEB",
"connectionDetails": "{\"url\":\"http://app.powerbi.com\"}",
"datasourceName": "teststratumwebdatasource",
"credentialDetails": {
"credentialType": "Windows",
"credentials": "{\"credentialData\":[{\"name\":\"username\", \"value\":\"username\"},{\"name\":\"password\", \"value\":\"password\"}]}",
"encryptedConnection": "Encrypted",
"encryptionAlgorithm": "None",
"privacyLevel": "None"
}

 

I tried to create datasource for web thru rest api which failed. does anyone else has an working example in this group for creating datasource using powerbi rest api calls 

sm3 Frequent Visitor
Frequent Visitor

Re: Power BI REST API via Powershell: Create Datasource

BUMP

I've found that there are lack of specification of parameters in body in documentation
For examaple what should the body look like when we want setting new datasource of SQL in gateway?
Here are some threads without reply (last link is mine):

https://community.powerbi.com/t5/Developer/Problem-with-creating-datasource-under-gateway-through-AP...
https://community.powerbi.com/t5/Developer/PowerBI-REST-API-error-Need-help/td-p/509452

https://community.powerbi.com/t5/Developer/Creating-new-SQL-Datasource-for-Gateway-with-Powershell/t...

If any of you know the answer please share correct examples.
MS please make up your documentation: https://docs.microsoft.com/en-us/rest/api/power-bi/gateways/createdatasource.

Greetings



Helpful resources

Announcements
Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 136 members 1,574 guests
Please welcome our newest community members: