Forum Discussion
Responsive ResidentHow do I create date column from quarter and year?
Hey cathoms ,
in Power Query you can create a custom function and use the function. #date( year, month, day) to create a date. You have to be aware that all parameters have to be numeric, meaning you have to be wrap the Power Query function Number.FromText( ... ) around each non-numeric parameter https://docs.microsoft.com/en-us/powerquery-m/number-fromtext
Use 1 as the day parameter.
The numeric parameter for the month can be derived by the quarter number (the numeric part) and some math. The formula below
= ( Quarternumber - 1 ) * 3 + 3returns the numeric value that represents the last month of a quarter
1 --> 3
2 --> 6
Until now #date( ... ) will always be a date like 1st of September Year, if the quarternumber 3 is extracted from the column value quarter.
Finally you can put #date(...) into the function Date.EndOfMonth( ... ) and you have a date like 31st of September.
Hopefully, this will help to tackle your challenge. If not prepare a pbix file that contains sample data, upload the file to to onedrive or dropbox and share the link. If you are using Excel to create the sample data, share the xlsx as well. As we are talking about using Power Query to create the date column it's necessary that we can access the source data as well. For this the xlsx or using the input method to enter sample data.
Regards,
Tom
Super UserHi cathoms ,
Looking at the data you have you can create the following colum:
#date ( Number.FromText ([Year]),
if Text.End([Quarter],1) = "1" then 1 else
if Text.End([Quarter],1) = "2" then 4 else
if Text.End([Quarter],1) = "3" then 7 else
10
,1)
Then format has date:
If you want to get the end of the quarter wrap the column into Date.EndofQuarter:
Date.EndOfQuarter(
#date ( Number.FromText ([Year]),
if Text.End([Quarter],1) = "1" then 1 else
if Text.End([Quarter],1) = "2" then 4 else
if Text.End([Quarter],1) = "3" then 7 else
10
,1))