Forum Discussion
Fill up groups of values
Is it possible with power query to transform table 1 into table 2? Can you help me.
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 FromColumnsthis 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
3 Replies
- Greg_DecklerCommunity Champion
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?
- rmengHelper II
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?
- Jimmy801Community Champion
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 FromColumnsthis 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