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
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