Date Key Column Conversion

Update: The February 2018 release of Power BI Desktop introduced the Mark as Date Table feature. This feature, which had been exclusive to Analysis Services Tabular models, allows for relationships to be defined with YYYYMMDD whole number (integer) key columns yet still utilize Time Intelligence functions by defining a date column on the date dimension table. Therefore, the conversion of YYYYMMDD integer columns to date columns or the addition of date data type columns to source views of the Power BI model is now limited to edge cases.     

Date columns in source tables are often stored as integers in YYYYMMDD format. However, to take advantage of date intelligence features in Power BI such as relative date filtering it’s necessary to convert these columns into a date type. In short, we need the Order Date column on the right and it’s stored as the Order Date Key column on the left (integer):

SQL View

The first option would be to implement the conversion within the SQL view of the source system dedicated to the table. For this blog post, let’s assume this isn’t an option. In our scenario, we have to implement the conversion within Power BI Desktop (or within Excel or SSAS 2017 Tabular).

Native Query Conversion

If the source database is SQL Server, a T-SQL statement such as the following can be used within a Value.NativeQuery() M function:

let 
Source = AdWorksSQLServer, 
OrderDateQuery = Value.NativeQuery(Source, 
"Select 
CONVERT(date,CAST([Order Date Key] AS nvarchar(8)),112) as [Order Date] 
From BI.vFact_InternetSales")
in OrderDateQuery

Note: AdWorksSQLServer is just a data source staging query. Essentially, it’s a Sql.Database() function with query parameters passed to it’s server and database inputs. (See Data Source Staging Queries for more details)

If we go with this approach, we’ll want to embed any other necessary transformations into the SQL statement as well. Any operations after the native query (ie filters, new columns, etc) will use local resources such as your On-Premises Data Gateway server.

M Function Conversion

Maybe SQL isn’t an option given the data source (e.g. CSV files). In another scenario, maybe you’ve already applied several M functions to the source data and you prefer to keep the logic defined in M expressions.

An M query approach could create the date column within a Table.AddColumn() function as follows:

let
 Source = AdWorksSQLServer,
 ISales = Source{[Schema = "BI", Item = "vFact_InternetSales"]}[Data],
 DateKeyCol = Table.SelectColumns(ISales,{"Order Date Key"}),
TextConvert = 
Table.TransformColumnTypes(DateKeyCol, {{"Order Date Key", type text}}), 
DateCol = Table.AddColumn(TextConvert,"Order Date", each 
#date
 (
 Number.From(Text.Start([Order Date Key],4)), // Year
 Number.From(Text.Range([Order Date Key],4,2)), // Month
 Number.From(Text.End([Order Date Key],2)) // Day 
 ), type date)
in DateCol

1. The TextConvert variable simply modifies the source column type to text
2. The DateCol variable references the the text column from Step 1 to compute the three required parameters for the #date function (year, month, day) via three text functions.

*Note that the Text.Range() parameter of 4 refers to the 5th character of the string given the zero-based system.

By transforming the column data type first, this operation is passed back to the source system as a SQL statement. The final variable, however, as currently shown will not be folded back to the server – this is a partially folded M query. Specifically, a SQL statement will be generated for the TextConvert variable and the DateCol variable will operate against the results of this SQL query (with local resources).

For most dimension tables and even smaller fact tables this single local step should be sufficient. If necessary, however, you could redesign the query to be fully folded. The M code for this is a bit more complex so I’ll save this for a future blog post.

Wrapping Up

With the Order Date column now a date data type, you have access to many built-in features in Power BI and robust options for DAX measures. I personally like the relative date filtering option available to both slicers and off-canvas filters:

Note: Another blog post is coming later today featuring a highly customized DAX measure (ie variables, table-valued variables, set-based logic, and more).