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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors