cancel
Showing results for
Did you mean:

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.

Frequent Visitor

## Week Number and ISO Week Number in Power Query

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?

1 ACCEPTED SOLUTION
Frequent Visitor

Thank You @Mahesh0016, found the solution here

4 REPLIES 4
Anonymous
Not applicable

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

@Mahesh0016 It works! Thanks a lot!

Frequent Visitor

Thank You @Mahesh0016, found the solution here

Super User

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 !!

Announcements

#### Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

#### Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

#### Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors