cancel
Showing results for
Search instead for
Did you mean:
Frequent Visitor

## convert week number into data

Hello,

I have a week number (1,2,...,52) and I have the year, I'm looking for a function that could transform\convert the week and the year into date.

for some reason I didn't manage to do that.

Tank you..:)

1 ACCEPTED SOLUTION

Accepted Solutions
MVP

## Re: convert week number into data

As I'm not aware of a function that returns the date of the week, instead I'd create a calendar for the year (replace "YourYear" respectively), create the week numbers and their first dates. Then filter on your week-numbers:

```let
Source = {Number.From(#date(YourYear,01,01))..Number.From(#date(YourYear,12,31))},
#"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"Column1", type date}}),
WeekColumn = Table.AddColumn(#"Changed Type", "Week", each Date.WeekOfYear([Column1])),
StartOfWeek = Table.AddColumn(WeekColumn, "StartOfWeek", each Date.StartOfWeek([Column1])),
#"Filtered Rows1" = Table.SelectRows(StartOfWeek, each [Week] >= List.Min(YourWeekList) and [Week] <= List.Min(YourWeekList))
in
#"Filtered Rows1"```

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Imke Feldmann

6 REPLIES 6
MVP

## Re: convert week number into data

Do you expect to create one date per week (if yes: which one? First, last?) or all days?

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Imke Feldmann

Frequent Visitor

## Re: convert week number into data

Yes, one data per week- the first day.

Thanks!

MVP

## Re: convert week number into data

As I'm not aware of a function that returns the date of the week, instead I'd create a calendar for the year (replace "YourYear" respectively), create the week numbers and their first dates. Then filter on your week-numbers:

```let
Source = {Number.From(#date(YourYear,01,01))..Number.From(#date(YourYear,12,31))},
#"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"Column1", type date}}),
WeekColumn = Table.AddColumn(#"Changed Type", "Week", each Date.WeekOfYear([Column1])),
StartOfWeek = Table.AddColumn(WeekColumn, "StartOfWeek", each Date.StartOfWeek([Column1])),
#"Filtered Rows1" = Table.SelectRows(StartOfWeek, each [Week] >= List.Min(YourWeekList) and [Week] <= List.Min(YourWeekList))
in
#"Filtered Rows1"```

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Imke Feldmann

Frequent Visitor

## Re: convert week number into data

great, it was vary helpful.

Frequent Visitor

## Re: convert week number into data

Another way is to insert a new new column (Power BI desktop >> New Measure >> New Column)

`ndate = DATE([year],1,-2)-WEEKDAY(DATE([year],1,3))+[week]*7`

This is based on the ISO week date, which means we need to find the Monday nearest to the 1st of January.

Super Contributor

## Re: convert week number into data

@wgarn ISO Week Number 1 is the week (Mo-Su) that contains the 4th of January.

For the correct rules check (the comments below) my video.

Edit: ah, you mean Monday closest to January 1st is the start of week 1?

That looks like another correct way of formulating ISO week 1.

Specializing in Power Query Formula Language (M)

## Helpful resources

Announcements

#### New Kudos Received Badges Coming

Kudos to you if you earned one of these! Check your inbox for a notification.

#### Microsoft Implementation for Communities Wins Award

Learn about the award-winning innovation that was implemented across Microsoft’s Business Applications Communities.

#### Power Platform World Tour

Find out where you can attend!

Top Solution Authors
Top Kudoed Authors (Last 30 Days)
Users online (1,812)