How to sum Columns based on list in power query

[Anonymous]

I want to consolidate monthly paysheets generated from SAP every month for different offices. The problem  with the  paysheets  is that it has many redundant columns which  needs to be added  and transformed into  a new column.  

To solve this problem I prepared One List of all columns reflecting in the consolidated paysheet after combine.  And seperate columns list to sum the columns e.g. OutOfAccount (used in the below query). 

Then using the List.Intersect function I removed the column names which are not in the All Column list and present in the OutOfAccount  list.

Then used following query to sum the columns available in the OutOfAccount List.

  Table.AddColumn(#"Removed Columns1","OutOfAccount", each List.Sum(Record.FieldValues(Record.SelectFields(_,OutOfAccount))), type number)

The query is useful for two to three columns but when I use it for more than 3 column names , the query is taking too much time. Please suggest an alternate solution.

This is the Paysheet -  https://1drv.ms/x/s!Al5DdavRiT-Ug60OeI4gp93c63zHrQ

And this is the list of coumns which if present in the paysheet needs to be added together according to the Category and subsequently deleted -  https://1drv.ms/x/s!Al5DdavRiT-Ug60P_5IA7cg4ubn7vg 

[lbendlin]

Can you provide a mapping table from all original columns to desired buckets?

[Anonymous]

This is the all columns mapping file.

https://1drv.ms/x/s!Al5DdavRiT-Ug65fMWkMAh1D_ymvEw 

[lbendlin]

Columns:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("rZrvbuO4EcBfhQhQIAE2znaBXtGPtmQ7BpLYa/u6H66HAy3RMW9l0qAkJ36be5Y+WYekJFP8Iym7/XC3Eec35IjkDIcj//bbzTeS7bEgaFayFEWcFYLuyoJydvOplt38/gk4XBCBogMWryRHa5LwMxEXgJ5oQlgOHZAazA+UvaJxlr2NQL45kYTiTD7zN8wSh9KtaCwEwQIU9B95hYkjijJeaDg9h3v8lYHRD+M85yCUL4A25S5PBAWNZVkgvkc4SXjJCoVvLyfyJqh8qapbu0VRMBtyJLBIv9B//2obcB3fRv0vtIU/81Jc+l7c5QB4huU5ZBe0whcNraNxpZ6j23m8uXN6wWeSZf3LASDLT1wUbbtGbn8/iPmsH0pRNugFGqx7Xm0u3OVa+QNlJS361sEh9ej24LwUD9sxeA897tSWa62POTaQaDseaRItsxStwQE7VV5WEZpg9l065yiw6UlGTgfOiDahFDk5EpB9CkqU1hiptdqDZ1R9+zZCExNqHfkOH+DXpKBCjfoRLc4M23qnVfLtgQao1EtoLeYY3Tfz0tPDvZqMXuhqGKCGlRPCyJ4Wcr81i7KBeE3EDrMex2hzfqfYlMdTUWIdbey+zK52Oc0LopyrZ9AWyUPjFmV6GeCJmwK/Mh3TFyxpZsh2wzY2UmPaI66eh4x3ymC247IwJ8RsarMjaUHgDStp11Ajbe3tRu70/M73YhLqCWxtpms8+8VCWMSPJzjZccHrY+gD/O0jzbK7Dp6QFK14XsiXh+WkZ2XzynisOKXdmiE5Ac7ba+66QIFZIhA79CQeVVTs9zMizpDhoLnAhQzvQzSSUsiDYMJZmssQPH5ZbWF6/DF5E082duKl2hKzTZLrUsArykmDf57onsC0QI6ApcZ69bTQEPxxncT7eJz/EU/zP+ZxBfllriaEoxklcO4s946mKfNqxksk7fHoVRKlRfPvA3asMdvD1wDEcM4TAUnmadgYFu7fPmuS01QFyo+YonRg4cA5ZGy6Xf2n/Pz5yy9buZH9A/hgT1xw6YjjLECOGgoGvJXnfcKPdsLocGtM4QS49HJ6V7oYRGsdkaOs3KFcZ+Snwu8Ka7KDJAfdLhgk4QRiA+g9cczu1CxLmcZiv/qqLO6X+70dpUOL7uL+RV+VjOYHMy9pt1zTkZXgf5Kk6ImtktKH8a0cvlIKx0oA9iP06ymVc6NEYVTHhjUsLt2rK11gpjQX8Xt+gniofHPDkx4YLU99CebqWW3Xf8obYkZJYwMcu2aDQp8WDyo8rMB5aHlEm9EWv5uhzBDKLoynWmpEllagC8s8mk0s27uahsyn2cS5jjC3mg/ZixB7OJOeHMokWgScriylso+ObaO90/R0OyqYyF1gVE3UYWAQ1DFSFSY6u7mGkkAvdULeio2enhouWo6fepl47EFU6lEFI6PmEZIorUiWD6rVNh6UTK5uIwysGwaXRBNIn5xL2gt5y09K7F7SlmBAQY/XzHwJVwotATeF1FhF4f6duCwO0D8EAwrHGsn9bq6hoZ39EpY/R2M5gvxHPe/3Mtd6hsMYLg+4vmiE9Q0e/rtmx9ZKvsBUDbv0v3zd9ITvl5WTrjlNiuPs/ivkCnKPpEhdPLz3SMmtBE4K6pho738f6kmGnzmk4AluClvyCM0RZmm99yqM5pBUbw9UQCYOdl5QTFIWCOzPJKUJdrekv93UuO73QeyoPl0s/baTPW0jxxRoa2TXQa3WUat0qXoxu+V5q7YZOuiNmieaQ86n83Ke3XwKiyy9G7dy+rSIjPPOfFJSguFombIEVxbNdPSb9aegjWozuc6bL/JMbpDWlvddoFXMk6e1Ps6DXtIchnK7t+90j+ux/4h7/DrAQR95mcvNYCzJ7WTzouK7Z9968F6ws7b5M5zHpQdjjAusJt4/dY08VLxpMoaO0DaImciILf+vnjAEj2t1PDT4XPDyVN9WYb42yQF2K9DRfLGpCJySOvux57IRemZmvpqhb7Q4pAK/YemATUOqGmpmRqvkaVh5TapU4bP5U7XHG/VdoaBFWZDO0s6sFAwoQcyvJh63nwlcpkg7M6ojkBw4IFA6q/busyasLU7/DNu4GuBys+dmde3Zn2Wcpw8xLO/roagnzNOoWPouD0KdiYAf1tMS2jQO73lPhTQnRVXg7IOCXvZT5LCR/09UV1ZvkR1ZuU3e+YOLhYXzcxsc2F9HhcNCQ+UNG9O5vENBrk7PrZTEaZLc9L0QOCYnLGQ4AKH6tBhIhT8OI0UXFT5+1fFoGo8hFt7/60sF3ssKE8UfKDRN35HWGYDC5oFsNZFVyu7gNH0yUw5/BtswdiY2jRG8E1rIGzIETvl+Xz7//bOSxXBzyvuK2hYUjFDxurnHBQpWVwKFal8m4i9ntYjA3dNkFu6d0hTXuzko96jTM9JVcDThcPb6txlssBLCZnXdQ//4myeeVEx1KfQG95jg4mAVcryjSWz4PgVeMJLXJSJdffBa2ObWOKOBQGahgc30I1BXoHXYcGR0UaMG3GtDV4R0YLnppNFDWU+S7+WGGLrwB16HC8VxF/zAy0TXlwmy/hyyEfeFo5/i7KzWpQaZ9sOQ+va9PYW+Z7fkjq2DhF2j+mVNrPP8eCUOWRpdkswqEvlWwYPp6QkeI1qjZ3EtqK8znRIbD0rGS/mjhqLAiT5Yb5fx7DlwhXXhLvZa0I8E5EOFLOWBi4RwxiATCCcszTddQO5hKjMUYSGo+sGDN2k3FSCjVHUKQUi4TA0KR7hJpejfOCuJHL+qzPYfJKC6owxUB37w0bw2LYagcSbVh+1OjTbq/2FPxPd7Qh62cA++Xmo8jZqV31GuizNSn7iANypxGjzQLEXTtByhGU6o/P0BWKB203Smr9/R41g9Vk/zx426Uz9uqufptygKLOx8KlOzDT5T9prXp7tuNYEPXpulKYZBcaDSE0W9XhYNWVDIOdEjZmnWVzn1kP6FlBx4xZlciBEx+mzQGh1vY0MDuhUEbfH3jqp1B+y3YsJFqsv2HfWdCWel3L36X92Ci77lajPhznFOk/A3rkbsWcGJLE8G6kNDZN4uUxRl9Fh9V+2YlHEpnZXrrw36oqSblk3TtZQ6Xq7Rizz08DveqdSlg2WstEoNAQuqyn1dG2o/ayLNkF1O7OgPTKL6gyLE287PhC5753Ueh+soPbisL8G0KPV1sM6Fnd9OdMH9Fkhcp8P93QbSXA8ZDewxULHwkXdDpsnvXy5nj/n7/wA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Column Name" = _t, Category = _t]),
    #"Removed Duplicates" = Table.Distinct(Source, {"Column Name"})
in
    #"Removed Duplicates"

Paysheet:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("7ZdNcsMwCIXv4jXTAQECnSWT+1+juLUcxz8ZV1baTVfWZDRIeh88yO02kEoaYMAPxM2HCySc1hlylmlJ6XtRQNCnrZBxN8b5j0Y4l2sxLn7S1TeswgH+PCAlIKTGE6kAZdvqKQhMX7/f4TaYux1FkPIg6SCcVsyJoKB0g24CJP43tPMDzwyegM3ecJag1pMmOrvbHNi5lX0GI93KqhYYubJn5HJ8ySjoWRzPvmJfbaFDrVPk5h8Vea9al/JMKgqX5ce5Ex6R2p8AOFf7QlGONc3EVUo+ijDurDooMK+JGyjnbtQlDMXziX1APj3L6k28+k8UrdZwDSUv+fkCnS1/DZf0uXZsd18B5FYTJIWskyGrQKkHjs27+JwElg/bfF6UPSGkTd2TQORQtzTwEiZ7Ig3e8dEdzzfg3Gq6L8mAyYlGPgJsloMx5rIdXdWjGeRKv2jhQ0UWU4qDS02KGX5aGEuHhj9aSroWoxkI7tCPjGDtEp5BX6pju8dcbABe6S+F1ZjWLVX6Fun9T39Ie7XP4Npn3vt9+qxAVcylsJKqn3/RxySH9PFxaQd13dKnNL/vOn2NueQd0/WZ2t+jP07JfZzfN3/1LMhyzbyXKYBguTSeqxr9eg7/kDeCTqPH/f4J", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Employee Id" = _t, #"Dearness Allowance" = _t, #"DA on TpA" = _t, #"Subsistence Allw." = _t, #"Basic Pay" = _t, #"Dearness Allowance.1" = _t, #"DA on TpA.1" = _t, #"House Rent Allowance" = _t, #"Transport Allowance" = _t, #"Spl. Allowance" = _t, #"Cash Handling Allowance" = _t, #"Care Taking Allowance" = _t, #"Spl. Pay" = _t, #"Training Allowance" = _t, CEA = _t, #"Tour/TA Reimb." = _t, #"Split Duty Allw." = _t, #"Total Earnings" = _t, #"Dearness Allowance Arr." = _t, #"House Rent Allowance Arr." = _t, #"Transport Allowance Arr." = _t, #"DA on TpA Arr." = _t, #"Basic Pay Arrear" = _t, #"Dearness Allowance Arrear" = _t, #"Band Pay Arr." = _t, #"House Rent Allowance Arr..1" = _t, #"Transport Allowance Arr..1" = _t, #"Spl. Allowance Arrear" = _t, #"DA Arrears" = _t, #"HRA Arrear" = _t, #"DA on TpA Arrears" = _t, #"TA Arrears" = _t, #"Band Pay Arr..1" = _t, #"Total Arrears" = _t, #"GPF Subscription" = _t, #"Prof. Tax" = _t, #"Income Tax" = _t, #"Claim from previous month" = _t, #"BF NPS Arrears" = _t, #"CF PF Monthly" = _t, #"Ptax Deduction Carry fwd" = _t, #"House Rent Recovery" = _t, #"Court Attachment (ODFM)" = _t, #"Punishment Rec." = _t, #"License Fee" = _t, #"Audit office Rec" = _t, #"Cooperative Credit Soc Re" = _t, #"AOR Non Taxable" = _t, #"PLI Premium" = _t, #"Recreation Club subscript" = _t, #"Union/Association Subscri" = _t, #"Welfare Fund Contribution" = _t, Connections = _t, #"Other Societies" = _t, #"OMCA (R)" = _t, #"PC Adv. (R)" = _t, #"PC Adv. Interest (R)" = _t, #"HBA Principle (R)" = _t, #"HBA Interest (R)" = _t, #"GPF Adv. (R)" = _t, #"OMCA Interest (R)" = _t, #"Total Deductions" = _t, #"Total Gross Amount" = _t, #"Net Pay" = _t]),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Employee Id"}, "Attribute", "Value"),
    #"Replaced Value" = Table.ReplaceValue(#"Unpivoted Other Columns",".1","",Replacer.ReplaceText,{"Attribute"}),
    #"Renamed Columns" = Table.RenameColumns(#"Replaced Value",{{"Attribute", "Column"}}),
    #"Merged Queries" = Table.NestedJoin(#"Renamed Columns", {"Column"}, Columns, {"Column Name"}, "Columns", JoinKind.LeftOuter),
    #"Expanded Columns" = Table.ExpandTableColumn(#"Merged Queries", "Columns", {"Category"}, {"Category"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Expanded Columns",null,"Unmapped",Replacer.ReplaceValue,{"Category"})
in
    #"Replaced Value1"

[Anonymous]

Thanks a lot sir 🙏🙏

[lbendlin]

The column names don't match exactly.  For example the sheet says "Dearness Allowance"  but the lookup table says "Dearness Allowance Arr."  .  Are these the same columns?

[Anonymous]

Spoiler

No.   Not same. 

[lbendlin]

Please provide sanitized sample data that fully covers your issue.
Please show the expected outcome based on the sample data you provided.

[Anonymous]

This is the original paysheet, 

Employee Id	Dearness Allowance	DA on TpA	Subsistence Allw.	Basic Pay	Dearness Allowance	DA on TpA	House Rent Allowance	Transport Allowance	Spl. Allowance	Cash Handling Allowance	Care Taking Allowance	Spl. Pay	Training Allowance	CEA	Tour/TA Reimb.	Split Duty Allw.	Total Earnings	Dearness Allowance Arr.	House Rent Allowance Arr.	Transport Allowance Arr.	DA on TpA Arr.	Basic Pay Arrear	Dearness Allowance Arrear	Band Pay Arr.	House Rent Allowance Arr.	Transport Allowance Arr.	Spl. Allowance Arrear	DA Arrears	HRA Arrear	DA on TpA Arrears	TA Arrears	Band Pay Arr.	Total Arrears	GPF Subscription	Prof. Tax	Income Tax	Claim from previous month	BF NPS Arrears	CF PF Monthly	Ptax Deduction Carry fwd	House Rent Recovery	Court Attachment (ODFM)	Punishment Rec.	License Fee	Audit office Rec	Cooperative Credit Soc Re	AOR Non Taxable	PLI Premium	Recreation Club subscript	Union/Association Subscri	Welfare Fund Contribution	Connections	Other Societies	OMCA (R)	PC Adv. (R)	PC Adv. Interest (R)	HBA Principle (R)	HBA Interest (R)	GPF Adv. (R)	OMCA Interest (R)	Total Deductions	Total Gross Amount	Net Pay
1542	0.00	0.00	0.00	39,200.00	6,664.00	612.00	9,408.00	3,600.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	59,484.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	200.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	2,000.00	0.00	0.00	0.00	0.00	0.00	0.00	12,101.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	19,167.00	59,484.00	40,317.00
7887	0.00	0.00	0.00	49,600.00	8,432.00	612.00	11,904.00	3,600.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	74,148.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	6,000.00	200.00	1,377.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	405.00	20.00	50.00	0.00	0.00	8,383.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	16,715.00	74,148.00	57,433.00
3039	0.00	0.00	0.00	40,400.00	6,868.00	612.00	0.00	3,600.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	51,480.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	200.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	493.00	0.00	12,347.00	0.00	0.00	0.00	0.00	0.00	0.00	2,020.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	20,067.00	51,480.00	31,413.00
5496	0.00	0.00	0.00	31,400.00	5,338.00	612.00	7,536.00	3,600.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	48,486.00	0.00	0.00	0.00	0.00	4,187.00	712.00	82.00	1,005.00	480.00	0.00	0.00	0.00	0.00	0.00	0.00	6,466.00	0.00	200.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	2,152.00	0.00	70.00	0.00	0.00	9,038.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	15,654.00	54,952.00	39,298.00
5762	0.00	0.00	0.00	60,400.00	10,268.00	612.00	14,496.00	3,600.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	89,376.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	5,000.00	200.00	7,363.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	1,741.00	0.00	0.00	0.00	0.00	15,676.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	30,660.00	89,376.00	58,716.00
9593	0.00	0.00	0.00	52,000.00	8,840.00	612.00	12,480.00	3,600.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	77,532.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	10,000.00	200.00	5,035.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	3,500.00	0.00	0.00	0.00	75.00	0.00	0.00	2,020.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	20,860.00	77,532.00	56,672.00
7363	0.00	0.00	0.00	52,000.00	8,840.00	612.00	12,480.00	3,600.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	77,532.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	25,000.00	200.00	3,857.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	3,500.00	0.00	0.00	0.00	75.00	0.00	0.00	2,020.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	35,132.00	77,532.00	42,400.00
7024	0.00	0.00	0.00	50,500.00	8,585.00	612.00	12,120.00	3,600.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	75,417.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	15,000.00	200.00	4,143.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	8,000.00	0.00	7,023.00	25.00	75.00	0.00	0.00	20,769.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	55,265.00	75,417.00	20,152.00

[Anonymous]

I want to convert the paysheet in following format by summing up several columns,

Employee Id	Dearness Allowance	DA on TpA	Subsistence Allw.	Basic Pay	Dearness Allowance	DA on TpA	House Rent Allowance	Transport Allowance	Spl. Allowance	Arrears	Out Of Account	Total Gross Pay	Total Recovery	Net Pay
3755	0.00	0.00	0.00	39,200.00	6,664.00	612.00	9,408.00	3,600.00	0.00					40,317.00
5865	0.00	0.00	0.00	49,600.00	8,432.00	612.00	11,904.00	3,600.00	0.00					57,433.00
7282	0.00	0.00	0.00	40,400.00	6,868.00	612.00	0.00	3,600.00	0.00					31,413.00
9733	0.00	0.00	0.00	31,400.00	5,338.00	612.00	7,536.00	3,600.00	0.00					39,298.00
6275	0.00	0.00	0.00	60,400.00	10,268.00	612.00	14,496.00	3,600.00	0.00					58,716.00
9480	0.00	0.00	0.00	52,000.00	8,840.00	612.00	12,480.00	3,600.00	0.00					56,672.00
1876	0.00	0.00	0.00	52,000.00	8,840.00	612.00	12,480.00	3,600.00	0.00					42,400.00
2971	0.00	0.00	0.00	50,500.00	8,585.00	612.00	12,120.00	3,600.00	0.00					20,152.00
5870	0.00	0.00	0.00	50,500.00	8,585.00	612.00	12,120.00	3,600.00	0.00					30,910.00

[lbendlin]

 it has many redundant columns which  needs to be added  and transformed into  a new column.

Right, but this may not need to be done in Power Query.  You might be able to get better performance by doing that in DAX.

Also, read about the difference between Table.RemoveColumns and Table.SelectColumns

[Anonymous]

Can you give me solution in Dax?

By redundant I meant that,  same category of amount.  I want to get these coumns added together and get rid of them. 

[lbendlin]

 I want to get these coumns added together and get rid of them

"premature optimization is the root of all evil"

Try loading the raw data first, and do the math in DAX. It runs in memory and is much faster than Power Query. Groupings in Power Query are very costly.

Please provide sanitized sample data that fully covers your issue. I can only help you with meaningful sample data.
Please paste the data into a table in your post or use one of the file services like OneDrive or Google Drive.
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...

Please show the expected outcome based on the sample data you provided. Screenshots of the expected outcome are ok.

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

[Anonymous]

Updated my first post with the file links.  Please check.