Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 |
Solved! Go to Solution.
Hi, please check the video.
This is the PBIX File
Regards
Victor
Lima - Peru
Great, it works. Thank you!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
98 | |
77 | |
66 | |
53 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |