Neer Help on Power Query Nested If statement

[coolshib]

Hello Everyone,
I have column in my data set which is 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

[Jimmy801 · Accepted solution]

Hello @coolshib 

here another approach. Add a new column with this formula. It checks if your text is containing "Cr" then text is splitted by " " and using the first part and multiplying with -1 otherwise not

if Text.Contains([Column1],"Cr") then Number.From(Text.Split([Column1]," "){0})*-1 else Number.From([Column1])

 Here the complete code example

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQwUIrViVYygtLGUNrEwEDBuQjMNIUKmSGUgqViAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if Text.Contains([Column1],"Cr") then Number.From(Text.Split([Column1]," "){0})*-1 else Number.From([Column1]))
in
    #"Added Custom"

Copy paste this code to the advanced editor in a new blank query to see how the solution works.

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

[coolshib]

@Jimmy801Thank you so much for the solution. This is exaclty what i wanted.

[harshnathani]

HI @coolshib ,

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQwUIrViVYygtLGUNrEwEDBuQjMNIUKmSGUgqViAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
    #"Split Column by Character Transition" = Table.SplitColumn(#"Changed Type", "Column1", Splitter.SplitTextByCharacterTransition({"0".."9"}, (c) => not List.Contains({"0".."9"}, c)), {"Column1.1", "Column1.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Character Transition",{{"Column1.1", Int64.Type}}),
    #"Added Conditional Column" = Table.AddColumn(#"Changed Type1", "Custom", each if [Column1.2] = null then [Column1.1] * 1 else [Column1.1] * -1)
in
    #"Added Conditional Column"

Split the column by Digit to Non Digit

Add a Conditional Column 

Table.AddColumn(#"Changed Type1", "Custom", each if [Column1.2] = null then [Column1.1] * 1 else [Column1.1] * -1)

Regards,
Harsh Nathani
Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

[coolshib]

Thank you so much for your response.