Help needed for Data Transformation in Powerquery

hnsbhat
Posts: 1
Joined: Sat Mar 10, 2018 1:12 am

Help needed for Data Transformation in Powerquery

Postby hnsbhat » Sat Mar 10, 2018 1:14 am

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

jiny680
Posts: 6
Joined: Thu Oct 27, 2016 7:10 pm

Re: Help needed for Data Transformation in Powerquery

Postby jiny680 » Sat Mar 10, 2018 5:56 pm

First use function List.Dates to transform those two columns into a list of one week dates.
Then expand that list.
Finally unpivot those hour columns.

Rudi
Posts: 6
Joined: Fri Sep 01, 2017 4:33 pm

Re: Help needed for Data Transformation in Powerquery

Postby Rudi » Fri Apr 13, 2018 9:03 pm

Attachments
Data.zip
(278 Bytes) Downloaded 59 times


Return to “Power Query”

Who is online

Users browsing this forum: No registered users and 1 guest

cron