Filter two columns with names and surnames in a unique column without repeating

[vicentetic]

I have a problem with this transformation apparently easy. It's a list of inventors of patents. Some patents have two inventors. Some inventors appear in several patents. I've split them into two columns but now, I can not achieve a unique column with all de names (and surnames) but without repetitions.

In the end, I want a ranking of inventors

Thank you in advance

[v-yiruan-msft]

Hi @vicentetic ,

Could you please provide some sample data and your expected results in the form of table in order to provide you a proper solution? Please also provide the calculation logic of Rank the inventors. Thank you.

Best Regards

Rena

[vicentetic]

Of course. I have prepared a simplified example of my problem

I HAVE THIS                                                     

patent ID	inventor 1	inventor 2
1	John	Susan
2	Peter	James
3	Mary	John
4	Ann	Peter
5	Peter	Susan
6	Arnold	Susan
7	Susan	Fred

I WANT THIS

	INVENTORS	Nº Patents
	Susan	4
	Peter	3
	John	2
	Mary	1
	Ann	1
	Arnold	1
	Fred	1
	James	1

Thank you

[harshnathani · Accepted solution]

Hi @vicentetic ,

You will need to unpivot the column 

Here is one way to do it in the query editor.  To see how it works, just create a blank query, go to Advanced Editor, and replace the text there with the M code below. 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUfLKz8gDUsGlxYl5SrE60UpGQF5AaklqEUgyMTe1GCxqDOT5JhZVwnSAxEyAHMe8PLh6kJgpkm6EmWYglUV5+TkpKMLmcJ6OkltRaopSbCwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"patent ID" = _t, #"inventor 1" = _t, #"inventor 2" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"patent ID", Int64.Type}, {"inventor 1", type text}, {"inventor 2", type text}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"patent ID"}, "Attribute", "Value"),
    #"Renamed Columns" = Table.RenameColumns(#"Unpivoted Other Columns",{{"Value", "Name"}, {"Attribute", "Inventor No"}})
in
    #"Renamed Columns"

Else go to Query Editor. Select the First Column abd the Click on UnPivot Columns, Unpivot Other Columns

Then use this measure

Count of Patents = Count ( 'Table'[patent ID])

Regards,

Harsh Nathani

Appreciate with a Kudos!! (Click the Thumbs Up Button)

Did I answer your question? Mark my post as a solution!

[Pragati11]

Hi @vicentetic ,

You can do something as shown below:

Raw data is as follows:

I created a UNION on both columns in the raw data as follows using DAX: (creating a new table using following DAX)

Inventory Union = UNION(ADDCOLUMNS(Inventor, "Inventor", Inventor[Inventor1]), ADDCOLUMNS(Inventor, "2nd", Inventor[Inventor2]))

The table after union I get is as follows:

The INVENTOR column in the above table has got all the inventor clubbing INVENTOR1 and INVENTOR2.

Now create a new table visual as follows:

Move INVENTOR column from union table.

Move COUNT(ID) column from union table.

This gives you the desired output. Hope this helps.

Thanks,

Pragati

[Pragati11]

HI @vicentetic ,

Can you share some sample data please?

Try if this helps:

UniqueCnt =  var uniqueTable = UNION(SELECTCOLUMNS('Tablename', "Inventor", [Inventor columnname]), SELECTCOLUMNS('Tablename', "Patent", [Patent columnname])) RETURN COUNTROWS(DISTINCT(uniqueTable))

Thanks,

Pragati

[Greg_Deckler]

@vicentetic - Can you create a unique column by concatenating your two columns into a single new column? It would help if you could demonstrate what your data looks like. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.