How to Separate a column into two by positive and negative numbers?

[Lenescarb]

As the subject lines says I'm trying to use power query to create a new column that allows me to seperate amount totals by positive and negative.

For Example Im trying to go from this:

To This: 

[rohit_singh · Accepted solution]

Hi @Lenescarb ,

Please try the following in Power Query. 

1) Load the data into PQ

2) Split column "Amount" by delimiter "-". This will generatetwo columns "Amount.1" and "Amount.2"

3) Multiply column "Amount.2" by -1 to get negative values in the column

Below are the steps :

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("nZA9D4IwGIT/SsOM0JavMlYo2ESpqVUj6EBMow5Agjror7dM6qIJ292by5N7r6qswEWeiyHGlm0h7PlBGJHY6KVkdK1mQvKSpYAmM5BIlnI1xILQiSMjkq451tebdbDHcSJMPIcMJKr2dwhxqMaySABtSIzY6P7y7FrwE1ROKUjZlCugJC1WGZPmOEHExjF04iEt+ro9aXA7665//GW9X/pAZMv57xZirXLBixwsRMF2X1V8FPrId6BnzNZMrEFTt/VJN7o1cx9e", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Post Date" = _t, #"Account Number " = _t, #"Transaction Description" = _t, Amount = _t, #"Transaction Detail" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Account Number ", Int64.Type}, {"Transaction Description", type text}, {"Amount", type number}, {"Transaction Detail", type text}}),
#"Split Column by Delimiter" = Table.SplitColumn(Table.TransformColumnTypes(#"Changed Type", {{"Amount", type text}}, "en-GB"), "Amount", Splitter.SplitTextByDelimiter("-", QuoteStyle.Csv), {"Amount.1", "Amount.2"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Amount.1", type number}, {"Amount.2", type number}}),
#"Multiplied Column" = Table.TransformColumns(#"Changed Type1", {{"Amount.2", each _ * -1, type number}})
in
#"Multiplied Column"

Kind regards,

Rohit

Please mark this answer as the solution if it resolves your issue.
Appreciate your kudos! 😊