Forum Discussion

Rsanjuan's avatar
Rsanjuan
Icon for Advocate III rankAdvocate III
9 years ago
Solved

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    ...
Need Help
  • Sean's avatar
    Sean
    9 years ago

    In the Query Editor

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

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

     

    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!

    MarcelBeugis there an easier way?

Sean's avatar
Sean
Icon for Community Champion rankCommunity Champion
9 years ago

In the Query Editor

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

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

 

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!

MarcelBeugis there an easier way?

  • MarcelBeug's avatar
    MarcelBeug
    Icon for Community Champion rankCommunity Champion
    9 years ago

    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"