PQ - Extract numbers from column with condition
(question from the web)
Quote:
If cell contains "Cr" then remove "Cr" and multiply by -1
| Source |
|
Result |
| raw |
|
Number |
100 |
|
100 |
200 |
|
200 |
300 |
|
300 |
| 400 Cr |
|
-400 |
500 |
|
500 |
600 |
|
600 |
| 200 Cr |
|
-200 |
Code:
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Text = Table.TransformColumnTypes(Source,{{"raw", type text}}),
Number = Table.AddColumn(Text, "Number", each try Number.From([raw]) otherwise Number.From(Text.Select([raw],{"0".."9"})) * -1),
RC = Table.RemoveColumns(Number,{"raw"})
in
RC
