Create groups based on names in a column.

[GMcFarlane]

Hello PBI Enthusiasts

I have a large list that contains different vendors, however the names for the same vendor may appear with additional characters and sometimes mispelled

Is there a way function to filter and group these items under the correct company name.

Example:

Vendor	Vendor Detail	Amount	Description
Alphabet company #1	invoice 2030	34322	Contrator Bob
Alphabet company 1	invoice 3433	453432	Web Design
Alphabet company 1	Invoice 4040	444	System
Beta Company	Invoice 3333	3556	Invoice
BetaCompany	Invoice 3332	5354	Web design
BetaComp	Invoice 2233	3433	Contractor 334

I would like to return

GroupName	Vendor	Vendor Detail	Amount	Description
Alphatbet Company LLC	Alphabet company #1	invoice 2030	34322	Contrator Bob
Alphatbet Company LLC	Alphabet company 1	invoice 3433	453432	Web Design
Alphatbet Company LLC	Alphabet company 1	Invoice 4040	444	System
Beta Company Grp.	Beta Company	Invoice 3333	3556	Invoice
Beta Company Grp.	BetaCompany	Invoice 3332	5354	Web design
Beta Company Grp.	BetaComp	Invoice 2233	3433	Contractor 334

There are thousands of rows. trying to find a way not to group this manualy each time I import. Or mitigate the amount of things i need to change.

thanks in advance. 

[Tad17 · Accepted solution]

Hey @GMcFarlane 

Based on the given information I think your fastest solution would be to do a find and replace for each misspelling. The nice thing about this is that it will repeat the find and replace each time you refresh. So once you catch all of the possible misspellings it will correct them going forward.

In Power Bi Desktop

in the ribbon under the home tab open the edit queries

select the vendor column 

in the add column tab select "duplicate column" the duplicate will become your "GroupName" Column

You can change the header to "GroupName" if necessary/desired

In the home tab of the edit queries dialog box select replace values

replace each of the possible mispellings with the desired result

Your vendor column will remain, but now you have a column that, with each refresh, will copy the vendor column and correct the mispelled names to the desired result.

If this helps please kudo.

If this solves your problem please accept it as a solution.

[GMcFarlane]

I thought there would be someway to do by DAX/R or something. But i guess this will work. Data work isnt always glamourous. 

[Tad17]

Hey @GMcFarlane 

Thanks for accepting my post as the solution.

You can probably cook something up in DAX such as a calculated column with if(left(#)="___", group name, 0), but you would have to embed a ton of if statements. The find and replace seems like the much better option to me. Unfortunately you may have to revisit the pbix file and reupload if there are new misspellings in the future, but overall it seems like the better way to do it.