Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
CleliaComes
Helper I
Helper I

If statement with double condition

Hi 

 

I am learning Power Query and Power BI and I have some troubles using an if function with a double condition.

A sample of the data below.

 

In column "Type rekening"  there is a label "klant/leverancier" (other labels are "klant" and "leverancier"). I want to get "Klant" in a new column when "cash in- cash out" > 0 and "Leverancier" when "cash in - cash out" <0.

 
 

power query.JPG

I used an if function with 'and' but it doesn't give me any result.

if function.JPG

Thanks in advance for your help.

 

greetings 

Clélia

 

 

1 ACCEPTED SOLUTION

Hi @CleliaComes 

This query does what you are asking. Copy paste into PBI Advanced Query editor.  It contain a snapshot of your data as shown in your image.

 

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WyivNyVHSgVGeeSWpRXmpCpllSToKBYlF2alFqXk6CkmJednZ+cUlOgqpJcl6QHUGSrE60UreOYl5JQjNMK6hobmesQVChYK+gk9qWWpRYl5yZmoRmnoMSV0zCz1TQ7BurLrQVBsbGFroWRiSa5uJqZ6ZpVJsLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Type rekening" = _t, Categorie = _t, #"Type rekening + categorie" = _t, #"Cash in - cash out" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Type rekening", type text}, {"Categorie", type text}, {"Type rekening + categorie", type text}, {"Cash in - cash out", type number}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if [Type rekening] = "Klant / Leverancier" and [#"Cash in - cash out"] > 0 then "Klant" else if [Type rekening] = "Klant / Leverancier" and [#"Cash in - cash out"] < 0 then "Leverancier" else null)
in
    #"Added Custom"

 

 

If you want to just add the single line of code to your file to create the Custom Column, this is the line to enter into the Create Custom Column dialog box

 

if [Type rekening] = "Klant / Leverancier" and [#"Cash in - cash out"] > 0 then "Klant" else if [Type rekening] = "Klant / Leverancier" and [#"Cash in - cash out"] < 0 then "Leverancier" else null

 

 

NOTE Your data shows the text string "Klant / Leverancier" but the code you tried to enter for the if statement showed "Klant/Leverancier" - notice the lack of spaces between the words.  Make sure you are checking for the correct text. My query checks for the text as shown in your data.

Phil


If I answered your question please mark my post as the solution.
If my answer helped solve your problem, give it a kudos by clicking on the Thumbs Up.



Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.


Blog :: YouTube Channel :: Connect on Linkedin


Proud to be a Super User!


View solution in original post

10 REPLIES 10
AnkitKukreja
Super User
Super User

Hi @CleliaComes 

 

Your formula is correct, I believe there might be some trailing or leading spaces in your text column that might have caused issue.

 

Please use the below formula as your custom formula:

 

if[Type rekening] = "Klant / Leverancier" and [#"Cash in - Cash Out"] > 0 then "Klant" else if [Type rekening] = "Klant / Leverancier" and [#"Cash in - Cash Out"] < 0 then "Leverancier" else null

 

Thanks,

Ankit

If my response has successfully addressed your question or concern, I kindly request that you mark this post as resolved. Additionally, if you found my assistance helpful, a thumbs-up would be greatly appreciated.
Thanks,
Ankit Kukreja
www.linkedin.com/in/ankit-kukreja1904
AlB
Super User
Super User

Hi @CleliaComes 

Why are you using [#"Type rekening"] insted of [Type rekening] and 

[#"Cash in - cash out"] instead of  [Cash in - cash out] ?

Are Type rekening and Cash in - cash out not the names of the columns. If so, use them as they are.

 

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

SU18_powerbi_badge

Anonymous
Not applicable

Hi @AlB 

Correct me if I am wrong.  I think power query will auto matically add "#" before the columm names where the special characters are in the column name, such as " ", "-" etc. 

 

@Anonymous 

Yes, you are absolutely correct

Thanks for your quick answer! 

 

I noticed [#    ] this as well. I just selected the column and the # was placed automatically.

 

However I chanced the column names and now I can add the column names without # .  

 

 cashin.JPGcashin4.JPG

 

The result is correct for the "klant" but for the "Leverancier". It gives an error?

result3.JPG

 

 

Hello @CleliaComes 

 

you a referencing another column, not the one you are marking in the screenshot. The formula is perfectly fine, but I suppose that cash-in out in your case for the line with error doesn't show a number but something else

 

BR

 

Jimmy

Hoi, @CleliaComes , plak maar jouw M code of voeg jouw pbix bestaand bij zodat het veel makkeljiker is om de probleme op te lossen.


Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension!

DAX is simple, but NOT EASY!

Hi @CleliaComes 

This query does what you are asking. Copy paste into PBI Advanced Query editor.  It contain a snapshot of your data as shown in your image.

 

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WyivNyVHSgVGeeSWpRXmpCpllSToKBYlF2alFqXk6CkmJednZ+cUlOgqpJcl6QHUGSrE60UreOYl5JQjNMK6hobmesQVChYK+gk9qWWpRYl5yZmoRmnoMSV0zCz1TQ7BurLrQVBsbGFroWRiSa5uJqZ6ZpVJsLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Type rekening" = _t, Categorie = _t, #"Type rekening + categorie" = _t, #"Cash in - cash out" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Type rekening", type text}, {"Categorie", type text}, {"Type rekening + categorie", type text}, {"Cash in - cash out", type number}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if [Type rekening] = "Klant / Leverancier" and [#"Cash in - cash out"] > 0 then "Klant" else if [Type rekening] = "Klant / Leverancier" and [#"Cash in - cash out"] < 0 then "Leverancier" else null)
in
    #"Added Custom"

 

 

If you want to just add the single line of code to your file to create the Custom Column, this is the line to enter into the Create Custom Column dialog box

 

if [Type rekening] = "Klant / Leverancier" and [#"Cash in - cash out"] > 0 then "Klant" else if [Type rekening] = "Klant / Leverancier" and [#"Cash in - cash out"] < 0 then "Leverancier" else null

 

 

NOTE Your data shows the text string "Klant / Leverancier" but the code you tried to enter for the if statement showed "Klant/Leverancier" - notice the lack of spaces between the words.  Make sure you are checking for the correct text. My query checks for the text as shown in your data.

Phil


If I answered your question please mark my post as the solution.
If my answer helped solve your problem, give it a kudos by clicking on the Thumbs Up.



Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.


Blog :: YouTube Channel :: Connect on Linkedin


Proud to be a Super User!


PhilipTreacy Thank you very much for your help.  I thought the formula was wrong but it was indeed just the lack of spaces between the words.  In the future I will first check spacing before posting. 😉

@CleliaComes 

What does the error message say? Click on any of them and  it should show, probably at the botttom, under the table

 

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

 

SU18_powerbi_badge

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors