Transposing two column when there are consecuti

[Alirezam]

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
Reading	Jun-21

And I want this table:

Date	Meter	Reading
Apr-21	A	1001
May-21	B	1003
Apr-21	C	1008

[CNENFRNL]

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"

[ERD · Accepted solution]

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.