excel - 从混合字符串中提取数字，然后在 Power Query 中有条件地进行一些计算

Question

直奔“问题”。

我有一个“简单”的任务要尝试在 Power BI > Power Query 中解决，其中我有一个混合列应该是一个 Amount 列，有很多不同的数字/文本/特殊字符组合，必须是先转换成数字格式再条件计算，如果有“brutto(不区分大小写)一般”，则将该值除以1.5，如果有CHF值，则将该值除以1.9。可以删除其余数据，如错误、#values 甚至空值。预期列在我的查询步骤中显示为 OnlyAmountNr。

这是我的数据的简短示例:

| Amount          | OnlyAmountNr |
|-----------------|--------------|
| Brutto ? 468384 | 468384       |
| 965-            | 965          |
| 703623 brutto   | 703623       |
| 654-? Netto     | 654          |
| ?1738           | 1738         |
| CHF     12800.0 | 12800        |
| #VALUE!         | -            |
| 89989           | 89989        |
| 0               | 0            |

我使用的，至少据我所知是这些步骤，对于这种任务来说可能太长了:

     let
        Quelle = Sql.Databases("ukTest123.database.windows.net"),
        CSD_DE_RawData = Quelle{[Name="CSD_DE_RawData"]}[Data],
        money_ASD_Applications = 
        CSD_DE_RawData{[Schema="money",Item="ASD_Applications"]}[Data],
        EXTRACT_SPECIAL_CHARS = Table.AddColumn(money_ASD_Applications, "Custom1", each Text.Select([Amount],{"A".."z","0".."9"})),
        EXTRACT_NUMBERS = Table.AddColumn(EXTRACT_SPECIAL_CHARS, "Custom2", each Text.Select([Custom1],{"a".."z","A".."Z"})),
        EXTRACT_T_F = Table.AddColumn(EXTRACT_NUMBERS, "Boolean", each if Text.Contains([Custom1], "brutto") then true else if Text.Contains([Custom1], "Brutto") then true else false),
        EXTRACT_TEXT = Table.AddColumn(EXTRACT_T_F, "OnlyAmountNr", each Text.Remove([Custom1],Text.ToList(Text.Remove([Custom1],{"0".."9"})))),
        #"Changed Type" = Table.TransformColumnTypes(EXTRACT_TEXT,{{"OnlyAmountNr", type number}}),
        #"CALCULATION_MwSt." = Table.AddColumn(#"Changed Type", "MwSt._CH_DE", each if [OnlyAmountNr]> 1 and [Boolean]= true then ([OnlyAmountNr]/1.199) else if [OnlyAmountNr]>1 and [Custom2]="CHF" then ([OnlyAmountNr]/1.077) else null)
     in
        #"CALCULATION_MwSt."

Answer 1

我使用实现正则表达式的自定义函数来提取数字。然后添加一个自定义列来实现逻辑。

我使用了您问题中的逻辑，而不是您代码中的逻辑。在您的代码中，您有不同的除数。另外，我不确定如何处理 Amount 不包含 brutto(不区分大小写)或 CHF 的数字，因此，根据您的问题陈述，我将它们设置为 null，但如果这不是您真正想要的，则很容易补救(只需在自定义列公式中将 else null 更改为 else n)

自定义函数
重命名 fnRegexExtract

//Rename fnRegexExtract

let   fx=(text,regex)=>
    Web.Page(
        "<script>
            var x='"&text&"';
            var y=new RegExp('"&regex&"');
            var b=x.match(y);
            document.write(b);
        </script>")[Data]{0}[Children]{0}[Children]{1}[Text]{0}

in
fx

主要代码

let

//change next line to reflect actual data source
    Source = Excel.CurrentWorkbook(){[Name="Table7"]}[Content],
    
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Amount", type text}}),
    
    #"Invoked Custom Function" = Table.AddColumn(#"Changed Type", "Number Only", each let 
        n =Number.From(fnRegexExtract([Amount], "\\d+")),
        result = 
          if Text.Contains([Amount],"brutto",Comparer.OrdinalIgnoreCase) then n/1.5
              else if Text.Contains([Amount],"CHF") then n/1.9 
              else null 
            in result, type number)
in
    #"Invoked Custom Function"

或者，将 else null 更改为 else n