Converting a post number into a negative

[Vegas01]

Hi everyone

I was wondering is there anyway to convert the numbers with a CR to a negative while leaving the rest to a positive. I am currently working in Power Query.

Thanks in advance

 

[Alex Blakenburg]

Try
Replace $ with -
Then Replace CR with "" / nothing
Change data type to a numeric one eg currency

 

[Vegas01]

Alex

Thanks for help but that did not work. The formula is below, I have a thought that it has something to do with the data type of the column which is currently "Any" I have change the data type to a couple of different things but it did not work. Any suggestions?

= Table.AddColumn(#"Filtered Rows", "Custom.1", each if [Inc GST] = "$" then "-" else [Inc GST])

 

[Alex Blakenburg]

See if this works for you.
If your Filtered Rows is the last step before the final "in" statement then you may just be able to copy this in after the Filtered Rows replacing your current in statement.
I have assumed you are using en-US just change the last 2 characters if that is not the case.

    #"Inserted Text Between Delimiters" = Table.AddColumn(#"Filtered Rows", "Text Between Delimiters", each Text.BetweenDelimiters(Text.From([Inc GST], "en-US"), "$", "C"), type text),
    #"Added Conditional Column" = Table.AddColumn(#"Inserted Text Between Delimiters", "Custom", each if [Text Between Delimiters] <> "" then -Number.FromText([Text Between Delimiters]) else [Inc GST])
in
    #"Added Conditional Column"

I will try to walk you through what I did.
1) Add 1st Column
Select Inc GST column
Add Column > Extract > Text Between Delimiters
Start delimiter = $
End delimiter = C

2) Add 2nd Column
2.1) Select new column "Text Between Delimiters"
Add Column > Conditional Column
if Text Between Delimiter is empty / blank (see below) then use that value else use the Inc GST value
2.2) Modify the M Code
Change what you see between the "then" and the "else"
from: then [Text Between Delimiters] else
to: then -Number.FromText([Text Between Delimiters]) else
(NOTE: the minus sign at the start before Number.From)

*** additional clean up steps ***
3) Change Data Type to Decimal or Currency as required
4) Remove columns no longer required
5) Rename Custom column to Inc GST