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 TABELLAPROVA -RefreshType FULL -Server "tabular_prova"

So far everything is ok.
Now I would like to do some processing in powershell

I would need to perform the query in powershell that reads powerbi
" select max(period), id_key from TABELLAPROVA group by id_key " and be able to export the result to a txt file and then do some logic on it.

I didn't find any commands that could help me

Thank you for your support

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.

4 Replies

  • Icaro99's avatar
    Icaro99
    Regular Visitor
    1 year ago

    thanks for the support, you solved my problem

     

    regards

  • Sahir_Maharaj's avatar
    Sahir_Maharaj
    Super User
    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"

     

    • 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
        Super 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.