Forum Discussion
Create a dynamic Column Title name for an existing static column based on filter on date column
Timesheet data where each row is one week:
I have the following:
Col 1 Col 2 Col 3 Col 4 Col 5
StartDate Day 1 Comment 1 Day 2 Comment 2 (etc. through Day 7)
Day ? - Column is hours worked on that day
Comment ? - Note from employee about hrs worked on respective day
Visualization will be filtered by start date. Is there a way to either build a measure = Day 1 column and tell it to title = Date.Day (Now)? then build another measure = Day 2 column title = Date.Day + 1, etc.?
1 Reply
Chris_WhiteResolver II
Is it possible to shape your data in Power Query so it's daily rather than weekly? I don't think a dynamic Column Title name is possible, and I expect moving to a table which is just WeekStartDate/Day/Comment/DayDate will remove the need for this.
There's almost certainly better ways of doing this in M, but what I'm thinking of is you load the data in 7 times. First time keep StartDate/Day1/Comment1, add acolumn Called DayDate which is just equal to StartDate and rename Day1->Day & Comment1->Comment. Second time keep StartDate/Day2/Comment2, add acolumn Called DayDate which is just equal to StartDate + 1 day and rename Day2->Day & Comment2->Comment. Third time keep StartDate/Day3/Comment3, add acolumn Called DayDate which is just equal to StartDate + 2 days and rename Day3->Day & Comment3->Comment. Repeat for each day of the week. Append them all together.