Transform a table with n rows in a single cell for every guy

[LukeReds]

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 (no dax, no vba). The numbers of rows for every guy can change

Thank you in advance

[LukeReds]

tahnk you to everyone, i got the solution!

[Anonymous]

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.

[Omid_Motamedise · Accepted solution]

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 

[jgeddes]

I am not sure what format you require for final data but here is an example of turning...

into this...

Example code...

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("TYy9CoAwDIRfpXR2aKs+gauCQ7fSIUiHQDTQ1sWnF1L8WZI77r4LQS+QkXXsgl4RrgtEOmONUQsSHC3bEpGyrh/EyZlPLK09cS6tZoXznPHPPVhNpMahF512QPrGPO7yiTeoyIcYyAnelT8Vbw==", 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}}),
    addInitialIndex = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1, Int64.Type),
    groupBlankCount = Table.Group(addInitialIndex, {"Column1"}, {{"_nested", each _, type table [Column1=nullable text, Index=number]}}),
    addBlankInstance = Table.TransformColumns(groupBlankCount, {{"_nested", each Table.AddIndexColumn(_, "instance", 1, 1, Int64.Type)}}),
    expandNestedRows = Table.ExpandTableColumn(addBlankInstance, "_nested", {"Index", "instance"}, {"Index", "instance"}),
    sortToOriginalOrder = Table.Buffer(Table.Sort(expandNestedRows, {"Index", Order.Ascending})),
    addOccurenceColumn = Table.AddColumn(sortToOriginalOrder, "Occurence", each if [Column1] = "" then [instance] else null),
    fillUp = Table.FillUp(addOccurenceColumn,{"Occurence"}),
    addLastOccurence = Table.ReplaceValue(fillUp,null,List.Max(fillUp[Occurence])+1,Replacer.ReplaceValue,{"Occurence"}),
    removeColumns = Table.RemoveColumns(addLastOccurence,{"Index", "instance"}),
    removeBlankRows = Table.SelectRows(removeColumns, each ([Column1] <> "")),
    groupByOccurence = Table.Group(removeBlankRows, {"Occurence"}, {{"_nestedRows", each Table.SelectColumns(_, {"Column1"}), type table [Column1=nullable text, Custom=number]},{"nestedRowCount", each Table.RowCount(_), Int64.Type}}),
    transposeNestedTables = Table.TransformColumns(groupByOccurence, {{"_nestedRows", each Table.Transpose(_)}}),
    columnList = List.Generate(()=>1, each _ <= List.Max(transposeNestedTables[nestedRowCount]), each _ + 1, each "Column"&Text.From(_)),
    selectNeededColumn = Table.SelectColumns(transposeNestedTables, {"_nestedRows"}),
    expandNested = Table.ExpandTableColumn(selectNeededColumn, "_nestedRows", columnList)
in
    expandNested

Hopefully this gets you pointed in the right direction.