Mixed table with dates and quarter year convert to date?

[Chimsie]

I have as input a mixed table with dates and quarter year. Is there a way to convert the quarter year so it returns the last date of the quarter?

Project Name	Plan date	Value	Project Reliability Score		Result
Project A	2-03-21	-2	A - Confirmed by source		2-03-21
Project B	4-03-21	-4.5	A - Confirmed by source		4-03-21
Project C	5-03-21	-4.5	B - Draft		5-03-21
Project D	7-03-21	9	B - Draft		7-03-21
Project E	8-03-21	-16.1	C - Undetermined		8-03-21
Project F	Q1-2021	-12	B - Draft		31-03-21
Project G	Q2- 2021	-1.5	B - Draft		30-06-21
Project H	Q3-2021	-12.5	B - Draft		30-09-21
Project I	Q4-2021	-9	A - Confirmed by source		31-12-21
Project J	1-12-21	20	B - Draft		01-12-20

And is it possible to divide the quarter year and Value by three (the months) and returns last date of the months in that quarter:                                                                    

		Input
Project Name	Plan date	Value	Project Reliability Score		Result
Project F	Q1-2021	-12	B - Draft		31-03-21
Project E	8-03-21	-16.1	C - Undetermined		8-03-21
		Output
Project F	Q1-2021	-4	B - Draft		31-1-2021
Project F	Q1-2021	-4	B - Draft		28-2-2021
Project F	Q1-2021	-4	B - Draft		31-03-21
Project E	8-03-21	-16.1	C - Undetermined		8-03-21

                                                           
Thank you in advance!!

[Chimsie]

@PC2790 

Thank You, this works fine for me.

Do you have any idea if dividing the quarter year and Value by three is possible?

[PC2790]

Glad that it worked for you.

Still exploring on the second one. You can post it as a new requirement and any one of the Power BI champions will definately help you.

All the best!

[PC2790 · Accepted solution]

Hello @Chimsie ,

I was able to address your first requirement by taking the following steps:

1) Generating a continuous date table having QuarterYear(in the same format as yours) and EndOfQuarter.

2) Create a new fucntion to perform as a Look up to get value of EndOfQuarter in your table.

3) Used that function to transform based on condition as below:

= Table.TransformColumns(#"Replaced Value",{{"Plan Date", each if Text.StartsWith(_,"Q") then 
    LookUpFunction(_, #"Date Table", "QuarterInCalendar", "QuarterEnding") else _, Int64.Type}})

Uploading the pbix file for your reference - here