Finding Minimum Date and Maximum Date Across All Tables in Power Query in Power BI and Excel

Finding Minimum Date and Maximum Date Across All Tables in Power Query in Power BI and Excel

When we talk about data analysis in Power BI, creating a Date table is inevitable. There are different methods to create a Date table either in DAX or in Power Query. In DAX you my use either CALENDAR() function or CALENDARAUTO() function to create the Date table. In Power Query you may use a combination of List.Dates()#date() and #duration() functions. Either way, there is one point that is always challenging and it is how to find out a proper date range, starting from a date in the past and ending with a date in the future, that covers all relevant dates within the data model. One simple answer is, we can ask the business. The SMEs know what the valid date range is..

While this is a correct argument it is not always the case. Especially with the Start Date which is a date in the past. In many cases the business says:

Lets’s have a look at the data to find out.

That is also a correct point, we can always a look at the data, find all columns with either Date or DateTime datatypes then sort the data in ascending or descending order to get the results. But what if there many of them? Then this process can be very time consuming.

Many of you may already thought that we can use CALENDARAUTO() in DAX and we are good to go. Well, that’s not quite right. In many cases there are some Date or DateTime columns that must not be considered in our Date dimension. Like Birth Date or Deceased Date. More on this later in this post.

In this post I share a piece of code I wrote for myself. I was in a situation to identify the Start Date and the End Date of the date dimension many times, so I thought it might help you as well.

How it works?

The Power Query expressions I share in this post starts with getting all existing queries using:

  • #sections intrinsic variable
  • Filtering out the current query name, which is GetMinMaxAllDates in my sample, to avoid getting the following error:

Expression.Error: A cyclic reference was encountered during evaluation.

Expression.Error: A cyclic reference was encountered during evaluation.
Continue reading “Finding Minimum Date and Maximum Date Across All Tables in Power Query in Power BI and Excel”

Quick Tips, Power BI Desktop, Query Parameters, Part 4, Passing Power Query Parameter Values to SQL Server Stored Procedures

I have written 3 blogposts about query parameters in the past.

This is the fourth one in the form of Quick Tips. Here is the scenario. One of my customers had a requirement to get data from a Stored Procedure from SQL Server. She required to pass the values from a Query Parameter back to SQL Server and get the results in Power BI.

The solution is somewhat easy. I created a simple stored procedure in AdventureWorksDW2019 as below:

CREATE PROCEDURE SP_Sales_by_Date 
	@date int
AS
BEGIN
	SET NOCOUNT ON;
	SELECT *
	FROM [dbo].[FactInternetSales]
	WHERE OrderDateKey >= @date
END
GO

In Power BI Desktop, get data from SQL Server, then:

  • Enter Server name
  • Enter Database name
  • Select Data Connectivity Mode
  • Expand the Advanced options
  • Type in a SQL statement to call the stored procedure like below:
exec SP_Sales_by_Date @date = 20140101
  • Click OK
Get Data From SQL Server using SQL Statements in Power BI Desktop
  • Click Transform Data
Transform Data in Power BI Desktop

Now we need to create a Query Parameter. In my sample I create a DateKey in Decimal Number data type:

Creating New Query Parameter in Power BI Desktop
Continue reading “Quick Tips, Power BI Desktop, Query Parameters, Part 4, Passing Power Query Parameter Values to SQL Server Stored Procedures”

Quick Tips: Time Dimension with Time Bands at Seconds Granularity in Power BI and SSAS Tabular

Time Dimension with Time Bands at Seconds Granularity in Power BI and SSAS Tabular

I wrote some other posts on this topic in the past, you can find them here and here. In the first post I explain how to create “Time” dimension with time bands at minutes granularity. Then one of my customers required the “Time” dimension at seconds granularity which encouraged me to write the second blogpost. In the second blogpost though I didn’t do time bands, so here I am, writing the third post which is a variation of the second post supporting time bands of 5 min, 15 min, 30 min, 45 min and 60 min while the grain of the “Time” dimension is down to second. in this quick post I jump directly to the point and show you how to generate the “Time” dimension in three different ways, using T-SQL in SQL Server, using Power Query (M) and DAX. Here it is then:

Time Dimension at Second Grain with Power Query (M) Supporting Time Bands:

Copy/paste the code below in Query Editor’s Advanced Editor to generate Time dimension in Power Query:

let
Source = Table.FromList({1..86400}, Splitter.SplitByNothing()),
    #"Renamed Columns" = Table.RenameColumns(Source,{{"Column1", "ID"}}),
    #"Time Column Added" = Table.AddColumn(#"Renamed Columns", "Time", each Time.From(#datetime(1970,1,1,0,0,0) + #duration(0,0,0,[ID]))),
    #"Hour Added" = Table.AddColumn(#"Time Column Added", "Hour", each Time.Hour([Time])),
    #"Minute Added" = Table.AddColumn(#"Hour Added", "Minute", each Time.Minute([Time])),
    #"5 Min Band Added" = Table.AddColumn(#"Minute Added", "5 Min Band", each Time.From(#datetime(1970,1,1,0,0,0) + #duration(0, 0, Number.RoundDown(Time.Minute([Time])/5) * 5, 0))  +  #duration(0, 0, 5, 0)),
    #"15 Min Band Added" = Table.AddColumn(#"5 Min Band Added", "15 Min Band", each Time.From(#datetime(1970,1,1,0,0,0) + #duration(0, 0, Number.RoundDown(Time.Minute([Time])/15) * 15, 0))  +  #duration(0, 0, 15, 0)),
    #"30 Min Band Added" = Table.AddColumn(#"15 Min Band Added", "30 Min Band", each Time.From(#datetime(1970,1,1,0,0,0) + #duration(0, 0, Number.RoundDown(Time.Minute([Time])/30) * 30, 0))  +  #duration(0, 0, 30, 0)),
    #"45 Min Band Added" = Table.AddColumn(#"30 Min Band Added", "45 Min Band", each Time.From(#datetime(1970,1,1,0,0,0) + #duration(0, 0, Number.RoundDown(Time.Minute([Time])/45) * 45, 0))  +  #duration(0, 0, 45, 0)),
    #"60 Min Band Added" = Table.AddColumn(#"45 Min Band Added", "60 Min Band", each Time.From(#datetime(1970,1,1,0,0,0) + #duration(0, 0, Number.RoundDown(Time.Minute([Time])/60) * 60, 0))  +  #duration(0, 0, 60, 0)),
    #"Removed Other Columns" = Table.SelectColumns(#"60 Min Band Added",{"Time", "Hour", "Minute", "5 Min Band", "15 Min Band", "30 Min Band", "45 Min Band", "60 Min Band"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Removed Other Columns",{{"Time", type time}, {"Hour", Int64.Type}, {"Minute", Int64.Type}, {"5 Min Band", type time}, {"15 Min Band", type time}, {"30 Min Band", type time}, {"45 Min Band", type time}, {"60 Min Band", type time}})
in
#"Changed Type"
Continue reading “Quick Tips: Time Dimension with Time Bands at Seconds Granularity in Power BI and SSAS Tabular”

Good Practices: Always Display the Reporting Environment

Good Practice: Always Display the Reporting Environment

When you work on real-world projects in power BI, you would probably have different environments Like DEV, UAT, Pre-Prod and Prod. It is important for you and your audience to know what the data is coming from. Am I looking at Dev or UAT data or I am actually looking at real data in Production environment. You may have asked or been asked with a question like “Where the data is coming from?”. It is important to know how trustworthy the data you’re analysing is. In this post I show you an easy way to show the environment your Power BI report is connected to.

How It Works

To display the environment name you use query parameters, then you reference that parameter, turn it to a table and add columns to show the environments accordingly. Easy right?

Read more about query parameters from a list output here.

Depending on your scenario the implementation might be slightly different, but the principals are the same. In this post I use a SQL server database. Therefore I need to Parameterise server name. in real world you may also need to parameterise the database name. Again, if your case is quite different, like if you get data from Excel, then the Excel path can be different for different environments. Let’s dig-in.

  • Open Query Editor
  • Click “Manage Parameters”
  • Click “New”
    • Enter “Name” and “description”
    • Tick “Required”
    • Select “Text” in “Type”
    • Select “List of values” in “Suggested Values” and type in server names for different environments
    • pick a “Default Value” and “Current Value”
    • Click OK
Creating new parameter in Power BI Query Editor

So far you created a new parameter that can be used to get data from a SQL Server data source.The next steps show you how to use that parameter to show the environments in your reports.

Continue reading “Good Practices: Always Display the Reporting Environment”