cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Rsanjuan
Helper IV
Helper IV

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?

View solution in original post

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
MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Top Solution Authors
Top Kudoed Authors