Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register 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
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
14 | |
11 | |
10 | |
10 | |
9 |
User | Count |
---|---|
18 | |
13 | |
12 | |
11 | |
8 |