Sum of values in one column based on similar-text values in another column

[Anonymous]

When using a measure to sum up the Table1[Cost of Purchase]  I get the aggregation of each of the unique company names in Table1[Company]:
-Bob's Bikes
-Bob's Bikes Online

-Bob's Motorcycle and Bicycles

-GlobalCorp

-Dinoco 

Table1

Company	Cost of Purchase
Bob's Bikes	$50.82
Bob's Bikes Online	$97.13
Bob's Bikes Online	$12.98
Bob's Bikes	$73.72
GlobalCorp	$17.26
Bob's Bikes	$38.17
Bob's Motorcycle and Bicycles	$44.43
GlobalCorp	$920.41
Dinoco	$51.84

Now, I know that all of the Bob's Bikes, Bob's Bikes Online, and Bob's Motorcycle and Bicycles are all from the same company (for the sake of the argument, let's say they've changed the company name a few times over the years) and I want to be able to sum them all up and have them return one aggregated value under Bob's Bikes --I'm also wanting to return the other companies and their respective aggregations as well. I've tried using a FILTER with a LEFT function nested in, but that only gets me Bob's Bikes and the like.

An example of what I'm looking to do is listed below.

Company	Cost of Purchase
Dinoco	$51.84
GlobalCorp	$937.67
Bob's Bikes	$317.25

[HotChilli]

Add a column in Power Query:

if [Company] = "Bob's Bikes Online" then "Bob's Bikes" else if [Company] = "Bob's Motorcycle and Bicycles" then "Bob's Bikes" else [Company]

And here's a confession , I didn't write any of that, I just went in to Add column from examples and let the algorithm work it out.

So for a relatively simple case like this, that's all you need but imagine if there were 20 versions of Bob's Bikes, B'obs Bikes, B's bikes etc, you can then consider the other solutions like   

 if Text.Contains([Company], "ike") then "Bob's Bikes" else [Company]

and again, I didn't write that, I just used the Add Conditional Column.

----

So you use whatever techniques you need to get the job done.

[AllisonKennedy]

@Anonymous  Do you have a common Customer Number or ID for all the Bob's Bikes names? If you have a Customer or Accounts table somewhere that you can export, this would make what @HotChilli  is suggesting much easier... 

[HotChilli · Accepted solution]

It's more common to do this in Power Query as part of your data prep.  This could range from adding another column with an 'if' statement (if it's simple) OR  creating a separate table with all the corrections (this could be used as dimension table in your model OR creating a separate table and doing a merge with a fuzzy join to find matches (sounds complex but Power Query does all the work).

Does that make sense?

[Anonymous]

Those are terrific options, thank you for presenting several for me. I will end up doing the second option because I already know how to do that. For sake of learning, could you elaborate on how I'd use the 'If' condition for another column?

Also, thank you for the tip regarding what part of the process I should take care of this, that genuinely helps me learn.