Table.ReplaceValue + Text.removeRange for mutiple columns

[informer]

Hey

FOr different columns, the data columns {Name, Volume(24h), Cirulation Supply} ended with the same acronyms. I wrote a script for each one.

For example, line 1 the 3 columns ended with BTC = value of {Acronyme}

I wrote a script for the {Name] column

= Table.ReplaceValue(#"Add col Nom",
each [Name],
each Text.RemoveRange([Name],Text.Length([Name])-Text.Length([Acronyme]), Text.Length([Acronyme])),
Replacer.ReplaceText,
{"Name"}
)

But is it possible to write one script for 3 columns ?

Thanks a lot

[DataNinja777 · Accepted solution]

Hi @informer ,

Yes, it is possible to write a single script to apply the transformation to multiple columns dynamically using Table.TransformColumns in Power Query (M language). Instead of repeating Table.ReplaceValue for each column, you can define a list of target columns and apply the same transformation logic to all of them. The following script accomplishes this by using List.Transform to iterate over each column and applying Text.RemoveRange to remove the acronym:

let
    Source = #"Add col Nom",
    ColumnsToTransform = {"Name", "Volume(24h)", "Circulation Supply"},
    TransformData = Table.TransformColumns(
        Source,
        List.Transform(ColumnsToTransform, 
            each {_, 
                each Text.RemoveRange(_, Text.Length(_) - Text.Length([Acronyme]), Text.Length([Acronyme])),
                type text
            }
        )
    )
in
    TransformData

This method ensures that all specified columns are processed in one step, making the script more efficient and easier to maintain. If additional columns require the same transformation, they can simply be added to the ColumnsToTransform list without modifying the core logic.

Best regards,

[informer]

Hi DataNinja777 and Omid Motamedise 

Thanks a lot for your help and solution

I copy & paste the script in Personnalisé1 step. But after lanchuning it, the data in the columns list are on error (See the copy screen)

I join the data before Personnalisé1 step

/*

Hi DataNinja777 and Omid Motamedise 

Thanks a lot for your help and solution

I copy & paste the script in Personnalisé1 step. But after lanchuning it, the data in the columns list are on error (See the copy screen)

I join the data before Personnalisé1 step

/*

Hi DataNinja777 and Omid Motamedise 

Thanks a lot for your help and solution

I copy & paste the script in Personnalisé1 step but after lanching it, the data in the columns list are on error (See the copy screen)

I join below data before Personnalisé1 step

id;Nom;Acronyme;PrixUnitaire;Capitalisation;Volume;Change;Circulating Supply
1;BitcoinBTC;BTC;$101,946.45;$2.02T$2,020,375,331,878;$43,412,968,006425,819 BTC;2.69%;19,818,006 BTC
2;EthereumETH;ETH;$3,243.54;$390.91B$390,913,228,451;$29,677,443,3039,151,374 ETH;2.37%;120,520,666 ETH
3;XRPXRP;XRP;$2.98;$172.06B$172,058,175,225;$4,448,069,1861,492,858,462 XRP;3.77%;57,703,732,191 XRP
4;TetherUSDT;USDT;$0.9997;$139.36B$139,362,076,851;$94,998,920,62895,001,678,111 USDT;0.04%;139,404,246,312 USDT
5;SolanaSOL;SOL;$227.57;$110.8B$110,795,805,603;$3,936,270,73717,306,557 SOL;4.39%;486,869,936 SOL
6;BNBBNB;BNB;$670.54;$95.54B$95,539,467,042;$1,643,578,8542,451,115 BNB;1.58%;142,480,661 BNB
7;USDCUSDC;USDC;$1.00;$53.31B$53,309,349,303;$8,456,209,6118,453,724,775 USDC;0.01%;53,306,507,014 USDC
8;DogecoinDOGE;DOGE;$0.3247;$48.01B$48,007,630,401;$1,828,338,7505,632,653,217 DOGE;2.03%;147,864,046,384 DOGE
9;CardanoADA;ADA;$0.9306;$32.74B$32,743,243,066;$687,153,589738,918,067 ADA;3.86%;35,185,844,017 ADA
10;TRONTRX;TRX;$0.2535;$21.84B$21,836,885,012;$625,523,1782,469,415,172 TRX;0.38%;86,125,622,035 TRX
 

[ronrsnfld · Accepted solution]

Now that you have provided a usable data sample, it is apparent you can remove the Acronymes in a single step.

Enter the code below as a step after the last step in your query that shows the table sample you provided. Replace #"Previous Step" with the name of the actual previous step.

You can see from the screenshots it has removed the Acronyme from each of those three columns

#"Remove Acronyme" = Table.ReplaceValue(
        #"Previous Step",
        each [Acronyme],
        null,
        (x,y,z) as text=> if Text.EndsWith(x,y) 
                            then Text.Trim(
                                Text.ReplaceRange(
                                    x,Text.Length(x)-Text.Length(y),Text.Length(y),"" )) 
                            else x,
        {"Nom","Volume","Circulating Supply"})

#"Previous Step"

#"Remove Acronyme"

[Omid_Motamedise]

Yes, besides the solution reccommended by @DataNinja777 , You can also apply the loop in Power Query any time using List.Acumulate

for more information, see the following video

https://www.youtube.com/watch?v=G8PRbWuDcmQ

[DataNinja777 · Accepted solution]

Hi @informer ,

Yes, it is possible to write a single script to apply the transformation to multiple columns dynamically using Table.TransformColumns in Power Query (M language). Instead of repeating Table.ReplaceValue for each column, you can define a list of target columns and apply the same transformation logic to all of them. The following script accomplishes this by using List.Transform to iterate over each column and applying Text.RemoveRange to remove the acronym:

let
    Source = #"Add col Nom",
    ColumnsToTransform = {"Name", "Volume(24h)", "Circulation Supply"},
    TransformData = Table.TransformColumns(
        Source,
        List.Transform(ColumnsToTransform, 
            each {_, 
                each Text.RemoveRange(_, Text.Length(_) - Text.Length([Acronyme]), Text.Length([Acronyme])),
                type text
            }
        )
    )
in
    TransformData

This method ensures that all specified columns are processed in one step, making the script more efficient and easier to maintain. If additional columns require the same transformation, they can simply be added to the ColumnsToTransform list without modifying the core logic.

Best regards,