How to replace value when cell contains keyword

%3CLINGO-SUB%20id%3D%22lingo-sub-2708673%22%20slang%3D%22en-US%22%3EHow%20to%20replace%20value%20when%20cell%20contains%20keyword%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2708673%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20this%20sample%20table%20with%20two%20columns%3A%20Company%20and%20Amount.%20In%20this%20table%2C%20there%20are%20four%20different%20companies%3A%20Amazon%2C%20Google%2C%20Apple%2C%20and%20Microsoft.%20However%2C%20as%20seen%20in%20the%20table%2C%20the%20company's%20name%20is%20not%20always%20the%20only%20thing%20in%20the%20cell.%20Sometimes%20there%20will%20be%20text%20in%20front%20of%20or%20behind%20the%20name.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhat%20I%20would%20like%20to%20do%20is%20sift%20through%20the%20rows%20and%20if%20a%20company's%20name%20is%20in%20the%20cell%2C%20replace%20the%20entire%20cell's%20content%20with%20just%20the%20name%20of%20the%20company.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECurrent%20Table%3A%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22SampleTable.PNG%22%20style%3D%22width%3A%20398px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F307366i479215B0B4A7EB49%2Fimage-dimensions%2F398x265%3Fv%3Dv2%22%20width%3D%22398%22%20height%3D%22265%22%20role%3D%22button%22%20title%3D%22SampleTable.PNG%22%20alt%3D%22SampleTable.PNG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EGoal%20Table(Just%20the%20company%20names%20are%20shown)%3A%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Goal%20Table.PNG%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F307367i0374FAB969E2FF8F%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22Goal%20Table.PNG%22%20alt%3D%22Goal%20Table.PNG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2708673%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2709009%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20replace%20value%20when%20cell%20contains%20keyword%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2709009%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1143795%22%20target%3D%22_blank%22%3E%40AnthonyMartini%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EFrom%20my%20point%20of%20view%20it%20shall%20be%20more%20logic%20added%20to%20the%20extracting%20of%20companies%20name.%20Otherwise%20how%20Power%20Query%20know%20that%20in%20%22Google%20Search%22%20and%20%22Apple%20Search%22%20company%20name%20is%20not%20%22Search%22%20but%20%22Google%22%20and%20%22Apple%22.%20Such%20logic%20could%20be%20separate%20table%20with%20all%20possible%20companies%20names.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EWith%20that%20we%20may%20add%20custom%20column%20to%20table%20as%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-powerquery%22%3E%3CCODE%3Eeach%20List.Intersect(%20%7BText.Split(%5BCompany%5D%2C%20%22%20%22)%2C%20Companies%5BCompany%5D%7D%20)%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3Eand%20extract%20value%20from%20it.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Visitor

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:

SampleTable.PNG

 

Goal Table(Just the company names are shown):

Goal Table.PNG

1 Reply

@AnthonyMartini 

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.