As Power BI developers we can never have too many ways of getting a date table into our data model. That said, let’s explore yet another way to do just that.
How to Create a Date Table
In my experience, most people either query a date table directly from a database or get the data from a static excel file. If neither of these is an option for you, there’s a very easy way to build a date table in Power Query with all of the a la carte column options you could ever want. Follow the steps below to get started.
1. From the “Get data” drop-down menu, select “Blank Query”. This will open the Power Query Editor window where you will build the table.
2. Create a list of dates using the formula bar.
- Note that in the example we are building a two year table with 2022 as the starting point. This can easily be modified by adjusting the date value and the count in the formula. For example, if you only wanted 2020, the formula would be: = List.Dates(#date(2020,1,1),365,#duration(1,0,0,0))
3. Convert the List into a table by clicking the “To Table” button in the Convert menu on the ribbon. You will not make any selections r in the subsequent window, click “OK”.
4. Once the List has been converted to a Table, rename the column to “Date Value” and change the data type to a Date type using the data type button next to the column header.
For the rest of the table, we will be using the Add Column menu on the top and selecting different date values from the Date dropdown using the Date Value column we just created as a base.
5. Navigate to the Add Column section and view the Date drop-down menu.
6. With the Date Value column selected, select Year from the Date drop-down menu.
7. Create the rest of the table in the same fashion by selecting the desired columns from the Date drop-down menu with the Date Value column selected.
Want to learn more?
Be sure to check out my other recent blog posts on Power BI:
