Forum Discussion
week + year in seperate columns to date
Hi all!
My dataset, which cannot be changed has two seperate columns to describe date; one [week] and one [year]. As the dataset spans over several years this naturally causes quite a bit of issues when trying to filter the data.
Is there any way to use power query to make the two columns into a new one which power bi will recognize as a validdate?
Making date into the first day of week would be perfectly acceptable.
Br
Frivik
Add custom column Date with formula
=Date.StartOfWeek(Date.AddDays(#date([year],1,1),([week]-1)*7),Day.Monday)
#date([year],1,1) returns January 1st of the [year],
then you add 7 days for each week using Date.AddDays
then you get first day of week using Date.StartOfWeek
2 Replies
avatorlImpactful Individual
Add custom column Date with formula
=Date.StartOfWeek(Date.AddDays(#date([year],1,1),([week]-1)*7),Day.Monday)
#date([year],1,1) returns January 1st of the [year],
then you add 7 days for each week using Date.AddDays
then you get first day of week using Date.StartOfWeekAnd you sir are a genious. Thanks for helping out a newbie!
Enjoy your weekend.
Best regards
