Perform a top occurrence of names present in several columns

[Anonymous]

Hy everyone, 

I'm new at Power BI and I would like to get a top 5 occurrence of names present in 5 different columns. I will use the "tab" graph in power BI, I tried with Power BI command in vain.

To do this, I tried to create a "mega column" containing the 5 initials, and filtering the empty cells. I did not succeed. For information, I unfortunately cannot change the form of the input excel. 
I'm looking for any kind of solution, directly in Excel or Power BI
Can you please help me to achieve this? Thank you in advance 🙂

[Anonymous]

Hi @Anonymous ,

Based on the testing, importing the excel file to power bi desktop and select transform.

Select columns that want to concatenate. Go to the add column and select custom column.

In the dialog, enter the following formula.

[Column1] & "," & [Column2] & "," & [Column3] & "," & [Column4] & "," & [Column5]

Go to the Home tab, select split column > by delimiter. Choosing the comma.

Select 5 columns and remove columns.

Close and apply.

Then, using the following DAX formula to create a new column. Drag the column to the table visual.

Column = COUNTAX(FILTER('Table', 'Table'[Custom] = EARLIER('Table'[Custom])), 1)

Sort the visual by the name and apply a Top 5 fitler.

Best Regards,

Wisdom Wu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous · Accepted solution]

Hi @Anonymous ,

When having some empty cell, select 5 columns, right click and choose unpivot columns.

Rename the column name.

Close and apply. Drag the name column to the visual and set Top N in the Filters pane.

Best Regards,

Wisdom Wu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

It's working very well.
Thank you for all 🙂 

[Anonymous]

Hi , thank you for your response, 
I think it may be working but I have some empty cell and it does not merge for this rows: the concatenete cells appears empty

[Anonymous]

I also have empty cells inside my test data and it works fine. Can you provide some sample data as text rows yes? The file link does not open.

Best Regards

Wisdom Wu

[Anonymous]

Hi, here it is, this is exactly what it's look like after I deleted all the other columms. 

I only anonymized the last names.

thank you for your help

EE 1	EE 2	EE 3	EE 4	EE 5
Bénedicte S	Claire B	Denis	Stéphane B
Olivier H	Thierry H	Patrick A
Jean-Luc M
Serge C	Gilles M	François D	Patrick R	Jean-Michel T
Olivier M				Bénedicte S
Fabrice B	Gabriel C	EMILIEN	Ludivine L	Bénedicte S
Bruno V	Patrice D	Lise B	Béatrice Du B
Bertrand F	Olivier H	Jean-Michel H
Lise L	Nans F	Paul Jun M	Franck B	Stanislas F
Denis F	Stéphane B	Sébastien R	Bénedicte S	Evangéline B
Damien L			Bénedicte S	Bénedicte S
Olivier H
Jean-Luc
Serge C
Olivier M
Fabrice B
Bruno V
Bertrand F
Lise L
Denis F

[audreygerred]

Can you please upload the pbi file with the excel, even if it is dummy data, that is fine 🙂

[Anonymous]

https://docs.google.com/spreadsheets/d/1fQfBKMCIsrqW1XCzNHNYVb6M4od7CKOQ/edit?usp=drivesdk&ouid=1075...

https://drive.google.com/file/d/17RCARHCxewNNy9wqeWg4u3T4sjah3rIC/view?usp=drivesdk
I think you have to ask the access or something