Using PowerShell to get PowerBI SQL connection strings

[aWalsh523]

Looking for an automated way to identify all SQL sources for a PowerBI dataset. Would like a way in PowerShell to get a list of connections for a PowerBI Dataset, then for each connection get the SQL statement (query text found under the 'Advanced Options' in PBI desktop).  Shown here in red:

I've tried MicrosoftPowerBIMgmt Get-PowerBIDatasource function as well as the Power BI REST API for Get Datasources. These both include the 'server' and 'database' fields that are found on the connection setup screen, but do not include the actual query text.  

Alternately, if a connection does not include SQL query text, would there be a way to access the 'Navigate' step in the 'Applied Steps'?

From the SQL query text or text from the 'Navigate' step I can then parse the text as needed.

Any help or suggestions would be appreciated!

[Anonymous · Accepted solution]

Hi @aWalsh523,

AFAIk, current you can't directly get the advanced SQL statement from the data connector by rest API.
It should be more suitable to trace these on the database side or you can consider parameterized t-sql string with query parameters, then you can use the rest API to get query parameters to get detailed t-sql queries.

Using the Power BI Service Parameters to change connection strings (To possibly change between Dev 

let
    source=Sql.Database("server", "database",[Query=SQLParameter])
in 
    source

Datasets - Get Parameters - REST API (Power BI Power BI REST APIs) | Microsoft Docs

Regards,

Xiaoxin Sheng