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.
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.
Thanks again for your help and solution. To clarify my understanging, I rewrote your code to be more explicit as below:
Table.ReplaceValue( #"Order col", each _ , null,
(col,row,z) =>
let
lstPos = Text.PositionOf(col,row[Acronyme],Occurrence.All,Comparer.OrdinalIgnoreCase),
lstPosReverse = List.Reverse(lstPos),
//strNewValue = lstPosReverse
// strNewValue = Text.Length(row[Acronyme])
//strNewValue = List.Accumulate(lstPosReverse, 0, (s,c)=> s+1 )
strNewValue = List.Accumulate(lstPosReverse, col, (lstPosReverseParam,colParam)=> Text.RemoveRange(lstPosReverseParam,colParam,Text.Length(row[Acronyme]))) // see below explaination
in strNewValue,
{"Nom", "Volume", "Circulating Supply"})
Table.ReplaceValue( #"Order col", each _ , null,
...
{"Nom", "Volume", "Circulating Supply"})
Context parameters
- #"Order col" :1rst parameter of Table.ReplaceValue = Table source for processing
- each _ : 2nd parameter of Table.ReplaceValue = indicates that Table.RepalceValue is applied on each record of #"Order col"
- {"Nom", "Volume", "Circulating Supply"} : 5th parameter of Table.ReplaceValue = list of columns which are processed
(col,row,z) => let
...
in
Declaration and appeal of the custom Replacing function with 3 parameters mandatory.
Considering its MSN definition
Table.ReplaceValue(table as table, oldValue as any, newValue as any, replacer as function, columnsToSearch as list) as table
Replacer function proposes 2 natives methods
Replacer.ReplaceText(text as nullable text, old as text, new as text) as nullable text
Replacer.ReplaceValue(value as any, old as any,new as any) as any
I assume that by declaring a custom replace function, a new method is added to the replacer object. But the construction of the replacer object, while allowing the addition of a new method, requires the new method to pass 3 parameters in order to be efficient. In fact, I've tested with 2 parameters: if there's no error, there's no action.
- Col : 5th parameter of Table.ReplaceValue : {"Nom", "Volume", "Circulating Supply"}
- row : 2nd parameter of Table.ReplaceValue : each _
- z : 3rd parameter of of Table.ReplaceValue : null
- ( )=> : Define and call the new method which is call for each row (param 2nd) and columns (param. 5th)
- let ... in : For assigning a result to a declared variables
- lstPos, lstPosReverse, strNewValue : declared variables
**************************************************************************************************************
List.Accumulate(lstPosReverse, col, (lstPosReverseParam,colParam)=> Text.RemoveRange(lstPosReverseParam,colParam,Text.Length(row[Acronyme])))
- List.Accumulate(lstPosReverse, col, (lstPosReverseParam,colParam)=> lines code)
For the context of the current row (each _) and one field of param 5th of current row - lstPosReverse : list of iteration position for the current context (see above)
- col : col of the current context
- (lstPosReverseParam,colParam)=> : Declaration and call of a custom Accumulate method for current context
- lstPosReverseParam : param 1st of List.Accumulate passed to the custom Accumulate method for current context
- colParam : param 2nd of List.Accumulate passed to the custom Accumulate method for current context
Text.RemoveRange(lstPosReverseParam,colParam,Text.Length(row[Acronyme])) : custom Accumulate method - List.Accumulate (..., ..., (lstPosReverseParam,colParam)=> ... ) : Accumulate method called for each list position for the context
considering the following context : Current line = index 105, field [Acronym] = “RON”, processed field [Name]
- “RON” exists twice in [Name] because PositionOf is declared case insensitive.
- List of indexes of each [Acronnym] value found in the [Name] field.
a position correspondS to the first letter position of the value of [Acronym] found in the target field; So lstPosReverse is {5, 0} because modified with List.Reverse.
To check Remove "//" and launch => strNewValue = lstPosReverse - For the 1st loop {5}, the value of the context column [Name] = “RoninRON”, is passed as the initialization value for processing, which returns Ronin”.
- For the second loop {0}, the value to be processed = “Ronin” and return = “in”.
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