5 Ways to Create a Date in Power Query

In this blog post, I’ll share five different examples of creating dates in Power Query especially when you don’t get a built-in date column with your data.

Some are easy and the rest are ridiculously easy!

 

5 Ways to Create a Date 📅 in Power Query – Video

https://youtu.be/Ys6p3eMAPm4

 

Example 1 – Create Dates with Month Numbers and Year

Consider this data where I have a Month Number and a Year Column but don’t have an explicit Date column. To create a Date let’s start with creating a new custom column.

I am going to use a #date() function.

• #date (Year, Month, Date) – Has 3 inputs
• Note that all the 3 inputs need to be in numeric format.

= #date([Year],[Month No],1)

As an output, I get the Date column. As a next, this column’s data type can be set to date.

 

Example 2 – Create Dates with Month Name and Year

In this data, we have a Month Name and a Year Column. I cannot use the #date() function since I have the month name and not a number.

Create a custom column and concatenate the Month and Year Column. Since the Year is in a number format before concatenation, I need to convert that into a text value.

= [Month No] & Text.From ( [Year] )

As an output, I get the Date column working fine and I have changed the type of the column to date.

 

Example 3 – Create Dates with Year

In this data, we only have the Year column. Let us add a new column by concatenating the Year, Month, and Date. I am assuming the dates will be at the start of the year, so the month and date will be = 1.

Create a custom column by following M-Code,

= #date([Year], 1, 1)

As an output, I will get the Date Column (again changed the type to date)

 

Example 4 – Create Dates with YYYYMMDD Format

Consider this data.

We have entire the dates concatenated in yyyymmdd format in a single string without any delimiters. If I change the data type to a Date I get correct Dates but with a few errors.

Now to rectify these errors, simply change the concatenated string to a text first and then apply the date data type. As an output, I get the dates working fine without any hassles.

 

Example 5 – Change Date format from MM-DD to DD-MM

In this example, consider the following dates but in US format (mm-dd-yyyy)

However, the problem is that my computer reads the date in Indian Format (dd-mm-yyyy). This will generate an error and will not create a valid date.

Steps to solve this problem

1. I am going to strip any timestamps from the date column (see row 4 shows an unwanted time stamp along with the date)
2. Make sure that all the dates are read in the US format

Consider a single M-Code (as a new column) that gets the job done.

= Date.From(
    if Value.Type([Date]) = DateTime.Type 
    then Text.From(Date.From([Date])) 
    else [Date], 
    'en-US'
)

1. It first checks if there is a timestamp and removes it.
2. Then converts all the dates into a US format.

 

 

More on Power Query

1. Duplicate Rows in Power Query
2. IFERROR in Power Query – Try and Otherwise
3. Promote Double Headers in Power Query
4. 5 Tricks to Reduce Steps in Power Query
5. Quick VLOOKUP in Power Query