1 ACCEPTED SOLUTION
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.
Hi @informer, another approach:
Output
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("JYyxDoMwDER/JUKMN8RJcOKxolulIgHqghg6MFSVgkRpv78OLGfr7t1NU9V3937Nr1yhvKp1Q0gUEEi2NZvAELbwwpoxCXwSsIgp9IypGvbn9s7L/hn7myLDeWpS1CWPKC40EdYFFMJI8Bo7NMnBRgY1yRydstV+t99irpfOjOt7yW3/UPbU2luvgwTLupe0r6OczJkSnHpOIgLT4c3zHw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Nom = _t, Acronyme = _t, Volume = _t, #"Circulating Supply" = _t]),
RemovedAcronyme = [ a = List.RemoveMatchingItems(Table.ColumnNames(Source), {"Acronyme"}),
b = Table.TransformRows(Source, each
Record.TransformFields(_, List.Transform(a, (x)=> { x, (y)=> Text.RemoveRange(y, Text.PositionOf(y, [Acronyme], Occurrence.First, Comparer.OrdinalIgnoreCase), Text.Length([Acronyme])) } ))),
c = Table.FromRecords(b, Value.Type(Table.FirstN(Source, 0)))
][c]
in
RemovedAcronyme
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.
Hi Ronrsnfld
Thanks again for your help and solution. To clarify my understanging, I rewrote your code to be more explicit as below:
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"})
MY PERSONAL DETAILED UNDERSTANDING
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.
Here is my personal explanation on the obligation to pass 3 parameters. I apologize to experts for this very rough or false explanation...
Considering its MSN definition
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”.
