Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello, I am pretty new to power query and I am trying to add a column that is filled as "01/MM/YYYY" for every row. Based on a check of the current date. I.e today is 20/01/2021 so the column would be filled with "01/01/2020" for all the rows.
I found the option to add a custom column however I dont know what to put in the code to give me what I want.
And next I would like to fill another column but considering the past year, using the same example, today being 20/01/2021 I want the column to be filled with "01/01/2020" so, subtracting a year from current date.
Any clues how to do so ?
Solved! Go to Solution.
You can use the the below function. Replace Date with DateTime or DateTimeZone depending on the column type.
Date.ToText([DateColumn], "01/MM/yyyy")
Hi @Anonymous
For the first colum:
= Date.StartOfYear(Date.From(DateTime.LocalNow()))
For the second:
Date.AddYears([Column1], -1)
Note it references Column1, the first column we've created
Please mark the question solved when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
@Anonymous
1st column: reads the current Date and time, converts it to date and extracts the first day of the year the date is in
2nd column simply takes the date from the previous column and subtracts one year
Please mark the question solved when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
Got it, thank you !
Hi @Anonymous
For the first colum:
= Date.StartOfYear(Date.From(DateTime.LocalNow()))
For the second:
Date.AddYears([Column1], -1)
Note it references Column1, the first column we've created
Please mark the question solved when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
That worked !, thank you. Can you help me understand what that fuction does ? the second column is pretty straight foward however I stil can´t grasp the first one.
You can use the the below function. Replace Date with DateTime or DateTimeZone depending on the column type.
Date.ToText([DateColumn], "01/MM/yyyy")
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
99 | |
50 | |
19 | |
12 | |
11 |