Forum Discussion
Solved
Transform a table with n rows in a single cell for every guy
Hi to everyone, i have this table, an empty cell divide one guy from the other (of course there are several guys in the table, not only 2) I need to have this data using only power query...
Use this code
let Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content], #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"Column1"}, {{"Count", each Text.Combine([Column1],"#(lf)"), type nullable text}},0,(a,b)=>Number.From(b[Column1]=null)) in #"Grouped Rows"result in
Hi,
Thanks for the solutions Omid_Motamedise and jgeddes offered, and i want to offer some more information for user to refer to.
hello LukeReds ,you can create a blank query and input the following code to advanced editor.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclSK1QGShhDKCEIZgykw4QQhIfJOEHkniLyTCYQyhVBmCE3OEBKiyRmiyRmoKRYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
#"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1, Int64.Type),
#"Added Custom" = Table.AddColumn(#"Added Index", "Custom", each let a=[Index]
in Table.RowCount(Table.SelectRows(#"Added Index",each [Index]<a and [Column1]=""))),
#"Grouped Rows" = Table.Group(#"Added Custom", {"Custom"}, {{"Count", each Text.Combine([Column1],"
")}}),
#"Trimmed Text" = Table.TransformColumns(#"Grouped Rows",{{"Count", Text.Trim, type text}}),
#"Removed Columns" = Table.RemoveColumns(#"Trimmed Text",{"Custom"})
in
#"Removed Columns"Original data
After transforming.
Best Regards!
Yolo Zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.