Fill up groups of values

rmeng — Sun, 28 Feb 2021 11:33:17 GMT

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

Re: Fill up groups of values

Greg_Deckler — Sun, 28 Feb 2021 13:43:16 GMT

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?

Re: Fill up groups of values

rmeng — Sun, 28 Feb 2021 15:06:55 GMT

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?

Re: Fill up groups of values

Jimmy801 — Sun, 28 Feb 2021 16:09:04 GMT

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

topic Re: Fill up groups of values in Power Query

Fill up groups of values

Re: Fill up groups of values

Re: Fill up groups of values

Re: Fill up groups of values