Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
naveen73
Helper III
Helper III

extract year from

Hi all, I have a question on Power Query. I have a column that has some times dates in the right format e.g. 01/25/2025 and some times in the format e.g. Q3-2004. Is there a way I can write a code that will give me the correct year of the date for every entry?

 

Thanks,

 

Nav

1 ACCEPTED SOLUTION

Hello there @naveen73 ! If I understood your situation correctly, I think this might give the result you are searching for:

 

= Table.AddColumn(#"Your previous step", "Year", each if Text.Start([ColumnName],1) = "Q" then Date.StartOfQuarter(Text.Start([ColumnName],2)) else Text.End(Text.From([ColumnName], Culture.Current),4))

 

 If you keep the column formatted as a Date it should work as well!

 

Hope this answer solves your problem! If you need any additional help please @ me in your reply.
If my reply provided you with a solution, pleased mark it as a solution ✔️ or give it a kudoe 👍
Thanks!

You can also check out my LinkedIn!

Best regards,
Gonçalo Geraldes

View solution in original post

10 REPLIES 10
naveen73
Helper III
Helper III

@goncalogeraldes thanks for your swift response.

The cells with a value of "Q1-2002" give "null" with your proposed solution. I tried your solution in the first instance but did not work.

@naveen73 is your data always in the format "QQ-YYYY"?

that is exactly the problem, sometimes it is and sometimes it is not.

@naveen73 are the year values always the last four?

yes

@naveen73 I will assume that it is a text column so try this then:

 

#"Added Custom" = Table.AddColumn(#"YourPreviousStep", "Year", each Text.End([ColumnName],4))

 

 Hope this answer solves your problem! If you need any additional help please @ me in your reply.
If my reply provided you with a solution, pleased mark it as a solution ✔️ or give it a kudoe 👍
Thanks!

You can also check out my LinkedIn!

Best regards,
Gonçalo Geraldes

Thanks for this solution. I meanwhile tried a different one:

  • convert to text
  • take the last four characters

 

However, as I got the answer i.e. the last four digits realized that is not the answer I was looking for. What I am looking for is actually more complex.

For the values that have Q1, Q2 etc. I want the first day of that quarter in that year. For example: Q3-2007 should be 1 Oct 2007

Will that be possible?

 

thanks,

Naveen

Hello there @naveen73 ! If I understood your situation correctly, I think this might give the result you are searching for:

 

= Table.AddColumn(#"Your previous step", "Year", each if Text.Start([ColumnName],1) = "Q" then Date.StartOfQuarter(Text.Start([ColumnName],2)) else Text.End(Text.From([ColumnName], Culture.Current),4))

 

 If you keep the column formatted as a Date it should work as well!

 

Hope this answer solves your problem! If you need any additional help please @ me in your reply.
If my reply provided you with a solution, pleased mark it as a solution ✔️ or give it a kudoe 👍
Thanks!

You can also check out my LinkedIn!

Best regards,
Gonçalo Geraldes

Thanks so much for your help, It is appreciated!!

goncalogeraldes
Super User
Super User

Hello there @naveen73 ! You have to select your date column in Power Query and use the "extract year" function as follows:

 

goncalogeraldes_0-1631892736784.png

 And select Year -> Year and it will create a column with the year for every date.

 

Hope this answer solves your problem! If you need any additional help please @ me in your reply.
If my reply provided you with a solution, pleased mark it as a solution ✔️ or give it a kudoe 👍
Thanks!

You can also check out my LinkedIn!

Best regards,
Gonçalo Geraldes

 

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors