Sep 01 2021 11:19 AM
I have this sample table with two columns: Company and Amount. In this table, there are four different companies: Amazon, Google, Apple, and Microsoft. However, as seen in the table, the company's name is not always the only thing in the cell. Sometimes there will be text in front of or behind the name.
What I would like to do is sift through the rows and if a company's name is in the cell, replace the entire cell's content with just the name of the company.
Current Table:
Goal Table(Just the company names are shown):
Sep 01 2021 12:59 PM
From my point of view it shall be more logic added to the extracting of companies name. Otherwise how Power Query know that in "Google Search" and "Apple Search" company name is not "Search" but "Google" and "Apple". Such logic could be separate table with all possible companies names.
With that we may add custom column to table as
each List.Intersect( {Text.Split([Company], " "), Companies[Company]} )
and extract value from it.