Receiving All Columns in One Step

[kellyjkon]

Hi everyone,

I want to group my vehicle list by year and model in power query. Then I want to insert the data in one step from DesiredOutput as a column List.
I know how to add one by one by with Add.Column as below, however, I don't want it to be a lot of manual steps as I will be add 15 columns.
Is there an M code where we can do these in one step?

Present:
Table.AddColumn(#"Previous Step", "New Column", each Table.Column([DesiredOutput],"Column Name"))
Table.AddColumn(#"Previous Step", "New Column", each Table.Column([DesiredOutput],"Column Name"))

Desired(maybe smt like that?)
Table.AddColumnS(#"Previous Step", "New Column","Second Column","Third Column", each Table.Column([DesiredOutput],"Column Name","Column Name 2","Column Name 3"))

Thanks!!

 

[RoryA]

Why don't you just expand the table by clicking the double arrow and choose your columns there?

 

[kellyjkon]

Because I will separate all the data - which I have grouped in Table - with commas and turn them into a single line per year and model

 

[kellyjkon]

As you can see below, i have to add columns from "Group By Data" table, after that I will concatenate all list values with comma in distinct rows per year and model.
But in this way let's say i need to 10 column from "Group By Data", I will have to create 10 "added columns" plus 10 "extracted value" steps and it is not effective at all.

 

[JGordon11]

This example may help

let
    tbl1 = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    tbl2 = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    tbl3 = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
    tbl = Table.FromColumns({{tbl1,tbl2,tbl3}}, {"OriginalColumn1"}),
    columns = {"Column1","Column2","Column3","Column4"},
    AddedColumns = List.Accumulate(columns, tbl, (s,c)=> Table.AddColumn(s,c, each 
                    Text.Combine( List.Transform(Table.Column([OriginalColumn1], c), (x)=> Text.From(x)), ", " ))),
    Result = Table.FromColumns({{"Table1", "Table2", "Table3"}} & List.Skip(Table.ToColumns(AddedColumns)), {"Table Name"} & List.Skip(Table.ColumnNames(AddedColumns)))
in
    Result

Book2
	A	B	C	D	E	F
1	Column1	Column2	Column3	Column4
2	17	13	23	7
3	21	15	1	22
4	14	11	18	6
5	19	9	4	12
6	20	16	24	2
7	3	10	8	5
8
9	Column1	Column2	Column3	Column4
10	26	9	8	22
11	3	17	31	39
12	40	12	33	27
13	18	35	34	36
14	14	16	21	28
15	1	11	29	38
16	19	37	15	7
17	5	32	25	20
18	2	13	30	24
19	23	4	6	10
20
21	Column1	Column2	Column3	Column4
22	6	12	10	3
23	16	18	8	20
24	2	9	5	7
25	15	13	1	19
26	11	4	17	14
27
28	Table Name	Column1	Column2	Column3	Column4
29	Table1	17, 21, 14, 19, 20, 3	13, 15, 11, 9, 16, 10	23, 1, 18, 4, 24, 8	7, 22, 6, 12, 2, 5
30	Table2	26, 3, 40, 18, 14, 1, 19, 5, 2, 23	9, 17, 12, 35, 16, 11, 37, 32, 13, 4	8, 31, 33, 34, 21, 29, 15, 25, 30, 6	22, 39, 27, 36, 28, 38, 7, 20, 24, 10
31	Table3	6, 16, 2, 15, 11	12, 18, 9, 13, 4	10, 8, 5, 1, 17	3, 20, 7, 19, 14
32
Sheet1

 

[kellyjkon]

This example may help

let
    tbl1 = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    tbl2 = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    tbl3 = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
    tbl = Table.FromColumns({{tbl1,tbl2,tbl3}}, {"OriginalColumn1"}),
    columns = {"Column1","Column2","Column3","Column4"},
    AddedColumns = List.Accumulate(columns, tbl, (s,c)=> Table.AddColumn(s,c, each
                    Text.Combine( List.Transform(Table.Column([OriginalColumn1], c), (x)=> Text.From(x)), ", " ))),
    Result = Table.FromColumns({{"Table1", "Table2", "Table3"}} & List.Skip(Table.ToColumns(AddedColumns)), {"Table Name"} & List.Skip(Table.ColumnNames(AddedColumns)))
in
    Result

Book2
	A	B	C	D	E	F
1	Column1	Column2	Column3	Column4
2	17	13	23	7
3	21	15	1	22
4	14	11	18	6
5	19	9	4	12
6	20	16	24	2
7	3	10	8	5
8
9	Column1	Column2	Column3	Column4
10	26	9	8	22
11	3	17	31	39
12	40	12	33	27
13	18	35	34	36
14	14	16	21	28
15	1	11	29	38
16	19	37	15	7
17	5	32	25	20
18	2	13	30	24
19	23	4	6	10
20
21	Column1	Column2	Column3	Column4
22	6	12	10	3
23	16	18	8	20
24	2	9	5	7
25	15	13	1	19
26	11	4	17	14
27
28	Table Name	Column1	Column2	Column3	Column4
29	Table1	17, 21, 14, 19, 20, 3	13, 15, 11, 9, 16, 10	23, 1, 18, 4, 24, 8	7, 22, 6, 12, 2, 5
30	Table2	26, 3, 40, 18, 14, 1, 19, 5, 2, 23	9, 17, 12, 35, 16, 11, 37, 32, 13, 4	8, 31, 33, 34, 21, 29, 15, 25, 30, 6	22, 39, 27, 36, 28, 38, 7, 20, 24, 10
31	Table3	6, 16, 2, 15, 11	12, 18, 9, 13, 4	10, 8, 5, 1, 17	3, 20, 7, 19, 14
32
Sheet1

Thanks but i have only one table to get data which i pointed as "Group By Data" . With that solution i guess i need to create duplicate "Group By Data" tables 3 times?
I want to create 5 new column from my grouping data table in one step with M query.

 

[kellyjkon]

Any suggestion?

 

[GraH]

Thanks but i have only one table to get data which i pointed as "Group By Data" . With that solution i guess i need to create duplicate "Group By Data" tables 3 times?
I want to create 5 new column from my grouping data table in one step with M query.

Looks like @JGordon11's solution is what you need.

Replace "Source" by what last step name you have in your original query.

No need to replicate the query 3 times.

let
    tbl = Table.FromColumns({Source[DesiredOutput]}, {"OriginalColumn1"}),
    columns = {"Column1","Column2","Column3","Column4"},
    AddedColumns = List.Accumulate(columns, tbl, (s,c)=> Table.AddColumn(s,c, each 
                    Text.Combine( List.Transform(Table.Column([OriginalColumn1], c), (x)=> Text.From(x)), ", " ))),
    Result = Table.FromColumns({{"Table1", "Table2", "Table3"}} & List.Skip(Table.ToColumns(AddedColumns)), {"Table Name"} & List.Skip(Table.ColumnNames(AddedColumns)))
in
    Result