Remove Duplicated values in column

[PowerBICouple]

Hi Power Query community

I have a sheet with the following structure

Project	Attribute
1	A
1	B
2	B

Thing is, the duplicated 1 value is a mistake as this is my project dimension. This is due to a manual input error. We're living with this manual setup for a bit, but I want to make sure this error doesn't mess up the model, so I would like to figure out a way in Power Query to delete the 1. Delete Duplicates doesn't work as the Attribute (there are many attribute columns) differentiates it and so it isn't a true duplicate. 

How do I make Power BI return only the 2|B column in the above example?

[Vijay_A_Verma · Accepted solution]

See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test (later on when you use the query on your dataset, you will have to change the source appropriately. If you have columns other than these, then delete Changed type step and do a Changed type for complete table from UI again)

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXJUitWBsJzALCMIKxYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Project = _t, Attribute = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Project", Int64.Type}, {"Attribute", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Project"}, {{"Count", each Table.RowCount(_), Int64.Type}}),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"Project"}, #"Grouped Rows", {"Project"}, "Grouped Rows", JoinKind.LeftOuter),
    #"Expanded Grouped Rows" = Table.ExpandTableColumn(#"Merged Queries", "Grouped Rows", {"Count"}, {"Count"}),
    #"Filtered Rows" = Table.SelectRows(#"Expanded Grouped Rows", each [Count] = 1),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Count"})
in
    #"Removed Columns"

 -------------------------------------------------------------------------------------------------------

👍 It's been a pleasure to help you | Help Hours: 11 AM to 9 PM (UTC+05:30)

How to get your questions answered quickly -- How to provide sample data

-------------------------------------------------------------------------------------------------------

[PowerBICouple]

That's a fantastic solution. The way of approaching the problem also taught me another way to solve problems in the future, so thank you very much!