Forum Discussion
extract year from
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
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
Thanks so much for your help, It is appreciated!!
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