Creating a column in Power Query that converts a date column to the first day of the month or the last day of the month is easy to do with Power Query Date formulas or using the AI powered Column from Example function to automatically generate Power Query M formulas for you.
Table of Contents
Convert Dates to Last Day of the Month with Power Query
To convert a date to the last day of the month in Power Query by launching the Power Query Editor, add a New Custom Column and use the Date.EndOfMonth([Date]) formula pointed at the date column you wish to convert. The results will be a new column that shows the last day of the month.
Here’s how in more detail:
Step 1.) Add a New Custom Column
From the Power Query Editor, select Custom Column from the Add Column section of the Power Query Ribbon. This allows you to enter formulas in the Power Query M language to generate new columns.
Step 2.) Enter the End of Month Date Formula
Name the New Column, and use the Power Query Formula Date.EndOfMonth([Date[) where [Date] is the name of the field being convert from a date to the last day of the month.
Step 3.) Accept the New Column
Press OK and a new Column will be generated at the far right of your dataset that shows the last day of the month.
The result is a new column we created called EOM to represent End of Month. It converts the Date Column to the last day of the same month.
Step 4.) Convert the Data Type to Date
Each column in Power Query is assigned a data type that dictates how values in those columns are handled. For our new end of month column, right click on the column header and convert the column to a Date Data Type.
Converting Dates to First of the Month in Power BI
To convert a date to the First of the Month using Power Query in Power BI, Add a New Custom Column, and apply the formula Date.StartOfMonth([Date]) where Date is the name of the column being converted. Power Query will automatically calculate the first day of the month.
Converting Dates using AI in Power Query
A low code alternative in Power BI is to create a Custom Column from Example. Power BI uses AI to detect patterns in a column that you provide examples for. In Power Query, click Add Custom Column from Example. The following interface appears where a blank column is created on the right side of the screen. Begin typing in examples of the desired output.
Power BI will suggest a Power Query M formula at the top left and a preview of common requests and results.
When you select one and click OK the remaining column will be filled in with the formula. It’s a fast way to generate formulas that are commonly used without having to remember the syntax.