Forum Discussion

Alirezam's avatar
Alirezam
Helper V
5 years ago
Solved

Transposing two column when there are consecuti

Hi friends,  In Power Query, is it possible? I have a table like this    Date Apr-21 Meter A Reading 1001 Date May-21 Meter B Reading 1003 Date Apr-21 Meter C R...
Need Help
  • ERD's avatar
    ERD
    5 years ago

    Hi Alirezam ,

    Here is an option:

    1. Add Index column (starting from 0)
    2. Divide Index by 3 using Number.IntegerDivide function
    3. Pivot first column using second column as values

    4. Remove Index column

    Code for everything:

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WckksSVXSUXIsKNI1MlSK1YlW8k0tSS0CCYF5QamJKZl56UC+oYEBRAFUi29iJboWJ0wtxshasNjijKnFQik2FgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t]),

    AddedIndex = Table.AddIndexColumn(Source, "Index", 0, 1, Int64.Type),
    IntegerDivided = Table.TransformColumns(AddedIndex,{{"Index", each Number.IntegerDivide(_, 3)}}),
    #"Pivoted Column" = Table.Pivot(IntegerDivided, List.Distinct(IntegerDivided[Column1]), "Column1", "Column2"),
    #"Removed Columns" = Table.RemoveColumns(#"Pivoted Column",{"Index"})
in
    #"Removed Columns"

     

    If this post helps, then please consider Accept it as the solution ✔️to help the other members find it more quickly.

CNENFRNL's avatar
CNENFRNL
Community Champion
5 years ago

Sure thing, you can split the original table into 3 sub-tables; then transpose them.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WckksSVXSUXIsKNI1MlSK1YlW8k0tSS0CCYF5QamJKZl56UC+oYEBRAFUi29iJboWJ0wtxshasNjijKnFQik2FgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t]),
    #"Split Table" = Table.Split(Source, 3),
    #"Transposed Tables" = List.Transform(#"Split Table", each Table.PromoteHeaders(Table.Transpose(_))),
    #"Combined Tables" = Table.Combine(#"Transposed Tables")
in
    #"Combined Tables"