Referring to column by index position when using Group By

[Desyn]

I'm really stuck with this one, I'd massively appreciate any help.

I have a table with employees in column 0, and then data for each week in each subsequent column. These columnns are named with the date, as shown below:

Employee	2023 24-Nov	2023 01-Dec	2023 08-Dec	2023 15-Dec
Jack	2	2	8	4
Jack	7	9	8	1
Jack	1	3	5	1
Amy	7	2	9	1
Amy	5	7	2	3
Amy	5	5	4	6

I need to produce a table with a single row for each employee, and the sum of each week for that employee in the same row, as shown below:

Employee	Week 1	Week 2	Week 3	Week 4
Jack	10	14	21	6
Amy	17	14	15	10

I believe Group By is the best function for this. If I use the function (just for the first week to begin with), it works. The code looks like this:

= Table.Group(#"Changed Type", {"Employee"}, {{"Week 1", each List.Sum([#"2023 24-Nov"]), type nullable number}})

The thing is, I don't want to refer to the date columns by their name, as they will change each week when a new report is read in.

I have tried using the following code to produce this table using column index position (for just the first week to begin with), but it doesn't work.

Table.Group(#"Changed Type", {"Employee"}, {{"Week 1", each List.Sum(Table.ColumnNames(#"Changed Type"){1}), type nullable number}})

I get the following error:

If anyone could help that would be great!

[TheoC · Accepted solution]

Hi @Desyn 

You will need to do a bit of transformation before attempting to do what you are doing:

1. Unpivot all date columns so that there are two values in your table (Dates and Values) by selecting Employee column and "unpivot other columns".

2. Then use Group by sum values. 

That should give you the output you are after (hopefully).

Theo 🙂