Group values on some similarity

[bnjmnnl]

Hello! 

I was wondering if Power BI holds the function/logica to class/group values based on some similarity. 
For instance, when working with products and its Group names, anl logic to group 'Motor oil', 'Motoroil', 'Turbine oil' with each other. As the data-entry is not waterproof and mistakes can (and will) be made, this happens a lot.

[v-tejrama]

Hi @bnjmnnl,

After performing a fuzzy merge between my Product table and the ProductMapping table to group similar product names (like "Motor oil", "Motoroil", and "Turbine oil" into "Engine Oils")

I expanded the merged column and brought in the GroupedProductName field. Then, I created a bar chart with GroupedProductName on the Axis and Count of ProductName in the Values field.

However, I noticed that some groups, like "Engine Oils" and "Filters", were showing the same count, even though I expected different values. To investigate this, I switched to a Table visual to inspect the underlying data and found that the counts were accurate because there were exactly three product names mapped under each group.

It's important to ensure that there are no duplicate product names in the data; if so, using "Count (Distinct)" in the Values field may be more appropriate. Additionally, I reviewed the fuzzy match results to confirm that products were grouped correctly, as a lower similarity threshold might have caused unintended matches.

After cleaning the mapping table and ensuring each product was matched accurately, the visual displayed the correct grouped counts.

Please find the attached pbix file for your reference.

If the response has addressed your query, please Accept it as a solution and give a 'Kudos' so other members can easily find it.

Best Regards,
Tejaswi.
Community Support

[v-tejrama]

Hi @bnjmnnl ,

Just wanted to check if you had the opportunity to review the suggestion provided?
If the response has addressed your query, please Accept it as a solution and give a 'Kudos' so other members can easily find it.

Thank you.

Tejaswi.

[bnjmnnl]

Hello ,

It is not the solution I was looking for, guess it is not possible 🙂

[burakkaragoz · Accepted solution]

Hi @bnjmnnl ,

Power BI doesn’t have a built-in function for automatically grouping text values based on similarity in visuals, but there are a few effective approaches:

1. Fuzzy Matching in Power Query:
When loading your data in Power Query, you can use the “Merge Queries” feature with “Fuzzy Matching” enabled. This can group together similar values (like 'Motor oil', 'Motoroil', 'Turbine oil') under a common name by merging with a mapping/reference table.

2. Custom Mapping Table:
Create a mapping table listing all possible variations and their intended group. Merge this table with your main data to standardize groupings.

3. Manual Grouping in Power BI Desktop:
For short lists, you can right-click a column in Data view and use “Group By” to manually assign similar items to the same group.

4. DAX Logic (for simple cases):
You can use a calculated column with SWITCH or IF statements to group known variations programmatically.

If you’d like a step-by-step for any method, just let me know!

[Enzo_Rideau]

Couldn't say better !