Horizontall string to appear vertically in a certain order

[Yollies]

Hi

I have 5000 cells populated horizontally from A1 to GJH1

I want to take the fist 5 entries and have it in A2 to E2

Then the next 5 entries in A3 to E3

until all 5000 there

Is there a quicker way than cut and paste?

thanks

Yollies

[Icey · Accepted solution]

Hi @Yollies ,

Try this:

Attach my PBIX file and the complete code:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Hcu5EcAgEATBXM6WwSChxyQOivzTUO047fVaNamjZg9nuMIId3jCG75AUw8mXNjwYcSJFW9vtfcP", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t, Column5 = _t, Column6 = _t, Column7 = _t, Column8 = _t, Column9 = _t, Column10 = _t, Column11 = _t, Column12 = _t, Column13 = _t, Column14 = _t, Column15 = _t, Column16 = _t, Column17 = _t, Column18 = _t, Column19 = _t, Column20 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}, {"Column8", type text}, {"Column9", type text}, {"Column10", type text}, {"Column11", type text}, {"Column12", type text}, {"Column13", type text}, {"Column14", type text}, {"Column15", type text}, {"Column16", type text}, {"Column17", type text}, {"Column18", type text}, {"Column19", type text}, {"Column20", type text}}),
    #"Transposed Table" = Table.Transpose(#"Changed Type"),
    #"Added Index" = Table.AddIndexColumn(#"Transposed Table", "Index", 1, 1),
    #"Added Custom" = Table.AddColumn(#"Added Index", "Custom", each Number.RoundUp([Index]/5)),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each Number.Mod([Index],5)),
    #"Replaced Value" = Table.ReplaceValue(#"Added Custom1",0,5,Replacer.ReplaceValue,{"Custom.1"}),
    #"Removed Columns" = Table.RemoveColumns(#"Replaced Value",{"Index"}),
    #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Removed Columns", {{"Custom", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(#"Removed Columns", {{"Custom", type text}}, "en-US")[Custom]), "Custom", "Column1"),
    #"Removed Columns1" = Table.RemoveColumns(#"Pivoted Column",{"Custom.1"}),
    #"Transposed Table1" = Table.Transpose(#"Removed Columns1")

in
    #"Transposed Table1"

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Yollies]

Hi Icey

Many thanks for all your trouble and your brilliant solution

Much appreciated

Yollies