How do I create date column from quarter and year?

[cathoms]

I am getting data from a folder. Each excel workbook in the folder has a column called Dataset that indicates the quarter and year the data are from in the format 1Q 2021, 2Q 2021, etc. I can easily create columns that separate these out to Quarter (Q1, Q2, etc.) and Year. and/or combine this to year-quarter, e.g. 2021 Q1, 2021 Q2, etc.

I need to be able to filter by time period and show the quarter over quarter difference. I can easily use the Dataset field to filter but not sure how I can use that to calculate quarter over quarter change.

So, my question is, is there a way to convert some version of this to a date column in power query, so I can add a date table in my data model and use time intelligence functions? 

[MFelix]

Hi @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))

[TomMartens · Accepted solution]

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  + 3

returns 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