Forum Discussion
Help - display previous week from a certain given date in power query
Hi Folks,
So I have an excel file that looks like this and when I unpivot the day name (Sunday,Monday,Tuesday...), it should automatically calculate the previous week based on the given timesheet date. Say, the given timesheet date is 11.Apr.2021, it should calculate week backward and display it on another column which should be from (Sunday) 4.Apr.2021 until 10.Apr.2021 (Saturday).
And also, I was having a hard time to think about how am I going to do with the Pay Wages, Pay Rate and Margin column when I unpviot it, it comes like on the picture below.
I was actually trying to do a employee time calculation and its wages. Thanks alot.
Hi rbalza
You can unpivot weekday columns and add a date column to the table to make it clearer.
#"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Name", "Timesheet Date", "Pay Wages", "Pay Rate", "Margin"}, "Attribute", "Value"), #"Added Conditional Column" = Table.AddColumn(#"Unpivoted Columns", "Custom", each if [Attribute] = "Sunday" then [Timesheet Date]-#duration(7,0,0,0) else if [Attribute] = "Monday" then [Timesheet Date]-#duration(6,0,0,0) else if [Attribute] = "Tuesday" then [Timesheet Date]-#duration(5,0,0,0) else if [Attribute] = "Wednesday" then [Timesheet Date]-#duration(4,0,0,0) else if [Attribute] = "Thursday" then [Timesheet Date]-#duration(3,0,0,0) else if [Attribute] = "Friday" then [Timesheet Date]-#duration(2,0,0,0) else if [Attribute] = "Saturday" then [Timesheet Date]-#duration(1,0,0,0) else null)Regards,
Community Support Team _ Jing
If this post helps, please Accept it as the solution to help other members find it.
2 Replies
You could unpivot only the weekday columns, but it would be much better if you would use a proper data model and separate the facts out from the dimension data.
Hi rbalza
You can unpivot weekday columns and add a date column to the table to make it clearer.
#"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Name", "Timesheet Date", "Pay Wages", "Pay Rate", "Margin"}, "Attribute", "Value"), #"Added Conditional Column" = Table.AddColumn(#"Unpivoted Columns", "Custom", each if [Attribute] = "Sunday" then [Timesheet Date]-#duration(7,0,0,0) else if [Attribute] = "Monday" then [Timesheet Date]-#duration(6,0,0,0) else if [Attribute] = "Tuesday" then [Timesheet Date]-#duration(5,0,0,0) else if [Attribute] = "Wednesday" then [Timesheet Date]-#duration(4,0,0,0) else if [Attribute] = "Thursday" then [Timesheet Date]-#duration(3,0,0,0) else if [Attribute] = "Friday" then [Timesheet Date]-#duration(2,0,0,0) else if [Attribute] = "Saturday" then [Timesheet Date]-#duration(1,0,0,0) else null)Regards,
Community Support Team _ Jing
If this post helps, please Accept it as the solution to help other members find it.
