Forum Discussion
If statement with double condition
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 nullNOTE 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.
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
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?
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
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.
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 nullNOTE 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.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. 😉
