cancel
Showing results for
Did you mean:
Helper II

## Mixed table with dates and quarter year convert to date?

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

1 ACCEPTED SOLUTION
Impactful Individual

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}})``````

3 REPLIES 3
Helper II

@PC2790

Thank You, this works fine for me.

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

Impactful Individual

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!

Impactful Individual

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}})``````

Announcements