Neer Help on Power Query Nested If statement

[spycein]

Hello Everyone,

I have column in my data set as follows:

100

200

300

400 Cr

500

600

200 Cr

I am looking for a power query formula which would return the following values:
(If cell contains "Cr" then remove "Cr" and multiply by -1).

100.00

200.00

300.00

-400.00

500.00

600.00

-200.00

Thank you so much in advance.

Regards
Shib

 

[alansidman]

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Split Column by Delimiter" = Table.SplitColumn(Table.TransformColumnTypes(Source, {{"Column1", type text}}, "en-US"), "Column1", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Column1.1", "Column1.2"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Column1.1", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if [Column1.2] ="Cr" then [Column1.1]*-1 else [Column1.1]),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Custom"})
in
    #"Removed Other Columns"