title: Extract numbers from the mixed string and conditionally do some calculations afterwards in Power Query
body: <p>Straight to the &quot;issue&quot;.</p>
  <p>I got one &quot;simple&quot; task to try to solve in Power BI &gt; Power Query, where I got one mixed column supposed to be an Amount column, with lot of different number/text/special chars combinations, which have to be firstly converted into number format and then conditionally calculated if there is &quot;brutto (no case sensitive) in general&quot;, then divide that value with 1.5 and if there is CHF value divide that value with 1.9. The rest of data, like errors, #values or even nulls can be removed. Expected column is shown as OnlyAmountNr and in my Query steps.</p>
  <p>This is short example of my data:</p>
  <pre><code>| Amount          | OnlyAmountNr |
  |-----------------|--------------|
  | Brutto ? 468384 | 468384       |
  | 965-            | 965          |
  | 703623 brutto   | 703623       |
  | 654-? Netto     | 654          |
  | ?1738           | 1738         |
  | CHF     12800.0 | 12800        |
  | #VALUE!         | -            |
  | 89989           | 89989        |
  | 0               | 0            |
  </code></pre>
  <p>What I used, at least to my knowledge are these steps,which might be too long for this kind of task:</p>
  <pre><code>     let
          Quelle = Sql.Databases(&quot;ukTest123.database.windows.net&quot;),
          CSD_DE_RawData = Quelle{[Name=&quot;CSD_DE_RawData&quot;]}[Data],
          money_ASD_Applications = 
          CSD_DE_RawData{[Schema=&quot;money&quot;,Item=&quot;ASD_Applications&quot;]}[Data],
          EXTRACT_SPECIAL_CHARS = Table.AddColumn(money_ASD_Applications, &quot;Custom1&quot;, each Text.Select([Amount],{&quot;A&quot;..&quot;z&quot;,&quot;0&quot;..&quot;9&quot;})),
          EXTRACT_NUMBERS = Table.AddColumn(EXTRACT_SPECIAL_CHARS, &quot;Custom2&quot;, each Text.Select([Custom1],{&quot;a&quot;..&quot;z&quot;,&quot;A&quot;..&quot;Z&quot;})),
          EXTRACT_T_F = Table.AddColumn(EXTRACT_NUMBERS, &quot;Boolean&quot;, each if Text.Contains([Custom1], &quot;brutto&quot;) then true else if Text.Contains([Custom1], &quot;Brutto&quot;) then true else false),
          EXTRACT_TEXT = Table.AddColumn(EXTRACT_T_F, &quot;OnlyAmountNr&quot;, each Text.Remove([Custom1],Text.ToList(Text.Remove([Custom1],{&quot;0&quot;..&quot;9&quot;})))),
          #&quot;Changed Type&quot; = Table.TransformColumnTypes(EXTRACT_TEXT,{{&quot;OnlyAmountNr&quot;, type number}}),
          #&quot;CALCULATION_MwSt.&quot; = Table.AddColumn(#&quot;Changed Type&quot;, &quot;MwSt._CH_DE&quot;, each if [OnlyAmountNr]&gt; 1 and [Boolean]= true then ([OnlyAmountNr]/1.199) else if [OnlyAmountNr]&gt;1 and [Custom2]=&quot;CHF&quot; then ([OnlyAmountNr]/1.077) else null)
       in
          #&quot;CALCULATION_MwSt.&quot;
  </code></pre>