Remove duplicates in one column based on a given category in another column

[ColinWatson]

Hi All,

I have a set of data that looks like the following:

Class Name	Class Number	Participant
Class A	1	Alex
Class A	1	Brad
Class A	1	Alex
Class B	2	Chase
Class B	2	Dave
Class B	2	Alex
Class B	2	Edward
Class C	3	Fiona
Class C	3	Fiona
Class C	3	Greg
Class C	3	Henry
Class A	4	James
Class A	4	Kelsey
Class A	4	James
Class A	4	Dave
Class A	4	James

Each class has a corresponding number/id associated to it, which is unique for each instance of a class.

I need a way to remove the repeated participants (those highlighted in red) whom appear more than once for any given Class Number. For instance, James would need to be removed from the list twice because he appears two times alongside the same Class Number; however, Dave does not need to be removed because he appears once as a participant in Class #2 and once as a participants in Class #4.

After the removing the duplicates, the table would look like this:

Class	Class Number	Participant
Class A	1	Alex
Class A	1	Brad
Class B	2	Chase
Class B	2	Dave
Class B	2	Alex
Class B	2	Edward
Class C	3	Fiona
Class C	3	Greg
Class C	3	Henry
Class A	4	James
Class A	4	Kelsey
Class A	4	Dave

I'm still new to Power BI and have been struggling with this one for a while. Any help/advice is greatly appreciated.

[Ashish_Mathur]

Hi,

This M code works

let
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(Source, {{"Class Number", type text}}, "en-IN"),{"Class Name", "Class Number", "Participant"},Combiner.CombineTextByDelimiter(";", QuoteStyle.None),"Merged"),
    Partition = Table.Group(#"Merged Columns", {"Merged"}, {{"Partition", each Table.AddIndexColumn(_, "Index",1,1), type table}}),
    #"Expanded Partition" = Table.ExpandTableColumn(Partition, "Partition", {"Index"}, {"Index"}),
    #"Filtered Rows" = Table.SelectRows(#"Expanded Partition", each [Index] = 1),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Index"}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Removed Columns", "Merged", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), {"Merged.1", "Merged.2", "Merged.3"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Merged.1", type text}, {"Merged.2", Int64.Type}, {"Merged.3", type text}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Merged.1", "Class Name"}, {"Merged.2", "Class Number"}, {"Merged.3", "Participant"}})
in
    #"Renamed Columns"

Hope this helps.

[mahoneypat · Accepted solution]

In the query editor, just right click on your last step in the steps pane and "Insert Step After", then use one of these to get your result.  Replace Changed Type with the name of your last step.

= Table.Distinct(#"Changed Type", {"Class Name", "Class Number", "Participant"}) // if you have more than 3 columns

= Table.Distinct(#"Changed Type") // if you have only the 3 columns

Regards,

Pat

[ColinWatson]

Thank you, Pat. Your solution worked absolutely brilliantly. Can't thank you enough!

Kind regards