(scottish) sql bob blog

rss

Some thoughts of your typical data shepard / data groomer / data dance teacher sort of person.


M (formally know as Power Query Formula Language), PowerBI and parameterising data sources - part two

In the previous post (see here) l set up the blank datasets as parameters storing the values of the database server and name.  Whilst attempting to create a suitable dataset to a report for an internal customer, l was frustrated that l could not do it using Power Query.  Yet on the other hand l know that l could write some TSQL that would give me the dataset l wanted very easily.  So l wondered if this was possible to combine the two?

So lets return to the SQL.Database function which has this very nice example
Sql.Database("localhost", "Northwind", [Query= select * from Customers]) The last setting is "Query as text - Provide a query text that is run on the server to return values". It's almost like someone thought it would be a good idea to allow a user to run a SQL query. A few minutes later l had the query l wanted to run and it looked something like this -:

DECLARE @IssueType AS VARCHAR(10) = 'Problem'; DECLARE @ClientName AS NVARCHAR(100) = 'RainbowPainters';
SELECT  DATEPART(MONTH, [Issue_Created]) AS Month_Number        ,DATENAME(MONTH, [Issue_Created]) AS Month_Name        ,DATENAME(YEAR, [Issue_Created]) AS Year_Name        ,COUNT(DATEPART(MONTH, [Issue_Created])) AS Total FROM    [dbo].[IssueData] AS ID WHERE   [Issue_type_name] = @IssueType         AND [ClientName] = @ClientName GROUP BY DATEPART(MONTH, [Issue_Created])        ,DATENAME(MONTH, [Issue_Created])        ,DATENAME(YEAR, [Issue_Created]);
Next when back to the dataset opened the 'Advanced Editor' and added the query, you can it in Figure 1 below

Figure 1 - Advanced Editor window with the new query in place

To make it a bit easier to read below is the actual text from the 'Advanced Editor' window above.  All that has been added is the text in red,and the query text which is in italics
let

Source = Sql.Database(DatabaseServer, DatabaseName
,[Query = "DECLARE @IssueType AS VARCHAR(10) = 'Problem'; DECLARE @ClientName AS NVARCHAR(100) = 'RainbowPainters'; SELECT  DATEPART(MONTH, [Issue_Created]) AS Month_Number        ,DATENAME(MONTH, [Issue_Created]) AS Month_Name        ,DATENAME(YEAR, [Issue_Created]) AS Year_Name        ,COUNT(DATEPART(MONTH, [Issue_Created])) AS Total FROM    [dbo].[IssueData] AS ID WHERE   [Issue_type_name] = @IssueType         AND [ClientName] = @ClientName GROUP BY DATEPART(MONTH, [Issue_Created])        ,DATENAME(MONTH, [Issue_Created])        ,DATENAME(YEAR, [Issue_Created]);" ]) in     Source

When l refreshed the dataset, the data appeared :-)  Job done?  No, as often happens one client gets something and then everybody wants why they have.  So l was asked if we could create the same set of reports for another customer.  There was two choices at this point, l could just change the hard code client name, or could l parameterise the client name.  Having heard this before l knew about passing parameters from excel to filter datasets see the following posts.

https://blog.oraylis.de/2013/05/using-dynamic-parameter-values-in-power-query-queries/
http://blog.crossjoin.co.uk/2015/11/24/using-parameter-tables-to-control-data-refresh-in-power-bi/

Having looked at the blog posts which declare and then used parameters this looked like a simple concept. Kudos to the Power BI team for that.  First step was to add additional dataset as a parameter.   So l now had three dataset as parameters, the latest one being 'ClientName', which stores the client name


Figure 2 - List of datasets containing parameters

So having looked at the blog posts above l realised that l just need to declare a query which would hold the query text and pass in the Client Name parameter to the query text.  Following some trial and error, l ended up with the query text below

let
pQuery =
"DECLARE @IssueType AS VARCHAR(10) = 'Problem';
DECLARE @ClientName AS NVARCHAR(100) = '" & ClientName & "';
SELECT  DATEPART(MONTH, [Issue_Created]) AS Month_Number
       ,DATENAME(MONTH, [Issue_Created]) AS Month_Name
       ,DATENAME(YEAR, [Issue_Created]) AS Year_Name
       ,COUNT(DATEPART(MONTH, [Issue_Created])) AS Total
FROM    [dbo].[IssueData] AS ID
WHERE   [Issue_type_name] = @IssueType
        AND [ClientName] = @ClientName
GROUP BY DATEPART(MONTH, [Issue_Created])
        ,DATENAME(MONTH, [Issue_Created])
        ,DATENAME(YEAR, [Issue_Created]);",
    Source = Sql.Database(DatabaseServer, DatabaseName,[Query=pQuery])

in

    Source


Figure 3 - showing the query text in the Advanced Editor Window

Much to my delight this syntax works as expected.  So l can now have a way that it is possible to specific parameters which can be passed to SQL server.  From reading about M (Power Query Formula Language) there's more to learn. For the moment l am happy with the new found knowledge, whilst l play with some of the other technologies offered by PowerBI.

The only downside is this only works in PowerBI Desktop :-(