Power BI – Invoke Parameter Functions to Stored Data Table

So I’ve been working on a pet project relating to SCCM and querying the SCCM SQL database for multiple queries in Power BI. I needed a way to insert the SQL Server and Database without having it permanently stored in the pbix file so used the below solution using XML tables.

Building the Stored XML Table.

Within Power BI create a new blank query and paste the below. You’ll see the Xml.tables is essentially just an XML table but inserting the parameters into it.

let SQLSource = (SCCMSQLServer as text, SCCMDatabase as text) => 
let
Source = Xml.Tables("" & SCCMSQLServer & "" & SCCMDatabase & ""),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Server", type text}, {"Database", type text}})
in
#"Changed Type"
in
SQLSource

When completed, you will see that you have an option to invoke the function.

Invoke the function and your prompted with funky box to enter your parameters.


When your done, you’ll have a stored data table which you can use as a reference for other queries!

Leave a Reply

Your email address will not be published. Required fields are marked *