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.
I'm looking to convert isoYearIsoWeek into a readable date format in PowerBI. When I try to change the data type from text to date, I get an error.
Can anyone provide some help on how to convert the isoYearIsoWeek date format into a year and week? Basically, there are two values - the first is the year followed by the week number (YYYYWW)
Here is what I have:
isoYearIsoWeek
201641
201642
201643
201644
Thank you,
Brian
Solved! Go to Solution.
As a week has 7 days - and all ISO weeks have 7 days, also the weeks around New Year - the following query will take you step by step to the start and the end of the week.
This approach is based on the fact that January 4th is always in ISO week number 1.
let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], Typed = Table.TransformColumnTypes(Source,{{"isoYearIsoWeek", Int64.Type}}), AddedJan_4th = Table.AddColumn(Typed, "Jan_4th", each #date(Number.IntegerDivide([isoYearIsoWeek],100),1,4), type date), AddedWeekday_Jan_4th = Table.AddColumn(AddedJan_4th, "Weekday_Jan_4th", each Date.DayOfWeek([Jan_4th],Day.Monday)), AddedStartOfWeek1 = Table.AddColumn(AddedWeekday_Jan_4th, "StartOfWeek1", each Date.AddDays([Jan_4th],-[Weekday_Jan_4th]), type date), AddedStartOfISOWeek = Table.AddColumn(AddedStartOfWeek1, "StartOfISOWeek", each Date.AddWeeks([StartOfWeek1],Number.Mod([isoYearIsoWeek],100)-1), type date), AddedEndOfISOWeek = Table.AddColumn(AddedStartOfISOWeek, "EndOfWeek", each Date.AddDays([StartOfISOWeek],6), type date), RemovedColumns = Table.RemoveColumns(AddedEndOfISOWeek,{"Jan_4th", "StartOfWeek1", "Weekday_Jan_4th"}) in RemovedColumns
Hi, I am am looking to convert the following WWYY format to a date. I stumbled upon this post and tried to utilize to code provided. But I am having trouble with the modification:
AddedJan_4th = Table.AddColumn(#"Renamed Columns2", "Jan_4th", each #date(Number.IntegerDivide([WeekYear],100),1,4), type date),
AddedWeekday_Jan_4th = Table.AddColumn(AddedJan_4th, "Weekday_Jan_4th", each Date.DayOfWeek([Jan_4th],Day.Monday)),
AddedStartOfWeek1 = Table.AddColumn(AddedWeekday_Jan_4th, "StartOfWeek1", each Date.AddDays([Jan_4th],-[Weekday_Jan_4th]), type date),
AddedStartOfISOWeek = Table.AddColumn(AddedStartOfWeek1, "StartOfISOWeek", each Date.AddWeeks([StartOfWeek1],Number.Mod([WeakYear],100)-1), type date),
AddedEndOfISOWeek = Table.AddColumn(AddedStartOfISOWeek, "EndOfWeek", each Date.AddDays([StartOfISOWeek],4), type date)
in
AddedEndOfISOWeek
I realized that the mod calclation drops the year value that I need when dividing by 100. Can you help with the modifcation of the code above? or with a different solution. Thanks in advance!
As a week has 7 days - and all ISO weeks have 7 days, also the weeks around New Year - the following query will take you step by step to the start and the end of the week.
This approach is based on the fact that January 4th is always in ISO week number 1.
let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], Typed = Table.TransformColumnTypes(Source,{{"isoYearIsoWeek", Int64.Type}}), AddedJan_4th = Table.AddColumn(Typed, "Jan_4th", each #date(Number.IntegerDivide([isoYearIsoWeek],100),1,4), type date), AddedWeekday_Jan_4th = Table.AddColumn(AddedJan_4th, "Weekday_Jan_4th", each Date.DayOfWeek([Jan_4th],Day.Monday)), AddedStartOfWeek1 = Table.AddColumn(AddedWeekday_Jan_4th, "StartOfWeek1", each Date.AddDays([Jan_4th],-[Weekday_Jan_4th]), type date), AddedStartOfISOWeek = Table.AddColumn(AddedStartOfWeek1, "StartOfISOWeek", each Date.AddWeeks([StartOfWeek1],Number.Mod([isoYearIsoWeek],100)-1), type date), AddedEndOfISOWeek = Table.AddColumn(AddedStartOfISOWeek, "EndOfWeek", each Date.AddDays([StartOfISOWeek],6), type date), RemovedColumns = Table.RemoveColumns(AddedEndOfISOWeek,{"Jan_4th", "StartOfWeek1", "Weekday_Jan_4th"}) in RemovedColumns
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 |
---|---|
114 | |
97 | |
85 | |
70 | |
61 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |