Week Number and ISO Week Number in Power Query

[GopVan]

I have added a week number column in Power Query and the output was as follows:

26/12/2022 - Week 53

...

31/12/2022 - Week 53

01/01/2023 - Week 1

02/01/2023 - Week 2

What is the logic behind this numbering? Why does Week 1 include only Jan 1, 2023?

How can I generate the ISO Week Number in Power Query (DAX) such that Jan 2, 2023, is Week 1?

[Anonymous]

Please look Function for ISO Year/Week number (ISO 8601)) 

NB! Pay attention to offsetindays parameter (by default it equals to zero - it means week starts on Sunday)

[o_belov]

@Mahesh0016 It works! Thanks a lot!

[GopVan · Accepted solution]

Thank You @Mahesh0016, found the solution here

[Mahesh0016]

let
Source = List.Dates(DateTime.Date(DateTime.LocalNow()), 365, #duration(1, 0, 0, 0)),
#"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Date"}}),
#"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Date", type date}}),
#"Weekday Number" = Table.AddColumn(#"Changed Type", "Weekday Number", each Date.DayOfWeek([Date], Day.Monday)+1),

#"ISO Week Number" = Table.AddColumn(#"Weekday Number", "ISO Week Number", each if
Number.RoundDown((Date.DayOfYear([Date])-(Date.DayOfWeek([Date], Day.Monday)+1)+10)/7)=0

then
Number.RoundDown((Date.DayOfYear(#date(Date.Year([Date])-1,12,31))-(Date.DayOfWeek(#date(Date.Year([Date])-1,12,31), Day.Monday)+1)+10)/7)

else if
(Number.RoundDown((Date.DayOfYear([Date])-(Date.DayOfWeek([Date], Day.Monday)+1)+10)/7)=53
and (Date.DayOfWeek(#date(Date.Year([Date]),12,31), Day.Monday)+1<4))

then
1

else
Number.RoundDown((Date.DayOfYear([Date])-(Date.DayOfWeek([Date], Day.Monday)+1)+10)/7))

in
#"ISO Week Number"


@GopVan Did I answer your question? Mark my post as a solution! Appreciate your Kudos !!