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.
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”.
Helpful resources
Join us at the Microsoft Fabric Community Conference
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Power BI Monthly Update - February 2025
Check out the February 2025 Power BI update to learn about new features.
Fabric Community Update - February 2025
Find out what's new and trending in the Fabric community.
