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   Nom Acronyme Volume Circulating Supply RONRonin RON $51,814,419ron 46,960,396 619,389,699 RON Stark...
Need Help
  • ronrsnfld's avatar
    ronrsnfld
    1 year ago

    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.

dufoq3's avatar
dufoq3
Community Champion
1 year ago

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
    1 year ago

    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
      1 year ago

      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.