March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now
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
Solved! Go to 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
@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.
that is exactly the problem, sometimes it is and sometimes it is not.
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:
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!!
Hello there @naveen73 ! You have to select your date column in Power Query and use the "extract year" function as follows:
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
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
User | Count |
---|---|
27 | |
17 | |
16 | |
12 | |
11 |
User | Count |
---|---|
35 | |
27 | |
26 | |
20 | |
15 |