Need help counting number of times a name is mentioned in each column, to represent in column graph.

[MatheusGuzman]

Hi!

At the moment i´m extracting values from an external API, corresponding to text that users fill in.

I need to make a column graph, showing how many times each name is mentioned. Problem is, everytime a user fills with his name he ends up doing it in a different manner each time. I have cases where the form is filled with multiple names too.

Already tried dividing the column in the multiple names case, but i wasnt able to translate the correct info to the graph this way, my only objective is being able to count how many times each name was mentioned, for each column.

Any idea guys?

[Anonymous]

Hi @MatheusGuzman ,

When I reviewed this post, I saw this post seem not to be solved.

Could you please tell us have you solved it in these days. It's good to see you share us with your own nice solution or accept the helpful replies from users.

In summary, just as Syk said, it is best to solve it from the source, fill it in according to the regulations when entering, and you can add prompt requirements to the input box.

Or you can do it through Power Query's transformation. Use the fuzzy matching in Power Query.

How fuzzy matching works in Power Query - Power Query | Microsoft Learn

Best Regards,

Stephen Tao

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

[Omid_Motamedise]

Could you provide sample of data in type table and also your desired result?

[MatheusGuzman]

So for instance:

The graph above is incorrect, because each bar contains a unique name, even if it´s the same name but typed in a differente way. I need for example to group all "Henrique", "Rogério", and "Jose Mario" each one for his own column.

[Omid_Motamedise]

THe differences is just for caracter "e" or other caracters also  come with the same issue?

[MatheusGuzman]

Other caracters too but is not really the main issue, i can just fix the names selecting the column and switch to the correct names after. The real problema is count multiple names in same column.

[Anonymous]

Power Query is built for garbage in. I would try Table.FuzzyGroup. Then you can just use the count aggregation. Play with the parameters till it gets right.

--Nate

[MatheusGuzman]

Thanks for the tip, i´m going to explore more with this function!

[Syk]

This is just a case of garbage in, garbage out. You won't be able to account for the numerous ways a user will input information. There needs to be guardrails where the user is entering this data instead of letting it run wild and forcing complex logic on the backend. Can you add a first name and last name field? This should at least let users know you're expecting one name and it should be somewhat similar everytime.

[MatheusGuzman]

I see, but even if i did that, the possibility of having two people´s name in the same column is still relevant. I could determine specifically with those users to only fill with their first names, but the problem maintains. I need a way to count even if i have multiple names in the same column, each name mentioned.
I tried making a sheet with the names of the user, to use as an index, but i don´t know how to count comparing the original table with the index.