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 

 

DateApr-21
MeterA
Reading1001
DateMay-21
MeterB
Reading1003
DateApr-21
MeterC
ReadingJun-21

 

And I want this table:

DateMeterReading
Apr-21A1001
May-21B1003
Apr-21C1008

 

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.

2 Replies

  • ERD's avatar
    ERD
    Community Champion
    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"