cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Chimsie
Helper II
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

                                                           
Thank you in advance!!

1 ACCEPTED SOLUTION
PC2790
Impactful Individual
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}})

Uploading the pbix file for your reference - here

View solution in original post

3 REPLIES 3
Chimsie
Helper II
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?

PC2790
Impactful Individual
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!

PC2790
Impactful Individual
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}})

Uploading the pbix file for your reference - here

View solution in original post

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

April Update

Check it Out!

Click here to read more about the April 2021 Updates!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

secondImage

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.