Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
aWalsh523
New Member

Using PowerShell to get PowerBI SQL connection strings

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:

 

Untitled.png

 

 

 

 

 

 

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!

1 ACCEPTED SOLUTION
v-shex-msft
Community Support
Community Support

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

View solution in original post

1 REPLY 1
v-shex-msft
Community Support
Community Support

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors