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: 4
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

My 2 cents contribution

Use the code below on this source file:

Data.zip
(278 Bytes) Downloaded 6 times


Code: Select all

let
    Source = Csv.Document(File.Contents("C:\Data.txt"),[Delimiter="   ", Columns=11, Encoding=1252, QuoteStyle=QuoteStyle.None]),
    #"Removed Other Columns" = Table.SelectColumns(Source,{"Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9", "Column10"}),
    #"Removed Blank Rows" = Table.SelectRows(#"Removed Other Columns", each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"", null}))),
    #"Promoted Headers" = Table.PromoteHeaders(#"Removed Blank Rows", [PromoteAllScalars=true]),
    #"Changed Type with Locale" = Table.TransformColumnTypes(#"Promoted Headers", {{"Start Date", type date}, {"End Date", type date}}, "en-GB"),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type with Locale", {"Name", "Start Date", "End Date"}, "Attribute", "Value"),
    #"Added Index" = Table.AddIndexColumn(#"Unpivoted Other Columns", "Index", 0, 1),
    #"Calculated Modulo" = Table.TransformColumns(#"Added Index", {{"Index", each Number.Mod(_, 7), type number}}),
    #"Added Custom" = Table.AddColumn(#"Calculated Modulo", "Date", each Date.AddDays([Start Date], [Index])),
    #"Removed Other Columns1" = Table.SelectColumns(#"Added Custom",{"Name", "Date", "Value"}),
    #"Changed Type with Locale1" = Table.TransformColumnTypes(#"Removed Other Columns1", {{"Date", type date}}, "en-GB"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Changed Type with Locale1",{{"Value", Int64.Type}})
in
    #"Changed Type1"


Return to “Power Query”

Who is online

Users browsing this forum: No registered users and 1 guest

cron