Power Query -Delete column substring corresponding to the text of another column (insensitive case)

[informer]

Hey

I have the data below in table Table1 whose data was loaded from a web page

Nom	Acronyme	Volume	Circulating Supply
RONRonin	RON	$51,814,419ron 46,960,396	619,389,699 RON
StarknetsTRK	STRK	$119,283,792457,024,sTRK 943	2,582,076,158 STRK
Curve DAO TokenCRV	CRV	$303,281,064578,587,068 CRV	1,278,297,461 CRV

And for each row, I want to remove the Acroyme text in the Name, Volume, Circulating Supply columns.

But the search for the value of the Acronym must be done in INSENSITIVE CASE and without any particular position because the Acroyme value can be all in lower case or with the 1st letter in capital letters in different colums.

I have a solution only for numeric values

= Table.ReplaceValue(#"Order col",each [Acronyme],null,
    (x,y,z) as text=>
        Text.Trim(Text.Select(x, {"1".."9"} )),
    {"Volume","Circulating Supply"}
)

Thanks by advance

[dufoq3]

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

[Omid_Motamedise]

Hi,
Use this formula or see the attached file

= Table.ReplaceValue(#"Changed Type",each [Acronyme],"",Replacer.ReplaceText,{"Volume","Nom","Circulating Supply"})

[Omid_Motamedise]

Soory, initially I didnt catch you are searching for Case Insensitive solution, 
you can use this solution but it convert all your texts  into the lower cases

= Table.ReplaceValue(#"Changed Type",each [Acronyme],"",(a,b,c)=>Text.Replace(Text.Lower(a),Text.Lower(b),c),{"Volume","Nom","Circulating Supply"})

[ronrsnfld]

@informer 

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.

[BeaBF]

@informer Hi! Try with:

= Table.TransformColumns(
#"Order col",
{"Nom", "Volume", "Circulating Supply"},
each (textValue) =>
Text.Trim(
Text.Replace(
Text.Replace(
Text.Replace(textValue, Text.Lower(_[Acronyme]), "", Comparer.OrdinalIgnoreCase),
Text.Upper(_[Acronyme]), "", Comparer.OrdinalIgnoreCase
),
_[Acronyme], "", Comparer.OrdinalIgnoreCase
)
),
type text
)

BBF

[informer]

Hi BeaBF

Thanks for your help.

I don't understant what is "textValue" and there is the code error message below

Expression.Error: Sorry... We were unable to convert Type Type to Type Number.
Details:
Value=[Type]
Type=[Type]