Forum Discussion

informer's avatar
informer
Helper I
1 year ago
Solved

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

 

NomAcronymeVolumeCirculating Supply
RONRoninRON$51,814,419ron 46,960,396619,389,699 RON
StarknetsTRKSTRK$119,283,792457,024,sTRK 9432,582,076,158 STRK
Curve DAO TokenCRVCRV$303,281,064578,587,068 CRV1,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

  • 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.

11 Replies

  • 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.

    • informer's avatar
      informer
      Helper I
      Hi 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 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

       

      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”.
      • ronrsnfld's avatar
        ronrsnfld
        Super 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 =>

         

    • Omid_Motamedise's avatar
      Omid_Motamedise
      Super 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"})

  • dufoq3's avatar
    dufoq3
    Community 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

     

    • informer's avatar
      informer
      Helper 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
      • dufoq3's avatar
        dufoq3
        Community 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.

  • BeaBF's avatar
    BeaBF
    Super 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-priyankata's avatar
    v-priyankata
    Community 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.