Forum Discussion
Power Query -Delete column substring corresponding to the text of another column (insensitive case)
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
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.
11 Replies
- ronrsnfldSuper User
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.
- informerHelper IHi Ronrsnfld
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 UNDERSTANDINGTable.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 ... inDeclaration 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 definitionTable.ReplaceValue(table as table, oldValue as any, newValue as any, replacer as function, columnsToSearch as list) as tableReplacer function proposes 2 natives methods
Replacer.ReplaceText(text as nullable text, old as text, new as text) as nullable textReplacer.ReplaceValue(value as any, old as any,new as any) as anyI 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”.
- ronrsnfldSuper User
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 =>
- #"Order col" :1rst parameter of Table.ReplaceValue = Table source for processing
- Omid_MotamediseSuper User
Hi,
Use this formula or see the attached file= Table.ReplaceValue(#"Changed Type",each [Acronyme],"",Replacer.ReplaceText,{"Volume","Nom","Circulating Supply"})- Omid_MotamediseSuper User
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"})
- dufoq3Community Champion
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- informerHelper I
Thanks a lot dufoq3 for this very interesting solution.
the implementation result is an error
Expression.Error : Sorry... We were unable to convert the value"<html lang="en" dir=..." in type Table. Détails : Value=<html lang="en" dir="ltr"><head><meta charset="utf-8"><meta http-equiv="x-ua-compatible" content="ie=edge"><meta name="viewport" content="width=device-width, initial-scale=1, maximum-scale=1, user-scalable=no, shrink-to-fit=no"><meta property="og:url" content="https://coinmarketcap.com/"><link rel="canonical" href="https://coinmarketcap.com/"><link rel="alternate" hreflang="ar" href="https://coinmarketcap.com/ar/?page=2"><link rel="alternate" hreflang="bg" href="https://coinmarketcap.com/bg/?page=2"><link rel="alternate" hreflang="cs" href="https://coinmarketcap.com/cs/?page=2"><link rel="alternate" hreflang="da" href="https://coinmarketcap.com/da/?page=2"><link rel="alternate" hreflang="de" href="https://coinmarketcap.com/de/?page=2"><link rel="alternate" hreflang="el" href="https://coinmarketcap.com/el/?page=2"><link rel="alternate" hreflang="en" href="https://coinmarketcap.com/?page=2"><link rel="alternate" hreflang="es" href="https://coinmarketcap.com/es/?page=2"><link rel="alternate" hreflang="fi" hre... Type=[Type- dufoq3Community Champion
As you can see on the picture in my post - the solution works. If you don't know how to use it - read note below my post please.
- BeaBFSuper User
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
- v-priyankataCommunity Support
Hi informer,
May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.
Thank you.