none
Converting rows to columns for repeating series (quasi-pivot/complex transform???) RRS feed

  • Question

  • Hi all,

    How do I do this in Power Query (I know how to do it Excel):

    Data:

    • Col1, Col2
    • Time, 10AM
    • Duration, 10 sec
    • Command, Copy
    • Time, 11AM
    • Duration, 10 sec
    • Command, Move
    • Time, 12AM
    • Duration, 17 sec
    • Command, Delete

    to the following:

    • Col1, Col2, Col3
    • 10AM,10 sec, Copy
    • 11AM,10 sec, Move
    • 12AM,17 sec, Delete

    TIA


    Wednesday, January 13, 2016 12:19 PM

Answers

  • Yes pivoting will do, but you need an additional column indicating which rows belong together:

    let
        Source = Excel.CurrentWorkbook(){[Name="Tabelle2"]}[Content],
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"Col1", type text}, {"Col2", type any}}),
        #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1),
        #"Integer-Divided Column" = Table.TransformColumns(#"Added Index", {{"Index", each Number.IntegerDivide(_, 3), Int64.Type}}),
        #"Pivoted Column2" = Table.Pivot(#"Integer-Divided Column", List.Distinct(#"Integer-Divided Column"[Col1]), "Col1", "Col2")
    in
        #"Pivoted Column2"


    Imke Feldmann TheBIccountant.com

    Wednesday, January 13, 2016 4:27 PM
    Moderator