Forum Discussion
hnsbhat
8 years agoHelper I
Help needed for Data Transformation in Powerquery
Hi, I have a data table with Names, start date of the week, end date of the week and hours for each week day in columns. I need to transpose this table to get date-wise hours for each name. Itries unpivot columns, but it gives same headings for each date in rows, I am not able to figure out how to get dates in between the week starts and week ending.
Below are the input table and expected output. Is there a way to do this in Powerquery? Thanks for your help.
Input -
NameStart DateEnd DateMonday HoursTuesday HoursWednesday HoursThursday HoursFriday HoursSaturday HoursSunday Hours
| ABC | 5/2/2018 | 11/2/2018 | 8 | 8 | 8 | 8 | 0 | 0 | 0 |
| ABC | 12/2/2018 | 18/2/2018 | 8 | 8 | 8 | 8 | 8 | 0 | 0 |
| XYZ | 5/2/2018 | 11/2/2018 | 8 | 4 | 4 | 4 | 4 | 0 | 0 |
| XYZ | 12/2/2018 | 18/2/2018 | 0 | 0 | 0 | 8 | 8 | 0 | 0 |
Expected Output -
| Name | Date | Hours |
| ABC | 5/2/2018 | 8 |
| ABC | 6/2/2018 | 8 |
| ABC | 7/2/2018 | 8 |
| ABC | 8/2/2018 | 8 |
| ABC | 9/2/2018 | 0 |
| ABC | 10/2/2018 | 0 |
| ABC | 11/2/2018 | 0 |
| ABC | 12/2/2018 | 8 |
| ABC | 13/2/2018 | 8 |
| ABC | 14/2/2018 | 8 |
| ABC | 15/2/2018 | 8 |
| ABC | 16/2/2018 | 8 |
| ABC | 17/2/2018 | 0 |
| ABC | 18/2/2018 | 0 |
| XYZ | 5/2/2018 | 8 |
| XYZ | 6/2/2018 | 4 |
| XYZ | 7/2/2018 | 4 |
| XYZ | 8/2/2018 | 4 |
| XYZ | 9/2/2018 | 4 |
| XYZ | 10/2/2018 | 0 |
| XYZ | 11/2/2018 | 0 |
| XYZ | 12/2/2018 | 0 |
| XYZ | 13/2/2018 | 0 |
| XYZ | 14/2/2018 | 0 |
| XYZ | 15/2/2018 | 8 |
| XYZ | 16/2/2018 | 8 |
| XYZ | 17/2/2018 | 0 |
| XYZ | 18/2/2018 | 0 |