Replace value, transform type and apply math for each changed cell in power query

I work for a company that recieves analitycal data, and the values that they return that are less then the detection limit of the equipment is written as "<#", .

The statistical procedure is to all results is to divide those numbers by two.

So for an example: Gold values less them 5 ppb are written as <5, and the output should be 2.5 Silver values less them 3 ppm are written as <3, and the output should be 1.5

I didn't want to hardcode it by checking all the columns, because there are alot of elements and different detection limits as well, like adding step for each column after running the next code:

= Table.ReplaceValue(#"Renamed Columns","<","",Replacer.ReplaceText,{"Batch_ID", "Type", "Sample ID", "Peso_Bruto_g", "FAA505_Au_ppb", "FAA505_Au1_ppb", "FAA505_Au2_ppb", "ICP40B_Ag_ppm", "ICP40B_Al_pct", "ICP40B_As_ppm", "ICP40B_Ba_ppm", "ICP40B_Be_ppm", "ICP40B_Bi_ppm", "ICP40B_Ca_pct", "ICP40B_Cd_ppm", "ICP40B_Co_ppm", "ICP40B_Cr_ppm", "ICP40B_Cu_ppm", "ICP40B_Fe_pct", "ICP40B_K_pct", "ICP40B_La_ppm", "ICP40B_Li_ppm", "ICP40B_Mg_pct", "ICP40B_Mn_pct", "ICP40B_Mo_ppm", "ICP40B_Na_pct", "ICP40B_Ni_ppm", "ICP40B_P_pct", "ICP40B_Pb_ppm", "ICP40B_S_pct", "ICP40B_Sb_ppm", "ICP40B_Sc_ppm", "ICP40B_Se_ppm", "ICP40B_Sn_ppm", "ICP40B_Sr_ppm", "ICP40B_Th_ppm", "ICP40B_Ti_pct", "ICP40B_Tl_ppm", "ICP40B_U_ppm", "ICP40B_V_ppm", "ICP40B_W_ppm", "ICP40B_Y_ppm", "ICP40B_Zn_ppm", "ICP40B_Zr_ppm"})

Solution

It's just a matter of creating a list of the applicable columns, which I cannot show you since you chose not to post any example, but the general process could be:

let

//Change next line to reflect data source
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],

//Change next line to reflect only columns that need transformations
//  Could use List.RemoveFirstN, or List.Range.
//    depends on your actual data table
    #"Columns to transform" = Table.ColumnNames(#"Source"),

//Create list of transformations for each applicable column
    Transforms = List.Transform(#"Columns to transform", 
        (cn)=>{cn, each try Number.From(Text.Split(_,"<"){1})/2 otherwise _, type number}),

//Apply the transforms
    #"Fix less than" = Table.TransformColumns(Source, Transforms)
    
in
    #"Fix less than"