Combine unknown number of columns

[JackDanIce]

Hi,

I have the table on the left in PQ and wish to output the table on the right:

Table in PowerQuery						Desired Output
Student	E	E Class	F	F Class		Student	E Class	F Class
A	0	<	8	<<<		A	0 | <	8 | <<<
B	1	<	9	<<<<		B	1 | <	9 | <<<<
C	2	<	10	<<<<		C	2 | <	10 | <<<<
D	3	<<	0	<		D	3 | <<	0 | <
E	4	<<	1	<		E	4 | <<	1 | <
F	5	<<	2	<		F	5 | <<	2 | <
G	6	<<<	3	<<		G	6 | <<<	3 | <<
H	7	<<<	4	<<		H	7 | <<<	4 | <<
I	8	<<<	5	<<		I	8 | <<<	5 | <<
J	9	<<<<	6	<<<		J	9 | <<<<	6 | <<<
K	10	<<<<	7	<<<		K	10 | <<<<	7 | <<<

The text before " Class" is always unique (i.e. always a pair of columns {A and A Class}, {B and B Class} etc) and I wish to combine them into single columns with a delimiter to separate, like the table on the right.

Unfortunately, the number of columns is dynamic so not keen to hardcode this.

So far I can do this for a single pair of columns as:

#"Add A Class" = Table.RenameColumns(Table.RemoveColumns(Table.AddColumn(Source, "A Class1", each Number.ToText([A] & " | " & [A Class]), {"A", "A Class"}), {{"A Class1", "A Class"}})

Any suggestions please?

TIA,
Jack

 

[I_cant_read]

Hi there Jack,

Tried the following.

Cheers ^^

 

[smozgur]

There could be easier ways than using List.Generate but here is how I would approach to this one.
I can comment on the code if it is not clear what it is doing and you are interested.
(Obviously, I wrote it to work with the new columns dynamically)

Source data (assumption: Just like in the provided sample data: the first column is the student name, other columns are the pairs just like E, E Class and no more extra columns)

Book1
	A	B	C	D	E
1	Student	E	E Class	F	F Class
2	A	0	<	8	<<<
3	B	1	<	9	<<<<
4	C	2	<	10	<<<<
5	D	3	<<	0	<
6	E	4	<<	1	<
7	F	5	<<	2	<
8	G	6	<<<	3	<<
9	H	7	<<<	4	<<
10	I	8	<<<	5	<<
11	J	9	<<<<	6	<<<
12	K	10	<<<<	7	<<<
Students

M Code

let
    Source = Excel.CurrentWorkbook(){[Name="StudentTable"]}[Content],
    ColumnNames = Table.ColumnNames(Source),
    ColumnCount = (Table.ColumnCount(Source) - 1)/2,

    BaseTable = Table.TransformColumnTypes(
        Source,
        List.Transform(ColumnNames, each {_, type text })
    ),
    AddColumns = List.Generate(
        () => [i = 0, Table = BaseTable],
        each [i] <= ColumnCount,
        each [i = [i] + 1,
            Table = Table.AddColumn(
                        [Table],
                        "Merged " & ColumnNames{2*([i]+1)},
                        each Table.Column(_, ColumnNames{2*i-1}) & " | " & Table.Column(_, ColumnNames{2*i})
                    )
        ]
    ),
    LastItem = AddColumns{ColumnCount},
    Table = Table.RemoveColumns(LastItem[Table],
                List.Generate(
                    () => [i = 1],
                    each [i] <= ColumnCount * 2,
                    each [i = [i] + 1],
                    each ColumnNames{[i]}
                )
    ),
    RenameColumns = List.Transform(Table.ColumnNames(Table), each {_, Text.Replace(_, "Merged ", "")}),
    Result = Table.RenameColumns(Table, RenameColumns)
in
    Result

Result

1168801.xlsm
	A	B	C
1	Student	E Class	F Class
2	A	0 | <	8 | <<<
3	B	1 | <	9 | <<<<
4	C	2 | <	10 | <<<<
5	D	3 | <<	0 | <
6	E	4 | <<	1 | <
7	F	5 | <<	2 | <
8	G	6 | <<<	3 | <<
9	H	7 | <<<	4 | <<
10	I	8 | <<<	5 | <<
11	J	9 | <<<<	6 | <<<
12	K	10 | <<<<	7 | <<<
Result

 

[I_cant_read]

Sorry was unable to digest the text below the images.

I would try and use the feature merge on this:
1.) A table with a unique list of students then load as a connection.
2.) Combine Queries > Merge with the other tables>
3.) Once merged you could use a custom column to combine them in a single column.

 

[JackDanIce]

Hi there, @I_cant_read and @smozgur, thank you both for your replies

I found a solution where I turned the input data into normalised form, applied a function to a single column then pivoted out, which kinda looks like a combination of both your suggestions:
(Some column header values are different but structure is equivalent for structure above)

let
    Source = Data_Output,
    #"Removed Columns" = Table.SelectColumns(Source,{"Fund Name", "Class Type", "P0 | Overall Score"}),
    #"Reordered Rows" = Table.ReorderColumns(Table.Sort(#"Removed Columns",{{"Class Type", Order.Ascending}, {"Fund Name", Order.Ascending}}), {"Fund Name", "P0 | Overall Score", "Class Type"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Reordered Rows",{{"Fund Name", type text}, {"Class Type", type text}, {"P0 | Overall Score", Int64.Type}}),
    #"Added Custom" = Table.RemoveColumns(Table.AddColumn(#"Changed Type", "Class Star Rating", each Number.ToText([#"P0 | Overall Score"]) & " " & Text.Repeat("<", Number.IntegerDivide([#"P0 | Overall Score"] , 3) + 1)), {"P0 | Overall Score"}),  
  
    #"Sorted Rows" = Table.Sort(#"Added Custom",{{"Fund Name", Order.Ascending}, {"Class Type", Order.Ascending}}),
    #"Pivot" = Table.Pivot(#"Sorted Rows", List.Distinct(#"Sorted Rows"[#"Class Type"]), "Class Type", "Class Star Rating"),  
    #"Output" = Table.ReorderColumns(#"Pivot", List.Sort(List.LastN(Table.ColumnNames(#"Pivot"),List.Count(Table.ColumnNames(#"Pivot"))-1)))
  
in
    #"Output"

Now a new headache is, I can't get Conditional formatting to isolate the number part of the string and provide a colour scale colouring (tried various CF formulas and no joy), so I may have to leave it as two columns and only apply the CF to the Class column (not class rating)

Anyway, marking this as closed but thank you both, much appreciated

 

[Bo_Ry]

Another M code for dynamic column

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    ChangedType = Table.TransformColumnTypes(Source, List.Transform(Table.ColumnNames(Source),each {_} & {type text} )),
    Merged = Table.CombineColumns(ChangedType,List.Skip(Table.ColumnNames(Source),1),Combiner.CombineTextByEachDelimiter(List.Repeat({" | ","_"},Table.ColumnCount(Source)), QuoteStyle.None),"M"),
    Split = Table.SplitColumn(Merged, "M", Splitter.SplitTextByDelimiter("_", QuoteStyle.Csv), List.Select(Table.ColumnNames(Source),each Text.EndsWith(_,"Class")))
in
    Split

 

[JackDanIce]

I think I understand it - that's cool, thank you for sharing @Bo_Ry