Remove Duplicate from Aggregated Column

[ray1804]

Hi, I have created a DAX column to calculate the aggregate by lane but now I want to remove duplicate rows from the table. Can you please suggest how to do that? 

[v-cgao-msft]

Hi @ray1804 ,

//I realized later that this is a calculated table, please try nesting the DISTINCT() function on the outside of the table's expression.

1. You can select these columns.

2. Right-click and remove the duplicate.

result.

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

[Peter_Beck]

Hi -

You can use "Group By" in the "Transform" tab of Power Query. Use the advanced option and group by each row in order. Then when the grouping is done you can delete the column that holds the summary.

Here is an example you can cut-and-paste into the Advanced Editor and walk through to see what it does:

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtQ30zcyMDJS0lEyAWIQHZJRWlScklgJZBoCsZ8ziGFkYKpnbq4Uq0O6llgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ShipDate = _t, Weekday = _t, WeekNumber = _t, DayOfWeek = _t, Quarter = _t, Lane = _t, AggregatedCubes = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"ShipDate", type date}, {"Weekday", Int64.Type}, {"WeekNumber", Int64.Type}, {"DayOfWeek", type text}, {"Quarter", Int64.Type}, {"Lane", type text}, {"AggregatedCubes", type number}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"ShipDate", "Weekday", "WeekNumber", "DayOfWeek", "Quarter", "Lane", "AggregatedCubes"}, {{"Count", each _, type table [ShipDate=nullable date, Weekday=nullable number, WeekNumber=nullable number, DayOfWeek=nullable text, Quarter=nullable number, Lane=nullable text, AggregatedCubes=nullable number]}}),
#"Removed Columns" = Table.RemoveColumns(#"Grouped Rows",{"Count"})
in
#"Removed Columns"

Hope this helps!

Peter