Forum Discussion

informer's avatar
informer
Icon for Helper I rankHelper 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.

informer's avatar
informer
Icon for Helper I rankHelper 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
Icon for Community Champion rankCommunity 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.