Forum Discussion
Solved
Calculate sum to Row for each column.
Hi,
I want to create a new column called total to calculate sum of all the values in current row.
Below is the sample table.
Total is the sum of all the rows in that particular row for ex for ABC employee it will be sum of all these numbers which will sum to 145 .
| 123 | ABC | 10 | 20 | 30 | 10 | 10 | 30 | 11 | 12 | 145 |
Basically i want to elimitate the rows for which sub totals are summing to zero , which is EMP 125 and filter out his data from table. Is there any way we can do this.
| EMP ID | Name | 06-06-2023 | 07-06-2023 | 08-06-2023 | 09-06-2023 | 10-06-2023 | 11-06-2023 | 12-06-2023 | 13-06-2023 | Total |
| 123 | ABC | 10 | 20 | 30 | 10 | 10 | 30 | 11 | 12 | 145 |
| 124 | asd | 11 | 21 | 31 | 11 | 11 | 31 | 12 | 13 | 154 |
| 125 | wed | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
| 126 | adr | 13 | 23 | 33 | 13 | 13 | 33 | 14 | 15 | 172 |
| 127 | ass | 14 | 24 | 34 | 14 | 14 | 34 | 15 | 16 | 181 |
| 128 | wer | 15 | 25 | 35 | 15 | 15 | 35 | 16 | 17 | 190 |
| 129 | www | 16 | 26 | 36 | 16 | 16 | 36 | 17 | 18 | 199 |
| 130 | qqq | 17 | 27 | 37 | 17 | 17 | 37 | 18 | 19 | 208 |
Thanks,
Binay
Hi binayjethwa
Here's the query
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlLSUTIGYhMgNoXSxlC2OZQGYTMoNkdSC+JbQMUsgRjdLHOoGmMkdUPLvlid6GHtQ0rsNAHj2FgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Resource GPN" = _t, #"*Resource name" = _t, #"*Hire date" = _t, #"Employee type" = _t, #"Current/Future Rank" = _t, #"Current/Future Grade" = _t, #"*Home city" = _t, #"*Managerial Location" = _t, #"*Sub-service line" = _t, #"*Sub-management unit" = _t, #"*Client name" = _t, #"Client Number" = _t, #"*Engagement name" = _t, #"Engagement number" = _t, #"*Business unit" = _t, #"Booking type" = _t, #"Notes - Public" = _t, #"Time (Hours) 19 Jun 2023" = _t, #"Time (Hours) 26 Jun 2023" = _t, #"Time (Hours) 3 Jul 2023" = _t, #"Time (Hours) 10 Jul 2023" = _t, #"Time (Hours) 17 Jul 2023" = _t, #"Time (Hours) 24 Jul 2023" = _t, #"Time (Hours) 31 Jul 2023" = _t, #"Time (Hours) 7 Aug 2023" = _t, #"Time (Hours) 14 Aug 2023" = _t, #"Time (Hours) 21 Aug 2023" = _t, #"Time (Hours) 28 Aug 2023" = _t, #"Time (Hours) 4 Sep 2023" = _t, #"Time (Hours) 11 Sep 2023" = _t, #"Time (Hours) 18 Sep 2023" = _t, #"Time (Hours) 25 Sep 2023" = _t, #"Time (Hours) 2 Oct 2023" = _t, #"Time (Hours) 9 Oct 2023" = _t, #"Time (Hours) 16 Oct 2023" = _t, #"Time (Hours) 23 Oct 2023" = _t, #"Time (Hours) 30 Oct 2023" = _t, #"Time (Hours) 6 Nov 2023" = _t, #"Time (Hours) 13 Nov 2023" = _t, #"Time (Hours) 20 Nov 2023" = _t, #"Time (Hours) 27 Nov 2023" = _t, #"Time (Hours) 4 Dec 2023" = _t, #"Time (Hours) 11 Dec 2023" = _t, #"Time (Hours) 18 Dec 2023" = _t, #"Time (Hours) 25 Dec 2023" = _t, #"Time (Hours) 1 Jan 2024" = _t, #"Time (Hours) 8 Jan 2024" = _t, #"Time (Hours) 15 Jan 2024" = _t, #"Time (Hours) 22 Jan 2024" = _t, #"Time (Hours) 29 Jan 2024" = _t, #"Time (Hours) 5 Feb 2024" = _t, #"Time (Hours) 12 Feb 2024" = _t, #"Time (Hours) 19 Feb 2024" = _t, #"Time (Hours) 26 Feb 2024" = _t, #"Time (Hours) 4 Mar 2024" = _t, #"Time (Hours) 11 Mar 2024" = _t, #"Time (Hours) 18 Mar 2024" = _t, #"Time (Hours) 25 Mar 2024" = _t, #"Time (Hours) 1 Apr 2024" = _t, #"Time (Hours) 8 Apr 2024" = _t, #"Time (Hours) 15 Apr 2024" = _t, #"Time (Hours) 22 Apr 2024" = _t, #"Time (Hours) 29 Apr 2024" = _t, #"Time (Hours) 6 May 2024" = _t, #"Time (Hours) 13 May 2024" = _t, #"Time (Hours) 20 May 2024" = _t, #"Time (Hours) 27 May 2024" = _t, #"Time (Hours) 3 Jun 2024" = _t, #"Time (Hours) 10 Jun 2024" = _t, #"Time (Hours) 17 Jun 2024" = _t, #"Time (Hours) 24 Jun 2024" = _t, Source = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Resource GPN", Int64.Type}, {"*Resource name", Int64.Type}, {"*Hire date", Int64.Type}, {"Employee type", Int64.Type}, {"Current/Future Rank", Int64.Type}, {"Current/Future Grade", Int64.Type}, {"*Home city", Int64.Type}, {"*Managerial Location", Int64.Type}, {"*Sub-service line", Int64.Type}, {"*Sub-management unit", Int64.Type}, {"*Client name", Int64.Type}, {"Client Number", Int64.Type}, {"*Engagement name", Int64.Type}, {"Engagement number", Int64.Type}, {"*Business unit", Int64.Type}, {"Booking type", Int64.Type}, {"Notes - Public", Int64.Type}, {"Time (Hours) 19 Jun 2023", Int64.Type}, {"Time (Hours) 26 Jun 2023", Int64.Type}, {"Time (Hours) 3 Jul 2023", Int64.Type}, {"Time (Hours) 10 Jul 2023", Int64.Type}, {"Time (Hours) 17 Jul 2023", Int64.Type}, {"Time (Hours) 24 Jul 2023", Int64.Type}, {"Time (Hours) 31 Jul 2023", Int64.Type}, {"Time (Hours) 7 Aug 2023", Int64.Type}, {"Time (Hours) 14 Aug 2023", Int64.Type}, {"Time (Hours) 21 Aug 2023", Int64.Type}, {"Time (Hours) 28 Aug 2023", Int64.Type}, {"Time (Hours) 4 Sep 2023", Int64.Type}, {"Time (Hours) 11 Sep 2023", Int64.Type}, {"Time (Hours) 18 Sep 2023", Int64.Type}, {"Time (Hours) 25 Sep 2023", Int64.Type}, {"Time (Hours) 2 Oct 2023", Int64.Type}, {"Time (Hours) 9 Oct 2023", Int64.Type}, {"Time (Hours) 16 Oct 2023", Int64.Type}, {"Time (Hours) 23 Oct 2023", Int64.Type}, {"Time (Hours) 30 Oct 2023", Int64.Type}, {"Time (Hours) 6 Nov 2023", Int64.Type}, {"Time (Hours) 13 Nov 2023", Int64.Type}, {"Time (Hours) 20 Nov 2023", Int64.Type}, {"Time (Hours) 27 Nov 2023", Int64.Type}, {"Time (Hours) 4 Dec 2023", Int64.Type}, {"Time (Hours) 11 Dec 2023", Int64.Type}, {"Time (Hours) 18 Dec 2023", Int64.Type}, {"Time (Hours) 25 Dec 2023", Int64.Type}, {"Time (Hours) 1 Jan 2024", Int64.Type}, {"Time (Hours) 8 Jan 2024", Int64.Type}, {"Time (Hours) 15 Jan 2024", Int64.Type}, {"Time (Hours) 22 Jan 2024", Int64.Type}, {"Time (Hours) 29 Jan 2024", Int64.Type}, {"Time (Hours) 5 Feb 2024", Int64.Type}, {"Time (Hours) 12 Feb 2024", Int64.Type}, {"Time (Hours) 19 Feb 2024", Int64.Type}, {"Time (Hours) 26 Feb 2024", Int64.Type}, {"Time (Hours) 4 Mar 2024", Int64.Type}, {"Time (Hours) 11 Mar 2024", Int64.Type}, {"Time (Hours) 18 Mar 2024", Int64.Type}, {"Time (Hours) 25 Mar 2024", Int64.Type}, {"Time (Hours) 1 Apr 2024", Int64.Type}, {"Time (Hours) 8 Apr 2024", Int64.Type}, {"Time (Hours) 15 Apr 2024", Int64.Type}, {"Time (Hours) 22 Apr 2024", Int64.Type}, {"Time (Hours) 29 Apr 2024", Int64.Type}, {"Time (Hours) 6 May 2024", Int64.Type}, {"Time (Hours) 13 May 2024", Int64.Type}, {"Time (Hours) 20 May 2024", Int64.Type}, {"Time (Hours) 27 May 2024", Int64.Type}, {"Time (Hours) 3 Jun 2024", Int64.Type}, {"Time (Hours) 10 Jun 2024", Int64.Type}, {"Time (Hours) 17 Jun 2024", Int64.Type}, {"Time (Hours) 24 Jun 2024", Int64.Type}, {"Source", Int64.Type}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Table.ToRows(Table.RemoveColumns(#"Changed Type", {"Resource GPN" , "*Resource name", "*Hire date", "Employee type", "Current/Future Rank", "Current/Future Grade", "*Home city", "*Managerial Location", "*Sub-service line", "*Sub-management unit", "*Client name", "Client Number", "*Engagement name", "Engagement number", "*Business unit", "Booking type", "Notes - Public", "Source"}))), #"Added Index" = Table.AddIndexColumn(#"Added Custom", "Index", 0, 1, Int64.Type), #"Added Custom1" = Table.AddColumn(#"Added Index", "Total", each List.Sum([Custom]{[Index]})), #"Filtered Rows" = Table.SelectRows(#"Added Custom1", each ([Total] <> 0)), #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Custom", "Index"}) in #"Removed Columns"which operates on this data
The important part of the code for you are the steps from #"Added Custom" onwards.
The #"Added Custom" step removes the columns you don't want to sum up from the source table, and creates in each row lists containing the values you do want to sum up.
= Table.ToRows(Table.RemoveColumns(#"Changed Type", {"Resource GPN" , "*Resource name", "*Hire date", "Employee type", "Current/Future Rank", "Current/Future Grade", "*Home city", "*Managerial Location", "*Sub-service line", "*Sub-management unit", "*Client name", "Client Number", "*Engagement name", "Engagement number", "*Business unit", "Booking type", "Notes - Public", "Source"}))Then I add an Index column.
The next step sums the values from each row (stored in the aforementioned lists), that is, it sums up the values across the row only in the columns you want
= List.Sum([Custom]{[Index]})You then filter out any 0 totals and delete the Index and Custom columns created during the query that are no longer needed.
Regards
Phil
15 Replies
Hi binayjethwa
Here's the query
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlLSUTIGYhMgNoXSxlC2OZQGYTMoNkdSC+JbQMUsgRjdLHOoGmMkdUPLvlid6GHtQ0rsNAHj2FgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Resource GPN" = _t, #"*Resource name" = _t, #"*Hire date" = _t, #"Employee type" = _t, #"Current/Future Rank" = _t, #"Current/Future Grade" = _t, #"*Home city" = _t, #"*Managerial Location" = _t, #"*Sub-service line" = _t, #"*Sub-management unit" = _t, #"*Client name" = _t, #"Client Number" = _t, #"*Engagement name" = _t, #"Engagement number" = _t, #"*Business unit" = _t, #"Booking type" = _t, #"Notes - Public" = _t, #"Time (Hours) 19 Jun 2023" = _t, #"Time (Hours) 26 Jun 2023" = _t, #"Time (Hours) 3 Jul 2023" = _t, #"Time (Hours) 10 Jul 2023" = _t, #"Time (Hours) 17 Jul 2023" = _t, #"Time (Hours) 24 Jul 2023" = _t, #"Time (Hours) 31 Jul 2023" = _t, #"Time (Hours) 7 Aug 2023" = _t, #"Time (Hours) 14 Aug 2023" = _t, #"Time (Hours) 21 Aug 2023" = _t, #"Time (Hours) 28 Aug 2023" = _t, #"Time (Hours) 4 Sep 2023" = _t, #"Time (Hours) 11 Sep 2023" = _t, #"Time (Hours) 18 Sep 2023" = _t, #"Time (Hours) 25 Sep 2023" = _t, #"Time (Hours) 2 Oct 2023" = _t, #"Time (Hours) 9 Oct 2023" = _t, #"Time (Hours) 16 Oct 2023" = _t, #"Time (Hours) 23 Oct 2023" = _t, #"Time (Hours) 30 Oct 2023" = _t, #"Time (Hours) 6 Nov 2023" = _t, #"Time (Hours) 13 Nov 2023" = _t, #"Time (Hours) 20 Nov 2023" = _t, #"Time (Hours) 27 Nov 2023" = _t, #"Time (Hours) 4 Dec 2023" = _t, #"Time (Hours) 11 Dec 2023" = _t, #"Time (Hours) 18 Dec 2023" = _t, #"Time (Hours) 25 Dec 2023" = _t, #"Time (Hours) 1 Jan 2024" = _t, #"Time (Hours) 8 Jan 2024" = _t, #"Time (Hours) 15 Jan 2024" = _t, #"Time (Hours) 22 Jan 2024" = _t, #"Time (Hours) 29 Jan 2024" = _t, #"Time (Hours) 5 Feb 2024" = _t, #"Time (Hours) 12 Feb 2024" = _t, #"Time (Hours) 19 Feb 2024" = _t, #"Time (Hours) 26 Feb 2024" = _t, #"Time (Hours) 4 Mar 2024" = _t, #"Time (Hours) 11 Mar 2024" = _t, #"Time (Hours) 18 Mar 2024" = _t, #"Time (Hours) 25 Mar 2024" = _t, #"Time (Hours) 1 Apr 2024" = _t, #"Time (Hours) 8 Apr 2024" = _t, #"Time (Hours) 15 Apr 2024" = _t, #"Time (Hours) 22 Apr 2024" = _t, #"Time (Hours) 29 Apr 2024" = _t, #"Time (Hours) 6 May 2024" = _t, #"Time (Hours) 13 May 2024" = _t, #"Time (Hours) 20 May 2024" = _t, #"Time (Hours) 27 May 2024" = _t, #"Time (Hours) 3 Jun 2024" = _t, #"Time (Hours) 10 Jun 2024" = _t, #"Time (Hours) 17 Jun 2024" = _t, #"Time (Hours) 24 Jun 2024" = _t, Source = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Resource GPN", Int64.Type}, {"*Resource name", Int64.Type}, {"*Hire date", Int64.Type}, {"Employee type", Int64.Type}, {"Current/Future Rank", Int64.Type}, {"Current/Future Grade", Int64.Type}, {"*Home city", Int64.Type}, {"*Managerial Location", Int64.Type}, {"*Sub-service line", Int64.Type}, {"*Sub-management unit", Int64.Type}, {"*Client name", Int64.Type}, {"Client Number", Int64.Type}, {"*Engagement name", Int64.Type}, {"Engagement number", Int64.Type}, {"*Business unit", Int64.Type}, {"Booking type", Int64.Type}, {"Notes - Public", Int64.Type}, {"Time (Hours) 19 Jun 2023", Int64.Type}, {"Time (Hours) 26 Jun 2023", Int64.Type}, {"Time (Hours) 3 Jul 2023", Int64.Type}, {"Time (Hours) 10 Jul 2023", Int64.Type}, {"Time (Hours) 17 Jul 2023", Int64.Type}, {"Time (Hours) 24 Jul 2023", Int64.Type}, {"Time (Hours) 31 Jul 2023", Int64.Type}, {"Time (Hours) 7 Aug 2023", Int64.Type}, {"Time (Hours) 14 Aug 2023", Int64.Type}, {"Time (Hours) 21 Aug 2023", Int64.Type}, {"Time (Hours) 28 Aug 2023", Int64.Type}, {"Time (Hours) 4 Sep 2023", Int64.Type}, {"Time (Hours) 11 Sep 2023", Int64.Type}, {"Time (Hours) 18 Sep 2023", Int64.Type}, {"Time (Hours) 25 Sep 2023", Int64.Type}, {"Time (Hours) 2 Oct 2023", Int64.Type}, {"Time (Hours) 9 Oct 2023", Int64.Type}, {"Time (Hours) 16 Oct 2023", Int64.Type}, {"Time (Hours) 23 Oct 2023", Int64.Type}, {"Time (Hours) 30 Oct 2023", Int64.Type}, {"Time (Hours) 6 Nov 2023", Int64.Type}, {"Time (Hours) 13 Nov 2023", Int64.Type}, {"Time (Hours) 20 Nov 2023", Int64.Type}, {"Time (Hours) 27 Nov 2023", Int64.Type}, {"Time (Hours) 4 Dec 2023", Int64.Type}, {"Time (Hours) 11 Dec 2023", Int64.Type}, {"Time (Hours) 18 Dec 2023", Int64.Type}, {"Time (Hours) 25 Dec 2023", Int64.Type}, {"Time (Hours) 1 Jan 2024", Int64.Type}, {"Time (Hours) 8 Jan 2024", Int64.Type}, {"Time (Hours) 15 Jan 2024", Int64.Type}, {"Time (Hours) 22 Jan 2024", Int64.Type}, {"Time (Hours) 29 Jan 2024", Int64.Type}, {"Time (Hours) 5 Feb 2024", Int64.Type}, {"Time (Hours) 12 Feb 2024", Int64.Type}, {"Time (Hours) 19 Feb 2024", Int64.Type}, {"Time (Hours) 26 Feb 2024", Int64.Type}, {"Time (Hours) 4 Mar 2024", Int64.Type}, {"Time (Hours) 11 Mar 2024", Int64.Type}, {"Time (Hours) 18 Mar 2024", Int64.Type}, {"Time (Hours) 25 Mar 2024", Int64.Type}, {"Time (Hours) 1 Apr 2024", Int64.Type}, {"Time (Hours) 8 Apr 2024", Int64.Type}, {"Time (Hours) 15 Apr 2024", Int64.Type}, {"Time (Hours) 22 Apr 2024", Int64.Type}, {"Time (Hours) 29 Apr 2024", Int64.Type}, {"Time (Hours) 6 May 2024", Int64.Type}, {"Time (Hours) 13 May 2024", Int64.Type}, {"Time (Hours) 20 May 2024", Int64.Type}, {"Time (Hours) 27 May 2024", Int64.Type}, {"Time (Hours) 3 Jun 2024", Int64.Type}, {"Time (Hours) 10 Jun 2024", Int64.Type}, {"Time (Hours) 17 Jun 2024", Int64.Type}, {"Time (Hours) 24 Jun 2024", Int64.Type}, {"Source", Int64.Type}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Table.ToRows(Table.RemoveColumns(#"Changed Type", {"Resource GPN" , "*Resource name", "*Hire date", "Employee type", "Current/Future Rank", "Current/Future Grade", "*Home city", "*Managerial Location", "*Sub-service line", "*Sub-management unit", "*Client name", "Client Number", "*Engagement name", "Engagement number", "*Business unit", "Booking type", "Notes - Public", "Source"}))), #"Added Index" = Table.AddIndexColumn(#"Added Custom", "Index", 0, 1, Int64.Type), #"Added Custom1" = Table.AddColumn(#"Added Index", "Total", each List.Sum([Custom]{[Index]})), #"Filtered Rows" = Table.SelectRows(#"Added Custom1", each ([Total] <> 0)), #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Custom", "Index"}) in #"Removed Columns"which operates on this data
The important part of the code for you are the steps from #"Added Custom" onwards.
The #"Added Custom" step removes the columns you don't want to sum up from the source table, and creates in each row lists containing the values you do want to sum up.
= Table.ToRows(Table.RemoveColumns(#"Changed Type", {"Resource GPN" , "*Resource name", "*Hire date", "Employee type", "Current/Future Rank", "Current/Future Grade", "*Home city", "*Managerial Location", "*Sub-service line", "*Sub-management unit", "*Client name", "Client Number", "*Engagement name", "Engagement number", "*Business unit", "Booking type", "Notes - Public", "Source"}))Then I add an Index column.
The next step sums the values from each row (stored in the aforementioned lists), that is, it sums up the values across the row only in the columns you want
= List.Sum([Custom]{[Index]})You then filter out any 0 totals and delete the Index and Custom columns created during the query that are no longer needed.
Regards
Phil
Hi binayjethwa
If you use Table.ToRows you can isolate the values for each row and then sum them. The row with total 0 can then be filtered out.
Here's the query
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fZAxDoQwDAT/kpoijkkC5d09A1GcxD0gUPD9yzpx3FGw0li7MGLbHAV2k3u9PzXJ1wgI9opkSIjg9gmrucL3OvQeEEyjZhgQ3Fexwv3Dyj8+rZ3wjeNsL6jfQDArkiFkKPZVFrNL72LK86gZQoZSXy1idupdTDmOmiGkKPfVitV9612MOY2aIaRoaSv5m6UUvYsx51EzhBStbt//", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"EMP ID" = _t, Name = _t, #"06-06-2023" = _t, #"07-06-2023" = _t, #"08-06-2023" = _t, #"09-06-2023" = _t, #"10-06-2023" = _t, #"11-06-2023" = _t, #"12-06-2023" = _t, #"13-06-2023" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"EMP ID", Int64.Type}, {"Name", type text}, {"06-06-2023", Int64.Type}, {"07-06-2023", Int64.Type}, {"08-06-2023", Int64.Type}, {"09-06-2023", Int64.Type}, {"10-06-2023", Int64.Type}, {"11-06-2023", Int64.Type}, {"12-06-2023", Int64.Type}, {"13-06-2023", Int64.Type}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Table.ToRows(#"Changed Type")), #"Added Index" = Table.AddIndexColumn(#"Added Custom", "Index", 0, 1, Int64.Type), #"Added Custom1" = Table.AddColumn(#"Added Index", "Total", each List.Sum(List.RemoveFirstN([Custom]{[Index]}, 2))), #"Filtered Rows" = Table.SelectRows(#"Added Custom1", each ([Total] <> 0)), #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Custom", "Index"}) in #"Removed Columns"Regards
Phil
Hi PhilipTreacy ,
I do not have any totals column in my table , I have created it manually.
I tried to to a SUM based on add column using standard power query functions but my column names might change if year is changed.
I have also tried unpivot columns but my data is bloating so much hence its not a good option for me.
Is there any other way we can achieve.
Thanks,
Binay
Hi binayjethwa
Have you tried my code? It creates a total from the numbers in the row.
Have you had problems adapting it to your situation?
Regards
Phil
Hi PhilipTreacy ,
I see below error for totals column , i have other fields in my table also.
Any suggestion ?
Hi binayjethwa
Can you please show all the columns in the table so I can leave out the ones that don't need to be summed up.
Regards
Phil
I have many columns in my data around 50 , but i only want columns with date name( Which are hours actually) as mentioned in above to be summed up.
Hi PhilipTreacy
I have many columns in my data around 50 , but i only want columns with date name( Which are hours actually) as mentioned in above to be summed up.
How will I know what the date names are?
Please help me to help you, post your data in a file. Just the column headers will be fine.
Phil
Hi PhilipTreacy ,
below would be my column names . which need to be summed up .
Time (Hours) 17 Jun 2023 - 23 Jun 2023 Time (Hours) 24 Jun 2023 - 30 Jun 2023 Time (Hours) 1 Jul 2023 - 7 Jul 2023 Time (Hours) 8 Jul 2023 - 14 Jul 2023 Time (Hours) 15 Jul 2023 - 21 Jul 2023 Time (Hours) 22 Jul 2023 - 28 Jul 2023 Time (Hours) 29 Jul 2023 - 4 Aug 2023 Time (Hours) 5 Aug 2023 - 11 Aug 2023 Time (Hours) 12 Aug 2023 - 18 Aug 2023 Time (Hours) 19 Aug 2023 - 25 Aug 2023 Time (Hours) 26 Aug 2023 - 1 Sep 2023 Time (Hours) 2 Sep 2023 - 8 Sep 2023 Time (Hours) 9 Sep 2023 - 15 Sep 2023 Time (Hours) 16 Sep 2023 - 22 Sep 2023 Time (Hours) 23 Sep 2023 - 29 Sep 2023 Time (Hours) 30 Sep 2023 - 6 Oct 2023 Time (Hours) 7 Oct 2023 - 13 Oct 2023 Time (Hours) 14 Oct 2023 - 20 Oct 2023 Time (Hours) 21 Oct 2023 - 27 Oct 2023 Time (Hours) 28 Oct 2023 - 3 Nov 2023 Time (Hours) 4 Nov 2023 - 10 Nov 2023 Time (Hours) 11 Nov 2023 - 17 Nov 2023 Time (Hours) 18 Nov 2023 - 24 Nov 2023 Time (Hours) 25 Nov 2023 - 1 Dec 2023 Time (Hours) 2 Dec 2023 - 8 Dec 2023 Time (Hours) 9 Dec 2023 - 15 Dec 2023 Time (Hours) 16 Dec 2023 - 22 Dec 2023 Time (Hours) 23 Dec 2023 - 29 Dec 2023 Time (Hours) 30 Dec 2023 - 5 Jan 2024 Time (Hours) 6 Jan 2024 - 12 Jan 2024 Time (Hours) 13 Jan 2024 - 19 Jan 2024 Time (Hours) 20 Jan 2024 - 26 Jan 2024 Time (Hours) 27 Jan 2024 - 2 Feb 2024 Time (Hours) 3 Feb 2024 - 9 Feb 2024 Time (Hours) 10 Feb 2024 - 16 Feb 2024 Time (Hours) 17 Feb 2024 - 23 Feb 2024 Time (Hours) 24 Feb 2024 - 1 Mar 2024 Time (Hours) 2 Mar 2024 - 8 Mar 2024 Time (Hours) 9 Mar 2024 - 15 Mar 2024 Time (Hours) 16 Mar 2024 - 22 Mar 2024 Time (Hours) 23 Mar 2024 - 29 Mar 2024 Time (Hours) 30 Mar 2024 - 5 Apr 2024 Time (Hours) 6 Apr 2024 - 12 Apr 2024 Time (Hours) 13 Apr 2024 - 19 Apr 2024 Time (Hours) 20 Apr 2024 - 26 Apr 2024 Time (Hours) 27 Apr 2024 - 3 May 2024 Time (Hours) 4 May 2024 - 10 May 2024 Time (Hours) 11 May 2024 - 17 May 2024 Time (Hours) 18 May 2024 - 24 May 2024 Time (Hours) 25 May 2024 - 31 May 2024 Time (Hours) 1 Jun 2024 - 7 Jun 2024 Time (Hours) 8 Jun 2024 - 14 Jun 2024 Time (Hours) 15 Jun 2024 - 21 Jun 2024 Time (Hours) 22 Jun 2024 - 28 Jun 2024 All the columns please.
You said the column names might change if the year changes, so these column names are no good in a query if explicitly named.
I need to know the names of the other columns that do not chnage so that they can be excluded from the SUM operation.
Whatever is left can be summed. This way the names of the columns being summed are irrelevant.
Phil
hi PhilipTreacy ,
below is the list of column names,
Resource GPN *Resource name *Hire date Employee type Current/Future Rank Current/Future Grade *Home city *Managerial Location *Sub-service line *Sub-management unit *Client name Client Number *Engagement name Engagement number *Business unit Booking type Notes - Public Time (Hours) 19 Jun 2023 Time (Hours) 26 Jun 2023 Time (Hours) 3 Jul 2023 Time (Hours) 10 Jul 2023 Time (Hours) 17 Jul 2023 Time (Hours) 24 Jul 2023 Time (Hours) 31 Jul 2023 Time (Hours) 7 Aug 2023 Time (Hours) 14 Aug 2023 Time (Hours) 21 Aug 2023 Time (Hours) 28 Aug 2023 Time (Hours) 4 Sep 2023 Time (Hours) 11 Sep 2023 Time (Hours) 18 Sep 2023 Time (Hours) 25 Sep 2023 Time (Hours) 2 Oct 2023 Time (Hours) 9 Oct 2023 Time (Hours) 16 Oct 2023 Time (Hours) 23 Oct 2023 Time (Hours) 30 Oct 2023 Time (Hours) 6 Nov 2023 Time (Hours) 13 Nov 2023 Time (Hours) 20 Nov 2023 Time (Hours) 27 Nov 2023 Time (Hours) 4 Dec 2023 Time (Hours) 11 Dec 2023 Time (Hours) 18 Dec 2023 Time (Hours) 25 Dec 2023 Time (Hours) 1 Jan 2024 Time (Hours) 8 Jan 2024 Time (Hours) 15 Jan 2024 Time (Hours) 22 Jan 2024 Time (Hours) 29 Jan 2024 Time (Hours) 5 Feb 2024 Time (Hours) 12 Feb 2024 Time (Hours) 19 Feb 2024 Time (Hours) 26 Feb 2024 Time (Hours) 4 Mar 2024 Time (Hours) 11 Mar 2024 Time (Hours) 18 Mar 2024 Time (Hours) 25 Mar 2024 Time (Hours) 1 Apr 2024 Time (Hours) 8 Apr 2024 Time (Hours) 15 Apr 2024 Time (Hours) 22 Apr 2024 Time (Hours) 29 Apr 2024 Time (Hours) 6 May 2024 Time (Hours) 13 May 2024 Time (Hours) 20 May 2024 Time (Hours) 27 May 2024 Time (Hours) 3 Jun 2024 Time (Hours) 10 Jun 2024 Time (Hours) 17 Jun 2024 Time (Hours) 24 Jun 2024 Source hi PhilipTreacy Any update ?
Hi,
This M code works
let Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content], #"Changed Type" = Table.TransformColumnTypes(Source,{{"EMP ID", Int64.Type}, {"Name", type text}, {"06-06-2023", Int64.Type}, {"07-06-2023", Int64.Type}, {"08-06-2023", Int64.Type}, {"09-06-2023", Int64.Type}, {"10-06-2023", Int64.Type}, {"11-06-2023", Int64.Type}, {"12-06-2023", Int64.Type}, {"13-06-2023", Int64.Type}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each List.Sum(List.Transform(List.Skip(Record.ToList(_),2), each Number.From(_)))), #"Filtered Rows" = Table.SelectRows(#"Added Custom", each [Custom] > 0) in #"Filtered Rows"Hope this helps.
After the initial post the OP clarified that there are lots more than just the first 2 columns that need to be omitted.
It took a while but my latest reply solves it.
Regards
Phil
