Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
I used an if function with 'and' but it doesn't give me any result.
Thanks in advance for your help.
greetings
Clélia
Solved! Go to 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.
Proud to be a 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
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
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 # .
The result is correct for the "klant" but for the "Leverancier". It gives an error?
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.
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. 😉
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