Remove duplicates without changing values in calculated column

Hi bamba98 ,

In Power Query Editor, you remove the duplicate ID rows, so they are not existed. You cannot get the Tot=45, because the real data is gone.

You need to get the Tot in Power Query Editor, and you can refer the following steps.

1. Group by ID.

2. Add a custom column to get the max period.

3. Expand the custom column.

4. At last delete the Table column.

The complete M Query is as follows:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTIyMDIAUoYGSrE6MBFDSyBliixgAVKCImKOEDFCGIMsgGSKEZIpBsgiMFNiAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Period = _t, Val = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Period", Int64.Type}, {"Val", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"ID"}, {{"Table", each _, type table [ID=nullable number, Period=nullable number, Val=nullable number]}, {"New Tot", each List.Sum([Val]), type nullable number}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom.1", each Table.Max([Table],"Period")),
    #"Expanded Custom.1" = Table.ExpandRecordColumn(#"Added Custom", "Custom.1", {"Period"}, {"Custom.1.Period"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Custom.1",{"Table"})
in
    #"Removed Columns"

BTW, pbix as attached.

Best regards,

Community Support Team _ zhenbw

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.