Forum Discussion
Combining duplicate rows, sum some values and keep others
I have a duplicate row combination issue that I haven't found the whole answer to, and I hope the brains of this community can come together and help me.
I am working with a table in Power Query within Power BI that includes some duplicate rows which are meant as adjustments in a billing context. The ID (key) and the name on the duplicate rows is the same, as are multiple other column entries on the rows, but where the entries are different, I need to keep or combine different values depending on the column.
Example input:
| ID Number | Account Number | Name | Department | Total Current Charges | Total Adjustments | Total Charges And Adjustments |
| 4173 | 34546 | JEN BRUINS | IT | $31.06 | $0.00 | $31.06 |
| 5062 | 25275 | BELLE COAL | -$36.01 | $0.00 | -$36.01 | |
| 5062 | 34546 | BELLE COAL | IT | $56.94 | $0.00 | $56.94 |
| 7698 | 25275 | BEN CREED | IT | $43.20 | $0.00 | $43.20 |
| 3849 | 34546 | SHELLY ROBSON | IT | $28.82 | $0.00 | $28.82 |
| 6269 | 25275 | CHRISTOPHER LEE | HR | -$41.27 | $0.00 | -$41.27 |
| 6269 | 34546 | CHRISTOPHER LEE | IT | $56.94 | $0.00 | $56.94 |
With these duplicated rows with the same ID number and Name, I need to
- take the sum of the currency columns of the duplicate rows, so that the negative number/billing adjustment is taken into account on the total;
- keep all values which are the same in both duplicate rows;
- retain the Account Number value on the row which has the positive values in the currency columns;
- also retain the Department value on the row which has the positive values in the currency columns, whether or not there is a null (ex: Belle Coal) or an entry (ex: Christopher Lee) in the column on the adjustment row.
Desired output:
| ID Number | Account Number | Display Name | Department | Total Current Charges | Total Adjustments | Total Charges And Adjustments |
| 4173 | 34546 | JEN BRUINS | IT | $31.06 | $0.00 | $31.06 |
| 5062 | 34546 | BELLE COAL | IT | $20.93 | $0.00 | $20.93 |
| 7698 | 25275 | BEN CREED | IT | $43.20 | $0.00 | $43.20 |
| 3849 | 34546 | SHELLY ROBSON | IT | $28.82 | $0.00 | $28.82 |
| 6269 | 34546 | CHRISTOPHER LEE | IT | $15.67 | $0.00 | $15.67 |
I'm sure there is a way to write this gracefully in the Advanced Editor as a function, but while I have found a number of different solutions for simpler versions of combining duplicate row values (namely I've tried using Group By several different ways without success), I haven't been able to combine those solutions into a query that gives me the result I need.
The real table has at least 150 of these duplicated rows in a table of total 1445 rows, and many more columns than this simplified version, so any help you can offer here will be greatly appreciated.
you can try this
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dY/LCsMgEEV/RVy34luzbFLBFNGi6aKE/P9vVBMthlIQ5t6Rw2HWFXKiGLxAxgWXeT6MB2N8zT7lMi/lhyBcfvA3b5cVCixpXlBBlchzNM4ZMIWbyyW/K5MIk0rV0mHNdsJ2m5Bo4JU7csGUHPTJ5sEUjbk3ijNEK7THwjDNh06VbHa9QQxjCr5xVCPdwCMXUlI5dLbJxjkt4WlNBM6YvLGxXMUJoqqdeJSObt5f+t+d2wc=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"ID Number" = _t, #"Account Number" = _t, Name = _t, Department = _t, #"Current Charges" = _t, #"Total Adjustments" = _t, #"Total Charges And Adjustments" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"ID Number", Int64.Type}, {"Account Number", Int64.Type}, {"Name", type text}, {"Department", type text}, {"Current Charges", type number}, {"Total Adjustments", Int64.Type}, {"Total Charges And Adjustments", type number}}),
#"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1, Int64.Type),
#"Added Custom" = Table.AddColumn(#"Added Index", "New Account Number", each Table.Sort( Table.SelectRows(#"Added Index",(x)=>[ID Number]=x[ID Number]),{{"Index", Order.Descending}}){0}[Account Number]),
Custom1 = Table.AddColumn(#"Added Custom", "New department", each Table.Sort( Table.SelectRows(#"Added Index",(x)=>[ID Number]=x[ID Number]),{{"Index", Order.Descending}}){0}[Department]),
#"Grouped Rows" = Table.Group(Custom1, {"ID Number", "New Account Number", "New department"}, {{"charges", each List.Sum([Current Charges]), type nullable number}, {"adjustment", each List.Sum([Total Adjustments]), type nullable number}, {"total", each List.Sum([Total Charges And Adjustments]), type nullable number}})
in
#"Grouped Rows"pls see the attachment below
6 Replies
- lbendlin
Super User
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ZY7bCsMgDEB/RaSPnXi/PK6dYIfo0O5hlP7/byw4Wix7SELCyUm2DUtmBB6xkEpqqE+f0FTeS6rQLCukQTBCNd7HDSuqOUy44kZBnXyMHs35HqFBELdBaEJZzx7eC/vzKk2cbKzRzl68Cc3F+8eJSkE4baiw0nXaGsD7QSVPNacT55ZY3nDNtevMcyhLXfMr+IKi9zAJpf0tGeGm3zgO/G9cnt+/", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Account = _t, Name = _t, Dept = _t, charges = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"charges", Currency.Type}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"ID", "Name"}, {{"Account", each Table.Sort(_,{{"charges",Order.Descending}}){0}[Account]}, {"Dept", each Table.Sort(_,{{"charges",Order.Descending}}){0}[Dept]}, {"Current Charges", each List.Sum([charges]), type nullable text}}) in #"Grouped Rows"How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done". Once you examined the code, replace the entire Source step with your own source.
ryan_mayuyou can try this
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dY/LCsMgEEV/RVy34luzbFLBFNGi6aKE/P9vVBMthlIQ5t6Rw2HWFXKiGLxAxgWXeT6MB2N8zT7lMi/lhyBcfvA3b5cVCixpXlBBlchzNM4ZMIWbyyW/K5MIk0rV0mHNdsJ2m5Bo4JU7csGUHPTJ5sEUjbk3ijNEK7THwjDNh06VbHa9QQxjCr5xVCPdwCMXUlI5dLbJxjkt4WlNBM6YvLGxXMUJoqqdeJSObt5f+t+d2wc=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"ID Number" = _t, #"Account Number" = _t, Name = _t, Department = _t, #"Current Charges" = _t, #"Total Adjustments" = _t, #"Total Charges And Adjustments" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"ID Number", Int64.Type}, {"Account Number", Int64.Type}, {"Name", type text}, {"Department", type text}, {"Current Charges", type number}, {"Total Adjustments", Int64.Type}, {"Total Charges And Adjustments", type number}}),
#"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1, Int64.Type),
#"Added Custom" = Table.AddColumn(#"Added Index", "New Account Number", each Table.Sort( Table.SelectRows(#"Added Index",(x)=>[ID Number]=x[ID Number]),{{"Index", Order.Descending}}){0}[Account Number]),
Custom1 = Table.AddColumn(#"Added Custom", "New department", each Table.Sort( Table.SelectRows(#"Added Index",(x)=>[ID Number]=x[ID Number]),{{"Index", Order.Descending}}){0}[Department]),
#"Grouped Rows" = Table.Group(Custom1, {"ID Number", "New Account Number", "New department"}, {{"charges", each List.Sum([Current Charges]), type nullable number}, {"adjustment", each List.Sum([Total Adjustments]), type nullable number}, {"total", each List.Sum([Total Charges And Adjustments]), type nullable number}})
in
#"Grouped Rows"pls see the attachment below
- Ashish_Mathur
Hi,
This M code works
let Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content], #"Grouped Rows" = Table.Group(Source, {"ID Number"}, {{"AN", each Table.SelectRows(_[[Account Number],[Total Current Charges]],each [Total Current Charges]>0)},{"DN", each Text.Combine([Name],"")},{"D", each Table.SelectRows(_[[Department],[Total Current Charges]],each [Total Current Charges]>0)},{"TCC", each List.Sum([Total Current Charges]), type number}, {"TA", each List.Sum([Total Adjustments]), type number}, {"TCCA", each List.Sum([Total Charges And Adjustments]), type number}}), #"Expanded AN" = Table.ExpandTableColumn(#"Grouped Rows", "AN", {"Account Number"}, {"Account Number"}), #"Expanded D" = Table.ExpandTableColumn(#"Expanded AN", "D", {"Department"}, {"Department"}) in #"Expanded D"Hope this helps.
v-veshwara-msftCommunity Support
Hi LauraB ,
Thanks for posting in Microsoft Fabric Community.
Just wanted to check if the solutions provided were helpful. If yes, please consider marking the helpful replies as "Accepted Solutions" to assist others with similar queries. If further assistance is needed, please reach out.
Thank you.Also thanks to ryan_mayu , lbendlin , and Ashish_Mathur for your prompt and helpful replies and contribution to the community.
- v-veshwara-msft
Hi LauraB ,
Hope you are doing good.Checking in again to see if your query is resolved and whether the responses provided were helpful. If you need further assistance, please reach out.
Thank you.
- v-veshwara-msft
Hi LauraB ,
We wanted to kindly follow up regarding your query. If you need any further assistance, please reach out.
Thank you.
