Combining duplicate rows, sum some values and keep others

[LauraB]

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. 

[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.

[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 ,
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.

[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.

[lbendlin]

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_mayu · Accepted solution]

@LauraB 

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