能否使用PowerShell创建Power BI数据集？无需Power BI Desktop创建数据集及数据源

Absolutely! You can 100% create Power BI datasets and their linked data sources directly using PowerShell—no need to touch Power BI Desktop at all. Let’s break down exactly how to do this, using the Power BI REST API (since some actions aren’t fully covered by the PowerShell cmdlets yet, but we can call the API directly from PowerShell easily).

Prerequisites #

• First, make sure you have the Power BI Management module installed. This gives you cmdlets for authentication and workspace management.
• You’ll need appropriate permissions in your Power BI workspace: at minimum, Workspace Contributor (or higher) to create datasets and data sources.

Step 1: Set up the Power BI Module #

If you don’t have the module installed, run this in an elevated PowerShell window:

Install-Module -Name MicrosoftPowerBIMgmt -Scope CurrentUser -Force
Import-Module MicrosoftPowerBIMgmt

Step 2: Authenticate to the Power BI Service #

Connect to your Power BI account with this command. It’ll prompt you to log in via a browser window:

Connect-PowerBIServiceAccount

If you’re working with a government or sovereign cloud, add the -Environment parameter, e.g., -Environment USGov

Step 3: Get Your Target Workspace ID #

You’ll need the ID of the workspace where you want to create the dataset. Fetch it with:

Get-PowerBIWorkspace -Name "Your Workspace Name"

Note down the Id value from the output.

Step 4: Create a Data Source #

We’ll use the Power BI REST API to create a data source. Let’s use a SQL Server data source as an example. Define the request body, then call the API:

$workspaceId = "your-workspace-id-here"
$dataSourceBody = @"
{
  "name": "My SQL Data Source",
  "connectionString": "Server=your-sql-server;Database=your-database;",
  "datasourceType": "Sql",
  "credentialDetails": {
    "credentialType": "Basic",
    "credentials": "{\"username\":\"sql-username\",\"password\":\"sql-password\"}",
    "encryptedConnection": "Encrypted",
    "privacyLevel": "Organizational"
  }
}
"@

# Call the API to create the data source
$createdDataSource = Invoke-PowerBIRestMethod -Method Post -Url "groups/$workspaceId/datasources" -Body $dataSourceBody
$dataSourceId = ($createdDataSource | ConvertFrom-Json).id

Adjust the connectionString, datasourceType, and credentialDetails to match your actual data source (e.g., Azure SQL, Excel, etc.).

Step 5: Create the Dataset Linked to the Data Source #

Now we’ll create a dataset that references the data source we just made. Define the dataset schema (tables, columns) and link it to the data source ID:

$datasetBody = @"
{
  "name": "My Power BI Dataset",
  "defaultMode": "Import",
  "tables": [
    {
      "name": "Sales",
      "columns": [
        { "name": "SaleID", "dataType": "Int64" },
        { "name": "ProductName", "dataType": "String" },
        { "name": "SaleDate", "dataType": "DateTime" },
        { "name": "Amount", "dataType": "Decimal" }
      ]
    }
  ],
  "datasources": [
    {
      "datasourceId": "$dataSourceId",
      "connectionDetails": {}
    }
  ]
}
"@

# Create the dataset
Invoke-PowerBIRestMethod -Method Post -Url "groups/$workspaceId/datasets" -Body $datasetBody

After running this, check your Power BI workspace—you’ll see the new dataset and linked data source there!

Key Notes #

• If you’re using DirectQuery instead of Import, change defaultMode to DirectQuery in the dataset body.
• For other data source types (like Azure Blob Storage, SharePoint), adjust the datasourceType and connectionString accordingly.
• Always handle credentials securely—avoid hardcoding them in scripts. Use PowerShell secrets or environment variables instead.

内容的提问来源于stack exchange，提问作者Rajesh Kumar