5 Ways to Create a Date 📅 in Power Query

Creating and Interpreting Dates in Power Query

The video presents five ways to create or correctly interpret dates when the source data does not contain a usable date column.

1. Create a date from a month number and year

   • Add a Custom Column and use Power Query’s #date function.
   • Provide the year, month number, and day. If the source has no day, use 1 to represent the first day of the month.
   • The year, month, and day inputs must be numeric.
   • You can specify type date as the column type in the custom-column expression.

2. Create a date from a month name and year

   • Concatenate the month name with the year, then convert the result to a date.
   • If the year is numeric, first convert it to text with Text.From.
   • After joining the values, change the resulting column’s type to Date.

3. Create a date from a year alone

   • Choose a consistent date to represent the year, such as January 1 or December 31.
   • Use #date with the year and the chosen month and day. For example, January 1 uses month 1 and day 1.
   • Set the result’s type to Date.

4. Convert a YYYYMMDD value into a date

   • If year, month, and day appear together without separators, converting the column directly to a date may produce errors when Power Query has inferred inconsistent types.
   • First convert the entire column to Text, then convert it to Date.

5. Handle dates written in a different regional format

   • If the computer’s locale expects a different date order—for example, day-month-year instead of month-day-year—select Change Type → Using Locale. Choose the appropriate date type and locale, such as English (United States).
   • Mixed values, especially those containing timestamps, can still be misread. A timestamp may prevent the locale conversion from interpreting some values correctly.
   • One interface-based fix is to extract the text before the space to remove the timestamp, then apply the date type using the correct locale.
   • Alternatively, create a new column that:
     1. Checks the value’s type with Value.Type.
     2. Uses Date.From to remove the time if the value is a datetime; otherwise, keeps the existing value.
     3. Converts the result to text with Text.From.
     4. Parses it using Date.From with the US culture, "en-US".
     5. Sets the final column type to Date.
   • Once the new date column is correct, remove the original column if it is no longer needed.

Speakers and Sources

• Chandeep, presenting for Goodly.
• The video includes Power Query demonstrations and brief background music at the end.