Some thoughts of your typical data shepherd / data plumber / data dance teacher sort of person.
Whilst working on developing some reports for our Service desk l was asked if we could just return a dataset containing the last 6 and 12 months of data. This is the type of request l would normally use a TSQL script to resolve. In this case one of the purposes of this project was to allow us to dog food both PowerBI and also self service BI more generally. The challenge was quite simple find a way to filter data in a dataset based on specific datetime. to this with a specific date-time using the UI is very simple. In Figure 1 below l will filter the column titled Issue_Created, which contains date-time values. Figure 1 - The field 'Issue_created' selected showing the filter icon From the list l can use this to filter by certain dates as shown below in Figure 2. This lead me to wonder if it was possible to filter by date ranges.
l Figure 2 - Filter by specific date
Below is a video showing how to filter records that either equal or occur after specific date, see Figure 3. Figure 3 - Creating a filter to show record on or after a specific date.
Once l had set up the filter l then opened the 'Advanced Editor' to look at the M Code. The code that does the filtering looks like this
#"Filtered Rows1" = Table.SelectRows(#"Filtered Rows", each [Created] >= #datetime(2015, 7, 7, 5, 16, 52))
The syntax looked relatively simple and straightforward, one of the main challenges is that in the snippet above the datetime is static not dynamic. At this point there was a tweak to the specification, my internal clients l have every faith in me :-). What they required was based on today's date, get the first day of the month and then return first day of the month 6 months and 12 month before. This is a pattern which l have done many times in TSQL, this was going to be a bit more of an adventure.
Long story short, after some experimenting l arrived at the following formulas = Date.AddMonths(Date.StartOfMonth(DateTime.LocalNow()),-6) First day of the month 6 months ago based on current date = Date.AddMonths(Date.StartOfMonth(DateTime.LocalNow()),-12) First day of the month 12 months ago based on current date. Whats the next step? Create a variable which will contain the DAX formulas that have created above. The first step to open PowerBI desktop and go to 'Edit Queries', this will open the 'Query Editor' window. In the 'Query Editor' window click on the 'New Source' button and select 'Blank Query' from the pull down list see Figure 4.
Figure 4 - Creating a new Blank Query to act as a parameter Now the blank query has been created the next step is to name the parameter, the name that l use in the example is Previous_FDM_6Months. Next l type in the DAX formula -> = Date.AddMonths(Date.StartOfMonth(DateTime.LocalNow()),-6). Once thats done, then click the green tick to update and evaluate the parameter.
Figure 5 - Setting the parameter name and inserting the DAX formula So now l have parameter which returns a datetime value based on current date and time. The last step was to amend the part of the M code l had grabbed earlier to filter the dataset. Below is the line from the M code l use in production let Source = Sql.Database("A_Cloud_Database","Some_Data"), dbo_Some_Data = Source{[Schema="dbo",Item="SomeRandomData"]}[Data], #"Filtered_Rows_Last12_Months" = Table.SelectRows(dbo_Some_Data, each [Issue_Created] >= Previous_FDM_12Months), ......... The data is extracted from the database and filter only return data where the Issue_Created is equal to or greater than the datetime value returned by the parameter Previous_FDM_12Months. A complete reference of all the datetime functions in power query lanague aka M can be found here https://msdn.microsoft.com/en-us/library/mt296608.aspx. If you want to download a PowerBI desktop file with the functions you can download it here.
So first l created a variable in the query window see figure 1.
Now you have a card with dynamic title, if you want to change the client name all you have to do is change the text in the variable. A sample PowerBI report file can be downloaded here which contains the report shown above.
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
letSource = 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 :-(