CODEWITHSUNDEEP
powerbipowerquerym
Palo Mendes
Palo Mendes

Reputation: 1

Row with duplicate values in one column and different values in a second column

In Power BI, I need to identify all distinct duplicate values in a Column A that have distinct values in Column B.

Example input:

Name    Index
-------------
john    1
mary    1
john    1
jim     1
john    2
mary    1
jim     2
jim     1
john    2
mary    2

Desired result:

Name    Index
-------------
john    1
mary    1
jim     1
john    2
jim     2
mary    2

Column Name in my Power BI is a concatenated column

Is this possible?

Upvotes: 0

Views: 998

Answers (2)

Alexis Olson
Alexis Olson

Reputation: 40264

You should be able to do this pretty easily in the Power Query Editor GUI.

Select the combination of columns that you want to remove duplicates on (name and index in your case) and then under the Home tab you can select Remove Rows > Remove Duplicates.

Query Editor GUI

This will automatically generate the Table.Distinct M code that chillin suggests.

Upvotes: 2

chillin
chillin

Reputation: 4486

Provided your previous step is a table, you should just be able to use:

Table.Distinct(nameOfPreviousStep, {"Name", "Index"})

Below is an example of what I mean:

let
    someTable = Table.FromRows({{"john",1},{"mary",1},{"john",1},{"jim",1},{"john",2},{"mary",1},{"jim",2},{"jim",1},{"john",2},{"mary",2}}, type table[Name=text, Index=Int64.Type]),
    removeDuplicates = Table.Distinct(someTable, {"Name", "Index"})
in
    removeDuplicates

Try it out and see if it gives you your expected output (I think it does based on what I've seen).

Upvotes: 0

Related Questions