Group by - refer to column by position

[Desyn]

I have the following:

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

But instead of referring to column 2023 24-Nov, I want to refer to it by it's position (column 3). The previous table is named Changed Type, so I tried to write the code as follows, but it doesn't work:

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

Any help would be great, thanks!

[Anonymous]

Hi @Desyn ,

If I understand correctly, your issue is that you want to group column by the position. To solve the issue, you can follow these steps:

1.You can use the Table.ColumnNames function to return a column name by position. This function is zero-indexed, so the first column has an index of 0.

ColumnName = Table.ColumnNames(#"Changed Type"){2}

2.If you have the column name, you can use the column name in the List Sum function.

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

3.Apply the Table Group function with the updated column reference.

4.After you try the approach above, if it doesn't wok,  you can provide the sample data and screenshots of the desired results.

Looking forward to your replay.

[Desyn]

Hi @Anonymous,

You are correct, I want to group a column by it's position, as opposed to it's name.

So the example you give here:

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

I want to do the same, but replace the part that refers to the column by it's name, with a part that refers to it by it's position.

I assumed I could use the code you show above, to produce this:

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

However, I get the following error:

[Anonymous]

Hi @Desyn ,

To solve the issue, you can try the following steps:

1.You can use the Table.Column function to reference a column by its positon.

2.You change the expression to the following formats: 

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

If the above one can't help you, could you please provide more raw data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples? It would be helpful to find out the solution. You can refer the following links to share the required info: How to provide sample data in the Power BI Forum - Microsoft Fabric Community

Looking forward to your reply.

[Desyn]

Hi @Anonymous 

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:

Thanks, Desyn.

[Anonymous · Accepted solution]

Hi @Desyn ,

I am afraid that there is no elegant way to implement it. Anyway, you can use the following method:

1.Create a simple table, select transform data.

2.Select a Table.

3.Right-click Table, and select Advanced Editor.

4.In the Advanced Editor, modify the content to the following, and select Ok.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8kpMzlbSUTKCYgsgNlGK1YFLmAOxJVTCEFnCEIiNgdgULuGYWwnVYATVhCxuiiRnjCZuCrZVR8lMKTYWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Employee = _t, #"2023 24-Nov" = _t, #"2023 01-Dec" = _t, #"2023 08-Dec" = _t, #"2023 15-Dec" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Employee", type text}, {"2023 24-Nov", Int64.Type}, {"2023 01-Dec", Int64.Type}, {"2023 08-Dec", Int64.Type}, {"2023 15-Dec", Int64.Type}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Employee"}, "Date", "Value"),
    #"Grouped Rows" = Table.Group(#"Unpivoted Other Columns", {"Employee", "Date"}, {{"Values", each List.Sum([Value]), type number}}),
    #"Pivoted Column" = Table.Pivot(#"Grouped Rows", List.Distinct(#"Grouped Rows"[Date]), "Date", "Values", List.Sum),
    #"Renamed Columns" = Table.RenameColumns(#"Pivoted Column",{{"2023 24-Nov", "Week 1"}, {"2023 01-Dec", "Week 2"}, {"2023 08-Dec", "Week 3"}, {"2023 15-Dec", "Week 4"}})
in
    #"Renamed Columns"

5.You can see the desired outcome.

Looking forward to your reply.

Best Regards,

Wisdom Wu