Forum Discussion
Power Query -Delete column substring corresponding to the text of another column (insensitive case)
You merely need to modify the Table.ReplaceValue function I provided you in your previous similar question:
#"Remove Acronyme" = Table.ReplaceValue( #"Previous Step", each [Acronyme], null, (x,y,z) as text => let pos = Text.PositionOf(x,y,Occurrence.All,Comparer.OrdinalIgnoreCase) in List.Accumulate( List.Reverse(pos), x, (s,c)=> Text.RemoveRange(s,c,Text.Length(y)) ), {"Nom","Volume","Circulating Supply"})#"Previous Step"
#"Remove Acronyme"
Note that in the first row of the first column, it is removing both instances of the Acronyme. Depending on exactly what you want to do in that instance, you may need to change the logic a bit.
The comments are worthwhile. Here are some finer points.
Your second argument:
each _,
could be improved. You only ever use it in the form
row[Acronyme]
since the table row is what get's passed there anyway. So you could simplify it to:
each [Acronyme],
and then maybe replace "row" with something like "akro", so your list of parameters for the fourth argument might be:
(cols, akro, z)
Also, omitting the "as text" means that you will have to set the data type again in a subsequent step. This may be desireable if some of the columns may be numeric after acronyme removal.
(cols, akro,z) as text =>
Hi ronrsnfld
I preferred to avoid optimizations to help community members who are starting out like me and who, like me, are baffled by the logic of the M language.
Thanks again for your help