Fill up groups of values

[rmeng]

Is it possible with power query to transform table 1 into table 2? Can you help me.

[Jimmy801 · Accepted solution]

Hello @rmeng 

this requires some Table.ToColumns, some transformation of the list created and then Table.FromColumns. What I cannot see from your data if on some point the number changes, what would lead then in repeating my transformation of tables to multiple entities. In this case you would need to group your data and apply my solution as function to it. Here the solution to the data provided by you

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUQKiCAgFRbE6cAmEXASmDC4dyJpw6EBIOOIyywmXhDOGRCwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Numero = _t, A = _t, B = _t, C = _t, D = _t, E = _t, F = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Numero", Int64.Type}, {"A", type text}, {"B", type text}, {"C", type text}, {"D", type text}, {"E", type text}, {"F", type text}}),
    ToColumns = Table.ToColumns(#"Changed Type"),
    MaxLenghtOfColumn = List.Max(List.Transform(List.RemoveFirstN(ToColumns), each List.Count(List.Select(_, each _ <>"")) )),
    TransformColumnDeleteEmpty = List.Transform
    (
        ToColumns,
        each List.FirstN(List.Select(_,each _ <>""), MaxLenghtOfColumn)
    ),
    
    FromColumns = Table.FromColumns
    (
        TransformColumnDeleteEmpty,
        Table.ColumnNames(#"Changed Type")
    )
in
    FromColumns

this transforms this

into this

Copy paste this code to the advanced editor in a new blank query to see how the solution works.

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

[Greg_Deckler]

@rmeng Would be a piece of cake if you had each row labeled with A, B, C. Then you could just group. What is the logic behind how these things group together?

[rmeng]

Hello,

A, B , C are random text inside a cell. There´s no rules there, As you told I´ll try to do a Group BY?