In another blog post, I covered ' How to create a Date Table using DAX ' which is one of the most common ways to create a date table in PowerBI. Another method that might be of interest is to create the table using Power Query. Anyone who has used Power Query will know that although it can achieve a similar output to DAX the process is very different. In this post, I'll show you how to create a relatively basic Date Table in Power Query that will contain a list of dates and 5 additional columns with the dates associated with the end of the month, quarter, week, year and fortnight. The process is to create a list of dates and then add custom columns for each of the additional columns that we want to include in the date table Although the most common way of creating a date table appears to be within DAX, I thought this would be an interesting exercise to see what is possible in Power Query (M). Date tables in DAX are expertly covered by the guys over at SQLBI in their article
Posts
If you've learnt Power BI organically and developed your skills using 'real world' applications you've probably learnt a couple of things the hard way. One thing I realised on my journey with PowerBI was that its very easy to create many measures and then end up with a model that’s difficult to manage and maintain. Initially, I would create measures and place them in the table that felt like the best place to store them, this worked for a while but inevitably didn’t quite work. I also tried putting a 'special' character at the start of the name, in my case a # symbol, to easily differentiate a measure from a column, this also didn’t quite work. After doing some research, I found the best solution to this issue was to create a Measures table and put all of my measures in this table. In this post, I'll show you how I created the measures table. There are a couple of methods to create a measures table, the most obvious one is to create a 'table' u
I recently blogged some instructions detailing the process to create a Date table using DAX in Power BI which is one way to create the foundation that enables the time intelligence functionality of PowerBI. If any of you have tried to use a date table you'll probably be aware of a couple of 'nuances' that can result in an incorrect result when you begin to visualise the data. I've described the 4 issues I've often had to rectify when working with time series data, hopefully they'll help you with similar issues I've incurred and found solutions to. Lets assume that you've created the date table and imported the sales data (more detailed explanation and link to files below). You then try and make a bar chart showing the number of sales by date and you end up with the following: I think most people who've used PowerBI have seen something similar, all of the columns are the same height and are of a magnitude that immediately feels incorrect. If you see t