Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
Rsanjuan
Advocate III
Advocate III

Creating Individual Dates based on Week of field

 

Is there a way to create individual dates if you have only a week of (based on Sunday) field and names of days?

 

 For example, I have the following:

 

Week of          Monday      Tuesday    Wednesday    Thursday     Friday     Saturday

 

8/14/16               8                                        8                 8                              4

 

4/30/17                                   8                    8

 

There are no dates associated with the days.  Under each of those days are number of hours and would like to add the hours based on not the week of field but on individual dates using a time slicer.

 

1 ACCEPTED SOLUTION
Sean
Community Champion
Community Champion

In the Query Editor

1) Select Week Of  column - Transform tab - Unpivot Other Columns

2) Add Column Tab - Add Conditional Column - and name Number

 Add Conditional Column.png

3) right-click Number column and change type to whole number

4) Add Column Tab - Add Custom Column - and name it Date

= Date.AddDays([Week Of], -[Number])

That should do it!

QE - Convert End of Week to Date.png

@MarcelBeugis there an easier way?

View solution in original post

2 REPLIES 2
Sean
Community Champion
Community Champion

In the Query Editor

1) Select Week Of  column - Transform tab - Unpivot Other Columns

2) Add Column Tab - Add Conditional Column - and name Number

 Add Conditional Column.png

3) right-click Number column and change type to whole number

4) Add Column Tab - Add Custom Column - and name it Date

= Date.AddDays([Week Of], -[Number])

That should do it!

QE - Convert End of Week to Date.png

@MarcelBeugis there an easier way?

MarcelBeug
Community Champion
Community Champion

This would be my solution:

 

let
    Source = Input,
    DayNames = List.Transform({0..6}, each Date.DayOfWeekName(Date.AddDays(Source{0}[Week of],_),"en-US")),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Week of"}, "Attribute", "Value"),
    #"Added Custom" = Table.AddColumn(#"Unpivoted Other Columns", "Date", each Date.AddDays([Week of],List.PositionOf(DayNames,[Attribute]))),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Date", "Value"})
in
    #"Removed Other Columns"
Specializing in Power Query Formula Language (M)

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

FebPBI_Carousel

Power BI Monthly Update - February 2025

Check out the February 2025 Power BI update to learn about new features.

Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Feb2025 NL Carousel

Fabric Community Update - February 2025

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