Let’s talk about the most important dimension table in the Power BI model – the Date table. A Date table includes all the attributes that you may want to filter, slice, and dice your data with to get significant insights. With a Date table, a date field anywhere within your data model won’t be a problem.
If you have a date field inside your model, you will probably end up running some logic in that, such as how many orders or invoices were processed, what’s the amount of the materials, costs, revenue, and margin, etc. The results of these questions will only make sense if you place them somewhere in time, and that’s why you need a Date table.
When you design your model, always check for date fields. If you have any, always include a date dimension table. So, in this blog, I’m going to share with you some of the Enterprise DNA tutorials on how to create and use an extended Date table quickly and effectively in Power BI.
The tutorials that I want to highlight here have been created by two of our Enterprise DNA experts, Melissa and Brian. The first one is a step-by-step guide on how to use the M code that Melissa made for an extended Date table. Melissa does a lot of work around M codes, and this approach makes the entire process faster, and it’s going to yield better quality results.
The Power Query M code is key in completing your Date Table report. It shows you what you want to see inside your table, and it gives you the data for your model efficiently. You just need to get the code, set it up inside Power Query, and make the necessary changes according to what you need in your table.
Here’s the full video tutorial.
This is the updated version of this topic. If you want to check out the previous tutorial I did for this topic, here’s the link.
Next, I’ll show you how to set up dynamic start and end dates when using date tables. Melissa will walk you through the process in this next tutorial.
There are many functions you can use with Date tables, and I suggest that you look through each one of them. Start creating blank queries and using them to see what happens. This is the best way to find out how each query function can help you out in the scenarios you might face in the future.
Lastly, I want to show you how to sort any column in the extended Date table, including custom columns. This question has been raised a lot of times on the Enterprise DNA Forum, and so Brian came up with a comprehensive tutorial on how to do this easily in Power BI.
In this tutorial, Brian demonstrates a good strategy to do for difficult fields, which came from building a few custom functions. You’ll find this general set of tools helpful in your own report development. Watch the full video tutorial below.
The process of creating and using an extended Date table is not complex at all. All you need is a basic understanding of how M language is structured. If you have a source for the M code, it’s easy enough from there.
Key Take Away
The Power Query is an amazing tool that could give you valuable insights, especially when dates are involved. I hope you’ll find the tutorials I’ve shared in this post helpful for your own work and reports.
The techniques used are updated and the most efficient you can get among Power BI tutorials. Check out other blogs and videos about Power Query and time intelligence to see what other techniques you can apply.
All the best!
Sam
***** Related Links *****
Using The Offset Function In Extended Date Tables
Creating A Date Table In Power BI The Fastest Way Possible
M Function For Date Table – How To Add A Parameter In The Query Editor
***** Related Course Modules*****
Time Intelligence Calculations
Advanced Data Transformations & Modeling
***** Related Support Forum Posts *****
Creating a dynamic Start-/EndDate for the Date table
Extended Date Table (Power Query M Function)
New Cheat Sheet For Extended Date Table
For more on the extended date table please see here…
Be the first to comment