Powershell add table to Power BI desktop file

[phillip_from_oz]

Hi,

I want to do the following:

1. Run a powershell script to get some Active Directory user information and format the data

    I want to use the data to create a table in a *.pbix file

    I know that I can get the powershell script to create say an intermediate Excel file

    Then read that Excel file inside Power BI Desktop

    I also know that you can get to Active Directory directly within Power BI Desktop "Get Source".

    Decoding User Account Control bit mask using Power BI Desktop fails in my AD environment after many hours

    [I've used some code I found in this forum for the decode]

2. Run a powerhshell script to run "refresh" and then run a report inside a *.pbix file

Is any of this possible?

Thanks for any help.

[Anonymous]

so ive a powershell script,i want to push the data in the script to powerbi desktop or service .help please

$PAT= ****
$base64AuthInfo= [System.Convert]::ToBase64String([System.Text.Encoding]::ASCII.GetBytes(":$($PAT)"))
$WorkItemQueryURL = "https://dev.azure.com/UWA-DEV/Global_Product_Strategy/FP Team/_apis/wit/wiql?api-version=6.0"
$teams=@('Reference Data I','Reference Data II','User Management')
$counter=0
$counter2=0
$team2=@('uvGO Technical Enhancements','Flight Planning','FIQ','Trips')
foreach ($i in $teams){
$body =@"
{
"query": "Select * From WorkItems Where [System.WorkItemType] in ('User Story','bug') and [System.State] != 'closed' and [System.State] != 'Removed' and [System.IterationPath] = @currentIteration('[Global_Product_Strategy]\\FP Team <id:233a2595-f8be-423e-a5a7-6132f319e008>') and [System.AreaPath] = 'Global_Product_Strategy\\$i'"
}
"@
$WorkItem = Invoke-RestMethod -Uri $WorkItemQueryURL -ContentType "application/json" -Body $body -Headers @{Authorization=("Basic {0}" -f $base64AuthInfo)} -Method POST

foreach ($id in $WorkItem.workItems.id) {
$counter++
}
write-host $counter $i
$counter=0
}

foreach ($j in $team2){
$body =@"
{
"query": "Select * From WorkItems Where [System.WorkItemType] in ('User Story','bug') and [System.State] != 'closed' and [System.State] != 'Removed' and [System.IterationPath] = @currentIteration('[Global_Product_Strategy]\\FP Team <id:233a2595-f8be-423e-a5a7-6132f319e008>') and [System.AreaPath] = 'Global_Product_Strategy\\uvGO\\$j'"
}
"@
$WorkItem = Invoke-RestMethod -Uri $WorkItemQueryURL -ContentType "application/json" -Body $body -Headers @{Authorization=("Basic {0}" -f $base64AuthInfo)} -Method POST
foreach ($id in $WorkItem.workItems.id) {
$counter2++

}
write-host $counter2 $j
$counter2=0
}

[lbendlin]

You cannot create new data sources inside a dataset programmatically. You can modify existing data sources, and you can replace entire datasets.

[phillip_from_oz]

Thanks.

Can you tell me how I can modify a data source?
An example would be great.
A web reference for data source modification is a great alternative

[lbendlin]

Depending on your scenario you need to use this call

Datasets - Update Datasources - REST API (Power BI Power BI REST APIs) | Microsoft Docs

or this one

Datasets - Update Datasources In Group - REST API (Power BI Power BI REST APIs) | Microsoft Docs

[phillip_from_oz]

Thanks.

Looks like this can only work for Power BI cloud version not the Power BI Desktop.
Right?

[lbendlin · Accepted solution]

Sort of. You can also use Tabular Editor or similar XMLA tools to do this both on the desktop (with running Power BI Desktop instance) and on the service. However this won't work from PowerShell.