Forum Discussion

LukeReds's avatar
LukeReds
Helper II
2 years ago
Solved

Transform an horizontal table in vertical (3 columns) with Power Query

Hi to everyone,

I have a table like the one in the image above, every year there will be 3 new columns (name, description and a date), i need to trasform this table like the one below, only 3 columns with the date in all the cells of the 3td column

I can do it with vba but i need to use power query ( no dax)

 

Thank you in advance

 

Need Help
  • m_dekorte's avatar
    m_dekorte
    2 years ago

    Hi LukeReds 

     

    Give this a go. It assumes the same set of three columns is repeated in the exact same order.

    let
    Source = YourTable,
    Split = Table.Combine( 
        List.Transform( 
            List.Split( Table.ColumnNames(Source), 3 ), each [
                t = Table.SelectColumns(Source, _),
                v = List.Last( Table.ColumnNames(t)),
                r = Table.FromColumns( 
                    List.RemoveLastN( Table.ToColumns(t), 1) 
                    & {List.Repeat( {v}, Table.RowCount(t))},
                    {"Name", "Description", "Date"}
                )
            ][r] 
        )
    )
in
    Split

     

    with this result

     

    I hope this is helpful 

5 Replies

  • m_dekorte's avatar
    m_dekorte
    Resident Rockstar
    2 years ago

    Hi LukeReds 

     

    Give this a go. It assumes the same set of three columns is repeated in the exact same order.

    let
    Source = YourTable,
    Split = Table.Combine( 
        List.Transform( 
            List.Split( Table.ColumnNames(Source), 3 ), each [
                t = Table.SelectColumns(Source, _),
                v = List.Last( Table.ColumnNames(t)),
                r = Table.FromColumns( 
                    List.RemoveLastN( Table.ToColumns(t), 1) 
                    & {List.Repeat( {v}, Table.RowCount(t))},
                    {"Name", "Description", "Date"}
                )
            ][r] 
        )
    )
in
    Split

     

    with this result

     

    I hope this is helpful 

  • dufoq3's avatar
    dufoq3
    Community Champion
    2 years ago

    Hi LukeReds, 2 more solutions:

     

    Result

     

    v1

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WKi7JT842VNJRSkktTi5SgHOBCKdUrA5UnxGqpBGSPmxScH3GqJLGSPqwScH1maBKmiDpwyYF12eKKmmKpA+bVGwsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [name = _t, description = _t, #"01/01/2000" = _t, name2 = _t, description2 = _t, #"01/01/2001" = _t]),
    ToCol = List.TransformMany(Table.ToRows(Source),
        each {List.Alternate(_, 1,2,2)},
        (x,y)=> {y{0}, y{1}, Table.ColumnNames(Source){2}, y{2}, y{3}, Table.ColumnNames(Source){5}} ),
    ToTbl = Table.Combine(List.Transform(List.Split(List.Zip(ToCol), 3), (x)=> Table.FromColumns(x, {"name", "description", "date"})))
in
    ToTbl

     

    v2 - with this solution it doesn't matter how many pairs of columns do you have (i.e. you can have 5 pars of [Name], [Description] and [Date] columns)

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WKi7JT842VNJRSkktTi5SgHOBCKdUrA5UnxGqpBGSPmxScH3GqJLGSPqwScH1maBKmiDpwyYF12eKKmmKpA+bVGwsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [name = _t, description = _t, #"01/01/2000" = _t, name2 = _t, description2 = _t, #"01/01/2001" = _t]),
    Ad_Helper = Table.AddColumn(Source, "Helper", each 
        [ a = Record.ToList(_),
          b = List.Alternate(a, 1, 2, 2), //name and description
          c = List.Alternate(Record.FieldNames(_), 2, 1), //every 3rd column name
          d = List.Split(b, 2), //splitted name and description
          e = List.Transform({ 0..List.Count(c)-1 }, (x)=> d{x} & {c{x}}) //combined name, descriptotion, 3rd column name
        ][e], type list),
    Helper = Table.Combine(List.Transform(List.Zip(Ad_Helper[Helper]), (x)=> Table.FromRows(x, {"name", "description", "date"})))
in
    Helper