Trouble counting occurence of same text value accross columns

[Toune84]

Hello all,

I am new at PowerBi. I have issue counting occurences between distinct columns. I have several character displayed as shown here with their gender + the accessories they wear:

Woman	3D Glasses	Long Hair	Santa Claus Hat
Man	Long Hair	Police Cap	Big Beard	Roman Collar	3D Glasses
Woman	3D Glasses	Roman Collar

What I would like to get is a table counting how many time each accessorry has been used such as:

3D Glasses	3
Big Beard	1
Long Hair	2
Police Cap	1
Roman Collar	2
Santa Claus Hat	1

I have tried many things I found on the forum but I do not manage to make it work. 

Thanks for the help!

Toune84

[Nathaniel_C · Accepted solution]

Hi @Toune84 ,
Try  this:

Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
Nathaniel

Here is your original table in Power Query.

highlight gender and Unpivot other columns"

Remove other columns

Filter out the blanks

Go to Transform tab, select groupby and you have your final table with names and counts

Here is the code that you may post into advance editor in power query.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCs/PTcxT0lEydlFwz0ksLk4tBnJ88vPSFTwSM4uA7ODEvJJEBeecxNJioFAJUEQBjGN1opV8wVqRVQfk52Qmpyo4JxYAOU6Z6QpOqYlFKUB2EMgeBef8nJzEIlTrQAZhdQWaFpC1IBQbCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"(blank)" = _t, #"(blank).1" = _t, #"(blank).2" = _t, #"(blank).3" = _t, #"(blank).4" = _t, #"(blank).5" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"(blank)", type text}, {"(blank).1", type text}, {"(blank).2", type text}, {"(blank).3", type text}, {"(blank).4", type text}, {"(blank).5", type text}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"(blank)", "Gender"}, {"(blank).1", "C1"}, {"(blank).2", "C2"}, {"(blank).3", "C3"}, {"(blank).4", "C4"}, {"(blank).5", "C5"}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Renamed Columns", {"Gender"}, "Attribute", "Value"),
    #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Other Columns",{"Gender", "Attribute"}),
    #"Filtered Rows" = Table.SelectRows(#"Removed Columns", each ([Value] <> "" and [Value] <> " ")),
    #"Grouped Rows" = Table.Group(#"Filtered Rows", {"Value"}, {{"Count", each Table.RowCount(_), Int64.Type}})
in
    #"Grouped Rows"

[Toune84]

Thanks a lot for the answer! It works perfectly 😉

[Nathaniel_C]

Hi @Toune84 ,
You are very welcome!

Thank you,

Nathaniel

[amitchandak]

@Toune84 , Do your distinct list of names?

Then you can create a measure like

countrows(filter(Table, search(max(Keyword[KeyWord]) , Table[Column],,0) >0 ) )

[Toune84]

Thanks for the answer! Unfortunately, I still do not manage to make it work.

Yes I have a list of all my accessesories called "Attributes list" in the table called Attributes (see screenshot)

All the occurences I want to find are in the columns '0', '1', '2', '3', '4', '5', '6', '7'

Can you please help me write the formula with the right table names and column names?

Thanks a lot for the help