none
Help needed for Data Transformation in Powerquery RRS feed

  • Question

  • 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
    Friday, March 9, 2018 3:16 PM

Answers

  • Hey! 

    This post might help you achieve what you need. You'd use List.Dates to come up with the list of dates and then you'd use other techniques (like conditional column) to assign the correct column value to each row.

    Here's a sample of that:

    let
        Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnRyVtJRMtI31TcyMLQAMw0NYWx0bADHsTrRShGRURhajWBsAySMqj02FgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Name = _t, StartDate = _t, EndDate = _t, MondayHours = _t, TuesdayHours = _t, WednesdayHours = _t, ThursdayHours = _t, FridayHours = _t, SaturdayHours = _t, SundayHours = _t]),
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"StartDate", type date}, {"EndDate", type date}, {"MondayHours", Int64.Type}, {"TuesdayHours", Int64.Type}, {"WednesdayHours", Int64.Type}, {"ThursdayHours", Int64.Type}, {"FridayHours", Int64.Type}, {"SaturdayHours", Int64.Type}, {"SundayHours", Int64.Type}}),
        #"Added Custom" = Table.AddColumn(#"Changed Type", "Dates", each List.Dates([StartDate], Number.From( [EndDate]-[StartDate])+1, #duration(1,0,0,0) )),
        #"Expanded Dates" = Table.ExpandListColumn(#"Added Custom", "Dates"),
        #"Inserted Day Name" = Table.AddColumn(#"Expanded Dates", "Day Name", each Date.DayOfWeekName([Dates]), type text),
        #"Added Conditional Column" = Table.AddColumn(#"Inserted Day Name", "Hours", each if [Day Name] = "Monday" then [MondayHours] else if [Day Name] = "Tuesday" then [TuesdayHours] else if [Day Name] = "Wednesday" then [WednesdayHours] else if [Day Name] = "Thursday" then [ThursdayHours] else if [Day Name] = "Friday" then [FridayHours] else if [Day Name] = "Saturday" then [SaturdayHours] else if [Day Name] = "Sunday" then [SundayHours] else null),
        #"Removed Columns" = Table.RemoveColumns(#"Added Conditional Column",{"MondayHours", "TuesdayHours", "WednesdayHours", "ThursdayHours", "FridayHours", "SaturdayHours", "SundayHours"})
    in
        #"Removed Columns"

    • Marked as answer by Sandeephn Friday, March 9, 2018 6:17 PM
    Friday, March 9, 2018 4:55 PM

All replies

  • Hey! 

    This post might help you achieve what you need. You'd use List.Dates to come up with the list of dates and then you'd use other techniques (like conditional column) to assign the correct column value to each row.

    Here's a sample of that:

    let
        Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnRyVtJRMtI31TcyMLQAMw0NYWx0bADHsTrRShGRURhajWBsAySMqj02FgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Name = _t, StartDate = _t, EndDate = _t, MondayHours = _t, TuesdayHours = _t, WednesdayHours = _t, ThursdayHours = _t, FridayHours = _t, SaturdayHours = _t, SundayHours = _t]),
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"StartDate", type date}, {"EndDate", type date}, {"MondayHours", Int64.Type}, {"TuesdayHours", Int64.Type}, {"WednesdayHours", Int64.Type}, {"ThursdayHours", Int64.Type}, {"FridayHours", Int64.Type}, {"SaturdayHours", Int64.Type}, {"SundayHours", Int64.Type}}),
        #"Added Custom" = Table.AddColumn(#"Changed Type", "Dates", each List.Dates([StartDate], Number.From( [EndDate]-[StartDate])+1, #duration(1,0,0,0) )),
        #"Expanded Dates" = Table.ExpandListColumn(#"Added Custom", "Dates"),
        #"Inserted Day Name" = Table.AddColumn(#"Expanded Dates", "Day Name", each Date.DayOfWeekName([Dates]), type text),
        #"Added Conditional Column" = Table.AddColumn(#"Inserted Day Name", "Hours", each if [Day Name] = "Monday" then [MondayHours] else if [Day Name] = "Tuesday" then [TuesdayHours] else if [Day Name] = "Wednesday" then [WednesdayHours] else if [Day Name] = "Thursday" then [ThursdayHours] else if [Day Name] = "Friday" then [FridayHours] else if [Day Name] = "Saturday" then [SaturdayHours] else if [Day Name] = "Sunday" then [SundayHours] else null),
        #"Removed Columns" = Table.RemoveColumns(#"Added Conditional Column",{"MondayHours", "TuesdayHours", "WednesdayHours", "ThursdayHours", "FridayHours", "SaturdayHours", "SundayHours"})
    in
        #"Removed Columns"

    • Marked as answer by Sandeephn Friday, March 9, 2018 6:17 PM
    Friday, March 9, 2018 4:55 PM
  • Yes that works, Thanks so much!
    Friday, March 9, 2018 6:14 PM
  • Another possibility, using Excel as a source for the original table:

    let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        ChangedType = Table.TransformColumnTypes(Source,{{"Start Date", type date}, {"End Date", type date}}),
        UnpivotedColumns = Table.UnpivotOtherColumns(ChangedType, {"Name", "Start Date", "End Date"}, "Attribute", "Hours"),
        GroupedRows = Table.Group(UnpivotedColumns, {"Name", "Start Date"}, {{"Table", each _, type table}}),
        AddedIndex = Table.TransformColumns(GroupedRows, {"Table", each Table.AddIndexColumn(_, "Index")}),
        ExpandedTable = Table.ExpandTableColumn(AddedIndex, "Table", {"End Date", "Hours", "Index"}, {"End Date", "Hours", "Index"}),
        AddedCustom = Table.AddColumn(ExpandedTable, "Date", each List.Dates([Start Date], Number.From([End Date]) - Number.From([Start Date]) + 1, Duration.From(1)){[Index]}),
        RemovedColumns = Table.RemoveColumns(AddedCustom,{"Start Date", "End Date", "Index"}),
        ReorderedColumns = Table.ReorderColumns(RemovedColumns,{"Name", "Date", "Hours"})
    in
        ReorderedColumns

    Friday, March 9, 2018 6:17 PM
  • And another possibility....

    let
        Source = Excel.CurrentWorkbook(){[Name="Tabela1"]}[Content],
        ChangedType = Table.TransformColumnTypes(Source,{{"Start Date", type date}}),
        Ready = Table.Combine(Table.AddColumn(ChangedType, "tbl", each Table.FromColumns({List.Repeat({[Name]},7), List.Dates([Start Date],7,#duration(1,0,0,0)), List.LastN(Record.ToList(_),7)}, {"Name", "Date", "Hours"}))[tbl])
    in
        Ready

    • Proposed as answer by Colin Banfield Saturday, March 10, 2018 2:43 AM
    Saturday, March 10, 2018 12:04 AM