Forum Discussion

Icaro99's avatar
Icaro99
Regular Visitor
1 year ago
Solved

Export data from powerbi via Powershell

Hi I'm new to using the potential of powerbi and powershell. I made a basic program that allows me to refresh tables using the command Invoke-ProcessTable -DatabaseName DBPROVA -TableName TABELLAP...
Need Help
  • Sahir_Maharaj's avatar
    Sahir_Maharaj
    1 year ago

    Hello Icaro99,

     

    Can you please try this PowerShell script to connect to the Power BI model, execute a query, and save the results to a .txt file:

    Import-Module SqlServer

$powerBIConnection = "localhost:<PORT>" # Replace <PORT> with the Power BI port found in Step 1
$databaseName = "Model" # Default Power BI model database

$query = @"
EVALUATE
SUMMARIZE(
    'TABELLAPROVA',
    'TABELLAPROVA'[id_key],
    "MaxPeriod", MAX('TABELLAPROVA'[period])
)
"@

$result = Invoke-ASCmd -Server $powerBIConnection -Database $databaseName -Query $query

$outputFilePath = "C:\Path\To\Output.txt"
$result.Tables[0].Rows | ForEach-Object {
    "$($_.id_key), $($_.MaxPeriod)" | Out-File -Append -FilePath $outputFilePath
}

Write-Host "Query results saved to $outputFilePath"

     

  • Sahir_Maharaj's avatar
    Sahir_Maharaj
    1 year ago

    Hi Icaro99,

     

    Thanks for your response.

     

    Yes, the -Server parameter is necessary in both commands because it tells the command where to connect to execute the operation.

     

    If you want to query data and refresh tables in the same workflow, you'll need to use both commands as follows:

    • Invoke-ProcessTable for refreshing data in tables.
    • Invoke-ASCmd for querying or executing DAX/MDX commands.

    Hope this helps.

Icaro99's avatar
Icaro99
Regular Visitor
1 year ago

Hi
thanks for the reply. I see that Invoke is always used but in your invoke i see also -Server 

$result = Invoke-ASCmd -Server $powerBIConnection -Database $databaseName -Query $query

while in the one I use to refresh a table I don't use it

Invoke-ProcessTable -DatabaseName DBPROVA -TableName TABELLAPROVA -RefreshType FULL -Server "tabular_prova"

 is it necessary?
Sorry I don't have my pc with me to test it but I had this doubt.

regards!

Sahir_Maharaj's avatar
Sahir_Maharaj
Icon for Super User rankSuper User
1 year ago

Hi Icaro99,

 

Thanks for your response.

 

Yes, the -Server parameter is necessary in both commands because it tells the command where to connect to execute the operation.

 

If you want to query data and refresh tables in the same workflow, you'll need to use both commands as follows:

  • Invoke-ProcessTable for refreshing data in tables.
  • Invoke-ASCmd for querying or executing DAX/MDX commands.

Hope this helps.