在power查询中替换值、转换类型和对每个更改的单元格应用数学

Question

我在一家接收分析数据的公司工作，他们返回的值小于设备的检测极限，被写为"<#"，。

统计程序是将所有结果除以这些数字。

因此，举个例子:黄金值减去5 ppb等于<5，输出应该是2.5，银值小于3 ppm，输出应该是1.5。

我不想通过检查所有列来对其进行硬编码，因为也有很多元素和不同的检测限制，比如在运行下一段代码之后为每一列添加步骤：

= 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"})

Answer 1

这只是创建一个适用列的列表的问题，我无法向您展示，因为您选择不发布任何示例，但一般过程可能是：

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"

​

​