Split single column (NO delimiters) into multiple columns

[abaker77]

Hello,
I have a worksheet with a single column of items, e.g. item001 - item100.
The number of items in this single column could vary, anywhere from 10 to 500 items.
The objective is to split that column into multiple columns.
I can't use Text-to-columns because there's no delimiter and I'm not splitting the item descriptions at all.
I was hoping there was a way that Power Query could do the trick without too much complication.
To illustrate, if I had 100 items and wanted to split it into 5 columns of 20 rows each, the resulting 5 columns should start with:
item001, item021, item041, item061, item081
The numer of split columns and rows might vary, e.g. instead of 5 columns of 20 rows, I might need 3 columns of 33 rows (with 1 spilling into a 4th column.)
I believe I can do this with a formula, for instance:
my original column starts in A6 (for 400 items) and I want to start my split in cell C6, and the # of rows I want in each column = num_rows
=INDEX($A$6:$A$406,ROW(C6)-5+(num_rows*(COLUMNS($C$6:C$6)-1)))
This formula apparently works but I have to subtract 5 (since I'm starting on row6) *and* I have to replicate the formula manually just num_rows down and to the right enough to accommodate all my items.
I have attached a simple worksheet to illustrate what I'm trying to achieve.
THANK YOU !

 

[alansidman]

I am close. Would this presentation work for you

Data Range

	A​	B​	C​	D​	E​
1​	Custom​	Custom.1​	Custom.2​	Custom.3​	Custom.4​
2​	item001​	item002​	item003​	item004​	item005​
3​	item006​	item007​	item008​	item009​	item010​
4​	item011​	item012​	item013​	item014​	item015​
5​	item016​	item017​	item018​	item019​	item020​
6​	item021​	item022​	item023​	item024​	item025​
7​	item026​	item027​	item028​	item029​	item030​
8​	item031​	item032​	item033​	item034​	item035​
9​	item036​	item037​	item038​	item039​	item040​
10​	item041​	item042​	item043​	item044​	item045​
11​	item046​	item047​	item048​	item049​	item050​
12​	item051​	item052​	item053​	item054​	item055​
13​	item056​	item057​	item058​	item059​	item060​
14​	item061​	item062​	item063​	item064​	item065​
15​	item066​	item067​	item068​	item069​	item070​
16​	item071​	item072​	item073​	item074​	item075​
17​	item076​	item077​	item078​	item079​	item080​
18​	item081​	item082​	item083​	item084​	item085​
19​	item086​	item087​	item088​	item089​	item090​
20​	item091​	item092​	item093​	item094​	item095​
21​	item096​	item097​	item098​	item099​	item100​

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"orig_col", type text}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),
    #"Inserted Modulo" = Table.AddColumn(#"Added Index", "Modulo", each Number.Mod([Index], 5), type number),
    #"Removed Columns" = Table.RemoveColumns(#"Inserted Modulo",{"Index"}),
    #"Added Custom" = Table.AddColumn(#"Removed Columns", "Custom", each if[Modulo]=0 then [orig_col] else null),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each if[Modulo]=1 then[orig_col] else null),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Custom.2", each if[Modulo]=2 then[orig_col] else null),
    #"Added Custom3" = Table.AddColumn(#"Added Custom2", "Custom.3", each if [Modulo]= 3 then[orig_col] else null),
    #"Added Custom4" = Table.AddColumn(#"Added Custom3", "Custom.4", each if [Modulo]=4 then [orig_col] else null),
    #"Filled Up" = Table.FillUp(#"Added Custom4",{"Custom.1", "Custom.2", "Custom.3", "Custom.4"}),
    #"Filtered Rows" = Table.SelectRows(#"Filled Up", each ([Custom] <> null)),
    #"Removed Columns1" = Table.RemoveColumns(#"Filtered Rows",{"orig_col", "Modulo"})
in
    #"Removed Columns1"

 

[pinarello]

For a dynamic solution, one parameter for the number of columns is sufficient. From this, the number of required rows results automatically.

 

[pinarello]

@alansidman

Your solutin shows a sequence Col A to E and Row 1 to 20 insteasd of Row 1 to 20 and Col A to E. Also it seems this solution is not dynamic in definition of required columns.

 

[AliGW]

@pinarrllo

The way I read the requirements, I think the number of ROWS is more important than the number of columns, so your solution isn't quite what's required, either.

 

[pinarello]

Hello Ali,

that may be the case, of course. I had also realized it in the First Approach for a dynamic number of rows. But for me personally I came to the conclusion that the prirority is on the number of columns and therefore I changed it again.

But in the end it doesn't matter whether the prioritization lies with rows or columns, because I can create a solution for both questions without any problems. Especially since my first solution was a bit easier to create.

 

[AliGW]

Can you do that, please? I'm curious to see how it's done. Using parameters with PQ is something I'm still learning to do. Thanks!

 

[alansidman]

[COLOR=#333333]Your solutin shows a sequence Col A to E and Row 1 to 20 insteasd of Row 1 to 20 and Col A to E. [/COLOR]

I am aware of this which is why I asked the OP if it was an acceptable solution. I was unable to think of a way to offer it up in the manner asked. I, too, am anxious to see your dynamic solution.

 

[pinarello]

I have now inserted a second query into the workbook, in which the number of desired rows is specified. In both queries, however, the control is only by quasi parameters, since these can only be used in the respective query. Real parameters are created separately and can then be addressed and used in all queries of a workbook.

By the way, there is nothing wrong with presenting solutions that do not represent the optimum. Because those who deal with a question and develop their own solution learn much more in the end than those who just remain passive.

When I started to develop formula solutions in a German forum some years ago, at the beginning all other solutions were much better than mine. But I was able to learn from the better solutions every time in the process.

Then in December 2018 I came, more or less by chance to Power Query and at first I had only understood train station. But because I tried from the beginning to develop solutions to all kinds of questions with Power Query, I had the first success relatively quickly.

 

[p45cal]

You're using Office 365? Then in any cell:
=TRANSPOSE(INDEX(A6:A105,SEQUENCE(D3,D2)))

 

[abaker77]

I want to thank all contributors for their time and expertise!
I was originally looking for a PQ solution but the TRANSPOSE formula works PERFECTLY - it's BRILLIANT!
But the PQ solution from pinarello seems to work beautifully too; I will need to go over the steps to see if I can replicate it myself.
Thank you all so much again!

 

[pinarello]

I have now merged the two previous queries (1 time definition desired number of columns and 1 time definition desired number of rows) into one query.

Thus, rows or columns and the corresponding number can now be selected in this combined query.

Compared to the formula solution (TRANSPOSE/SEQUENCE), the advantage of the Power Query is that the result can also contain empty cells. This is always the case if the division of the total number of input records by the desired number of rows/columns results in a remainder.