Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code FABINSIDER for a $400 discount.
Register nowThe 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.
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.
Solved! Go to Solution.
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?
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?
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"
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Check out the February 2025 Power BI update to learn about new features.
If you love stickers, then you will definitely want to check out our Community Sticker Challenge!
User | Count |
---|---|
84 | |
69 | |
68 | |
39 | |
37 |