Power Query Custom Column syntax help

Greeting Excel Pro's

I have a very large sheet that I've formulated a way to get the fiscal year and quarter for certain dates on that sheet. I do this for metric tracking purposes and its nice to be able to easily produce pivot charts that sorts to my fiscal quarters. (My fiscal year starts in April, we are currently in Q3'20)

I track multiple dates this way, and have ended up needing multiple columns that are just taking up major real estate in my end user spreadsheet. Hiding them isn't a great solution for me either, and people copy and paste rows of data other places.

I use power query on this data source to extract the data to another book, that I use for metric tracking. I never look at the extracted data, and could care less if there is the additional column there, thus enter custom columns. I'm very new to the power query syntax and not sure if its even possible to add the formulas that I have. Can anyone help?

The two formulas I would need in power query syntax are as follows... (i've also attached a sample to see how they work in regular excel)

=IF([@[START DATE]]<>"",YEAR([@[START DATE]])+IF(MONTH([@[START DATE]])>3,1,0),"")

=IF([@[START DATE]]<>"","Q"&(CHOOSE(MONTH([@[START DATE]]),4,4,4,1,1,1,2,2,2,3,3,3)&"'"&(RIGHT([@[Start Year]],2))),"")

There is 7 different dates I track in this way for fiscal reporting purposes and with 2 columns needed for each just for my fiscal numbers, that is 14 columns I can remove from my end user data source and hide in my power query/metric tracker.

Hope this is clear... and please and thank you for any help you could provide!

Originally Posted by CK76

Alternate approach. Rather than numeric functions, using Date functions.

Please Login or Register  to view this content.

For sorting... you either need to add sorting column or sorting prefix. But in general I'd recommend YY'Q# format for sorting purpose.

I took your advice and swapped to YY'Q#. I was able to swap around your provided code and get it implemented. That will give me the sort order I desire, and cut down on the number of needed columns.

Thank you both for your time... you two are rockstars in my eyes!