Power BI Desktop June 2016 Update – Part 1 – Functions

I created a post a while back on invoking functions to specify parameters for data queries (Link Here). This was happily working using a single function to update multiple queries… until what seems to be the May/June Update. This introduced inline functions and parameters to the BI desktop, which caused me a bit of pain on some of my reports as it switched the way functions were used.

The Problem

So in previous versions when invoking a function, I could use the below code to create a dynamic  XML table, allowing me to use this table as a parameter reference in my other SQL queries.

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

In the above example, this would have invoked the configuration function and allowed me to reference a field by using the below:

SCCMServer = Configuration{0}[Server],
SCCMDatabase = Configuration{0}[Database],

Since the Power BI desktop update this has changed as it no longer updates its original table but creates a new executed query called “Invoked Function#”.

When attempting to create a function off my original query via the right click options,  I’d also get the below.

The Quick Fix

To quickly fix the issue, I initially wrapped the function around one of the SQL queries, added the variables in and then invoked it.

let SQLSource = (SCCMSQLServer as text, SCCMDatabase as text) =>
    SCCMServer = SCCMSQLServer,
    SCCMDatabase = SCCMDatabase,

<Rest of Code>

This returned my data but again…in a new query. This wouldn’t have matted so much if I was using one query but I had multiple referencing my original table.

The Solution

To properly resolve this issue, we can utilise the parameter feature to create the references required for executing the SQL queries. In part 2, I’ll go through how we use parameters in the Power BI desktop and how we update existing queries to variables instead.

Leave a Reply

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