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

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.

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.

tahnk you to everyone, i got the solution!