Split single column (NO delimiters) into multiple columns

abaker77 · Mar 27, 2021

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 · Mar 28, 2021

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

Code:

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 · Mar 28, 2021

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

pinarello · Mar 28, 2021

@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 · Mar 28, 2021

@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 · Mar 28, 2021

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 · Mar 28, 2021

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 · Mar 28, 2021

Code:

[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 · Mar 28, 2021

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 · Mar 29, 2021

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

abaker77 · Mar 31, 2021

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 · Apr 2, 2021

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.

Split single column (NO delimiters) into multiple columns

abaker77

New member

Attachments

alansidman

Member

pinarello

Member

Attachments

pinarello

Member

AliGW

Administrator

pinarello

Member

AliGW

Administrator

alansidman

Member

pinarello

Member

Attachments

p45cal

Super Moderator

abaker77

New member

pinarello

Member

Attachments