How to Create a Date Table from Scratch in Power BI

How to Create a Date Table from Scratch in Power BI

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.

Power Query

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))
List of Dates in Formula Bar

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

Converting List

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.

Date Value

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.

Add Column

6. With the Date Value column selected, select Year from the Date drop-down menu.

Date Column

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.

Date Drop-Down Menu
Want to Learn More?

Be sure to check out my other recent blog posts on Power BI:

Power BI SUM vs SUMX

Power BI Tooltips

How to Create a Power BI App

About Tessellation

We are a modern analytics consultancy. We enable and manage organizations’ analytics and self-service teams by educating people, optimizing technology, developing world-class products, and providing sustainable results. Curious to know how we can level up your organization’s analytics? Click here!

Interested in joining the Tessellation Team as a data analyst, Tableau expert, dashboard designer, or data scientist? We’re hiring! Check out our latest job listings on our website and on our Linkedin page!

Subscribe to Our 'Data Cap' Newsletter!

Want a monthly insights from the Tessellation Team with tips, tricks, and secrets to improve your analytics?

Sign up below and we'll deliver articles, events, and how to's straight to your inbox.
Comments are closed.