Write if statements like a Pro in Power Query

[@TacticsBI]

If "Not Curbal" then "it's no Fun" :) .... Thanks Ruth 👍🏻

[@FM-qw3eo]

Ruth - I cant believe how much value that you manage to squeeze into a 12 min video... simply brilliant !

[@CurbalEN]

Wonderful to hear, thanks!

[@mihirhardas8297]

I completely agree, i was in awe when i saw the IF condition planted inside replace video!

[@CurbalEN]

😊

[@yearofmars]

Nice video. I think your countries example could be improved using List.Contains if List.Contains( {"Sweden’, ‘Spain’, ‘France"}, [origin]} then "Europe" else ‘Non-Europe’ This is much easier and more readable for long lists or lists which are references.

[@CurbalEN]

Neat! Thanks 😊

[@eq2home]

Well done. You make it seem second nature, intuitive and fun when we ended up at your site because PowerBi custom fields aren't second nature (not Excel code), intuitive or fun. I'm old, cranky and set in my ways so thanks for showing me the light at the end of the tunnel. Cheers

[@CurbalEN]

Thanks for the feedback!

[@alreadydeadfunk]

I find that IF statement gets more complex and hard to maintain once it gets more than three conditions (IF A ELSE IF B ELSE [C]). When that happens, I tend to use a little look up technique that can come in handy, replacing IF Statement. For instance, you are doing the following IF Statement condition (IF [Column1] = 1 then 'A' ELSE IF [Column1] = 2 then 'B' Else 'C'). We could change that to try { 'A', 'B'}{List.PositionOf({1, 2}, [Column1])} otherwise 'C'. Alternatively, you could set up the look up table (outside or inside the current query). For instance, you have a table contains the response to 'A' and 'B' for [Column1] like lookup = #table( { "find", "value" }, { { "A", 1 }, { "B", 2 } } ). Then look up that table by lookup{[find = [Column1]]}[value]. The good thing about using the look up table (possibly set up externally). It is easy to maintain (can add or delete conditions easily). But the downside is that lookup of the record tends to use the memory and gets time-consuming when you are dealing with thousands of lines. If so, you may want to consider using table join technique (join the lookup table with the data table), which is much faster to get the same result.

[@yearofmars]

Best comment and better than the video. Would say, List.Contains is also useful in complex conditionals for reducing verbosity.

[@lindabond9307]

Thanks Ruth, I refer to this a lot as it basically covers everything. So I'd like to share a tip for people working with dates, and current dates. You might want to create a conditional statement which compares a date to the current date / Today. The M Query Code is: *DateTime.Date( DateTime.LocalNow () )* First use the query wizard to create you code BUT write null where you want Today. For instance: If [Date] = null then "this is Today". When you look at the formula bar you can paste the M Query Code over null. For example: If [Date] = DateTime.Date( DateTime.LocalNow () ) then "this is Today". I hope this makes sense and helps with current date queries :)

[@joanneosborne2428]

Ruth, you are the BEST! 😍 I absolutely ❤ all your videos and thank you so much for helping me to understand and learn Power BI, DAX, Power Query. It's so true - If "Not Curbal" then it's no Fun" 👍

[@CurbalEN]

Thanks ☺😊!

[@jessicavea1379]

you explain so simply and immediately found my answer with your and example. thank you!!

[@CurbalEN]

Fab to hear 👏👏

[@kevinhenry1681]

Thanks SOO MUCH for showing how to customize columns in Power Query!!

[@sujitnair8395]

You always solve my problems to the point, wish i could take you on a date for that. Love from India !

[@nancmadi]

You are amazing... this was exactly what I needed to bring a current Excel static report to the next level in PBI, my old processes encompassed Excel & Access to produce accurate results, I knew there had to be a way to make it work in PBI, I hunted everywhere for how to do If statements yesterday... crickets.. today there you were in my feed...THANK YOU FOR ALWAYS MAKING THE LEARNING PROCESS EASIER.

[@CurbalEN]

And thanks for the feedback!!! Well done :)

[@vicky-mx2ku]

You are the best one stop shop to find a solutions of my problem

[@CurbalEN]

At your service! Did you know you can also search here? curbal.com/curbal-learning-portal

[@excelbelajar]

thank you so much. learn so much from your channel

[@CurbalEN]

Thanks!

[@Lyriks_]

I don't know why her face makes me happy, great video !

[@CurbalEN]

Because power query makes me happy and it is contagious 😄

[@maheshbhosale4661]

I was exactly looking for this solution to create a new column with similar conditions. Thank you very much for posting!

[@lolalola8421]

Thanks for you videos!! Can you make a video of "if/ and" as a measure with 3 tables and the if compare columns of the 3 tables?

[@AT-LT]

Just seen this video, and wow, how simple! Thanks

[@SolutionsAbroad]

Great tips Ruth, would be great to see if there is a better way to deal with nested if statements other than making them humongously ugly!

[@CurbalEN]

Like a switch function in DAX?

[@SolutionsAbroad]

@@CurbalEN great shout!

[@yearofmars]

Wrote this in above comment. Might help if you use ‘if’ with ‘List.Contains ‘ to reduce the verbosity.

[@prawonalewo5689]

Hello M’am could you tell me how the syntax should look when i want to break my if function?

[@magnusvangekjrchristensen1996]

You are simply the best Power BI tutor out there, Ruth! Thank you so much for your work :)

[@CurbalEN]

And thanks a million for the feedback!!

[@pancholitachiu23]

Ruth: Gracias. Si quiero fechas me salía un error que tenía que ver con el tipo de datos. Y si tengo una columna RELATED no me sale en las columnas.

[@YazzaY1]

Thank you for the video and I have a question please. What is the equivalent of the following in power query language: =IF(a2="name", if(b2=3, if(c2>1,"true","false),"false"),"false") Please I would really appreciate it!!

[@txreal2]

Nested IFS (test 3 conditions if TRUE, if FALSE test 4 more conditions for True, if False test 2 more conditions for True, ....) VS table with the lookup values and their corresponding results for each condition? What do you think? Any help is appreciated.

[@hcedenop]

Ruth I loved this PQ&M too! the Excel style Error was a Dejavu for me when I was doing my 1st PQ back in 2018 Fall.. I was so frustrated beck then 😣. More PQ&M less DAXing 😁

[@vida1719]

Great video and explanation. When the same functions have different syntax in different environments, it is not possible to remember them all. I tried to memorise in past, but still kept making errors (in PQ if starts with small ‘i’ while in Excel and VBA with capital ‘I’). I used to check syntax online until discovered Custom Column.

[@CurbalEN]

Yes, the Add conditional column is perfect for that! /Ruth

[@MichaelBrown-lw9kz]

How do you use "Custom Column" to write syntax to group numerical values using operators? For example, if variable x is greater-than or equal to 1 and less-than or equal to 100 then this is Group 1. If variable x is greater-than or equal to 101 and less-than or equal to 200 then this is Group 2.

[@paspuggie48]

That last step is what I noticed too Ruth and learned the hard way that it all has to be in order if there are multiple outputs and they have to be in order. Can be confusing at times but important and effective. Thank you for sharing....love your PQ vids as always 😉

[@CurbalEN]

Thanks Paul, There are not very popular, but I love doing them :) /Ruth

[@paspuggie48]

@@CurbalEN they are invaluable to me 😉

[@Soulenergy31]

Thx for this if statments like pro in power query !

[@miked1906]

Great Video!! Very help and easy to understand.

[@EricaDyson]

As ever, Ruth... excellent. Thanks so much. Will definitely help me work faster and with less stress getting to the right result!!

[@CurbalEN]

Wondefuk to hear!!

[@sreekumarmenonk7678]

That's nice.. but how to add a conditional column inside a nested table? I cant find any resources online!

[@soy34mb]

I don't know if I'm your #1 fan, but I feel like I am! Love your way to explain the things, you make it looks so easy. Thanks!

[@CurbalEN]

You have been supporting me for sooo long!! Thanks a million Jimmy! /Ruth

[@NirmalanKrishnan]

Thanks for the awesome video. I am struggling with if statements. I would like to calculate the days duration depending on the status of the work progress. The statuses are Created, Draft, Review, Approve Execute and Close. Each status will have a date stamp. How to calculate duration of days with ‘and’ ‘or’ functions applied to the status. Can you do a video for dates calculation with if, and, or functions? Thanks.

[@CurbalEN]

Yes, time and dates require some special knowledge.

[@SolutionsAbroad]

Hey mate, you can probably use DATEDIFF function for calculating the days between each status. docs.microsoft.com/en-us/dax/datediff-function-dax

[@alterchannel2501]

what's the code for saying if the values in a column start with a number then .... else if they start with letters then .... ?

[@mahdiyeanbari4052]

Thank you for the video it was so practical and useful🤘😍

[@erictsang789]

Dear Curbal I have a question on the if function in Power Query I want to get [Profit]/[sales] , as there are some Zero profit in the column, it prompts "nan", how can I handle this ? Eric

[@stucknthematrix77]

I love your videos. Very practical and easy to follow. Could you show how to solve a particular conditional if statement for me. I have a database table containing service requests that I’m analyzing. Each request has a date and time stamp on it, and there are 4 rotating shift teams of people in the company (team 1, team 2, team 3, team 4). Teams 1 and 3 are permanently covering a 9AM - 9PM shift (when team 1 is working, team 3 is off) and teams 2 and 4 have the same setup, but always working 9PM - 9AM. So, I’m trying to make a conditional statement that will tell me what team each service request came from, based on the date/time on the service request, referred to a calendar lookup table. Your help is appreciated!

[@sa2144]

Do you have any video on conditional logic with contains example if Europe column contains “spa” or “ger” then 1 else 0

[@dsherwind]

This helped me so much! thank you!!

[@glmrenard]

Your youtube channel is so useful, thanks !

[@CurbalEN]

Thanks for the feedback 😀

[@romeop2345]

Hi Nice video, was wondering what you would write in the following case? If text contains Apple = staff wages If text contains orange = student discount If text contains both Apple and orange I want it to = Student discount but I think power query will match the first true statement and return staff wages :/ any ideas? (Still new to this great program)

[@yearofmars]

Write the if contains both Apple and orange first :-) Always start from the most stringent condition and move to the least, just like the less than number example.

[@pavan393]

Thank you so much, such a wonderful video! please keep on posting the videos related to Power BI

[@CurbalEN]

Will do:)

[@prachibagade4952]

You are super! Thanks for this educational video. 🙂

[@rdaleprice7184]

Very helpful and easy to understand. Thank you for another great video!

[@CurbalEN]

Thanks!

[@carloscerezo5006]

Thank you, please go ahead with more power query videos

[@benoitmbelo8239]

Hello Curbal, Nice to see your videaos and I'm wondering if one day you will share with us how to count (or sum) lines between a range of dates. Thank you in advance Benoit

[@vikkukumar7278]

I want to apply if statement and in if statement if the condition is true then print false but the condition is false then I want them to print series 1 to n values (n = 12879). so please tell me how to write it then it gives me output according to my condition

[@gisgis5985]

Great tips! Thank you

[@Ingles4allYou]

1000 thanks! It worked great!!

[@CurbalEN]

👏👏

[@zubairso]

How do you do calculated measures through? I mean if I want to do calculations based on my IF and not just return value from the table. Also, how can we incorporate time intelligence like date into IFs in power query and BI?

[@chamilam]

very useful tips ... many thanks Ruth ..

[@dinolapa3820]

YOU ARE THE BEST THANKS SO MUCH FOR YOUR HELP

[@CurbalEN]

Yey!!

[@nehachak]

Thank you for the awesome video. It will very helpful if you can please help me solve the following - I have a table with rank 1,2,3 and respective products name shampoo, diaper, lotions. I want a measure with the name of the product with the highest rank (i.e. 1).

[@danielsierra8919]

Great videos, thank you so much! Do you have any Power query or M code course? it will be perfect! Thanks again.

[@CurbalEN]

Yes, here: curbal.com/product-category/courses

[@nimishjain3313]

Hi Curbal, I have grouped 2 matrix visuals and am trying to export to excel but it is exporting only the second visual and not the complete visual. Any solutions.

[@zxyzxyzyzyugfd]

great video ! how do you add a conditional columns which refers to 2 others columns with many values, with the If statement like : If column 1 contains "null" values then take values of column 2 else take the values of column 1. thanks

[@neulzzi4942]

What if i want to use if statement with column in grouped table? {[table], "column name"} ..? doesn't work ? any way ..? :)..

[@marklemus9548]

Thanks for ever you saved my life for 2nd time xD

[@CurbalEN]

At your service !! 🎉🎉

[@mehdihammadi6145]

Hi Ruth, Thank you for the video👌. Is there any limitation on the number of nested if or the number of "or" we can use?

[@CurbalEN]

Good question! But I don't know? I have done quite a few without issues but I have never hit the limit.

[@blankpages3881]

Thanks, Ruth, best content always. The Product ID concatenation with 1 reminds me of a problem I faced. Our Product Ids are always 10 digit numbers, but at times they start with a zero (or two or more zeros) which users at times forget to input in the Excel data source. I used to get it rectified in Excel itself, could we think of another way using IF?

[@CurbalEN]

You don't need an if condition for that, use Text.PadStart instead on the column: m.kzbin.info/www/bejne/qqaWYpqVocZojKs

[@nancmadi]

You mentioned in the video @2.25 that you 'didn't have any intelligence on' what did you mean, how is it turned on or off? Thank you.

[@CurbalEN]

In File > options and Settings

[@mattsnow2376]

Hi There Ruth, this was really helpful. Are you folks aware of any method that would searching a long concatenated string of text info in a column? I have used the conditional column to search for terms like diabetes, or a condition. I have a column that some times includes multiple F codes (which are diagnostic)....what I am trying to do in sometimes a very long concatenated string to search for ANY F codes-they are formatted like F10.17, or F310.1, etc. When I have searched the column just for the presence of a captial F, it return words that begin with capital F plus the F codes. Any ideas?

[@HarshNathani]

Thanks for the video. As always very informative. Is there an equivalent Switch statement in M language too or do we use nested if as shown in the video ?

[@CurbalEN]

There are other way to check for conditions like this one: m.kzbin.info/www/bejne/i3TYooOBnJp9Zqs

[@atiry839]

simply amazing Ruth!

[@CurbalEN]

😊😊😊

[@stevedoherty6649]

Hi Ruth, great content as usual. An unrelated question though. Can you recommend an easy to use Video Editing software? I'm finding that I need to create help videos in models to demonstrate functionality but, I find the current software (HitFilm Express) powerful but, too complicated for my requirements. Any suggestions?

[@CurbalEN]

Camtasia, been using it from day 1 :) /Ruth

[@yearofmars]

You know you can add a gif to the hover over action on a transparent button on the dashboard. I have very IT illiterate sales people who need handholding with even basic click operations...

[@VinceM797]

Thanks for this!

[@shawnvargas9152]

Hi, Thank you for your great videos. After 20 plus years of excel excellence I have recently moved to away from doing my data transformation in excel and moved to power query. One Item I am running into is and an or statement within an and statement. Have you done any videos on this. Is it possible. Here is an example. IF(AND([@ProductLine]="123",[@[Domestic / INT]]="Domestic",OR([@[Top Level SW]]="6",[@[Top Level SW]]="C")),"abc"

[@niranjansaikrishnac4394]

Thanks Ruth.

[@CurbalEN]

My pleasure!

[@vacilando86]

amazing tutorial.

[@CurbalEN]

Thanks! /Ruth

[@politempofu]

Amazing video! very clear and useful! subscribed!

[@CurbalEN]

Welcome!

[@giorgioiribarren]

Hola Ruth, como estas? gracias por el video, muy aclaratorio. Quería aprovechar de preguntar por un video que ya realizaste, donde explicas como a partir solo de número de semana, mes y año se puede construir fechas para realizar la tabla calendario. Creo que utilizaste una formula para recuperar la fecha del primer día de un número de semana, para lograr esas fechas, pero no puedo encontrar el video. Me puedes ayudar?. Gracias por la ayuda. Saludos desde Chile.

[@CurbalEN]

Hola Jorge, Aqui tienes un blog post con todos los videos de calendarios: curbal.com/blog/create-power-bi-custom-calendars /Ruth

[@giorgioiribarren]

@@CurbalEN Muchas gracias Ruth por tu ayuda, que tengas un gran día.

[@SrinivasaRDandamudi]

Hi Are there any options to filter dataset from visualization selection in Direct Query?

[@yearofmars]

Yes. Use a parameter. I do it with twitter feed to a dashboard.

[@jorgevalentin4756]

Thank you so much !!!

[@RajaParivlog]

Wow 👍 nice explanation 👏

[@CurbalEN]

🥳🥳🥳

[@rafaeltan8686]

Hi there Ruth, Thanks for the awesome video! Just a question, is there a way to use the if conditionals on the same column as they started from? Not as a Add.Column step? (like in a bulk translation step) I'm currently using the power bi "replace.value" to translate the database, and its gotten over 300 translation steps total on multiple columns. There are columns with more than 70 translations, so it would be awesome if I could condense it in a Single step. I was thinking on using "Switch" to do it but perhaps I could do it with If conditionals. Thanks again for the video and for all other videos.

[@CurbalEN]

Maybe this? m.kzbin.info/www/bejne/mYC7f5WXlsRjaLc /Ruth

[@alvez1]

Great content

[@annapilipczuk926]

Very usefull this step with try, thank you for that :)

[@CurbalEN]

My pleasure :) /Ruth

[@lfvc1984]

Asome!!! Thanks so much for Sharing!!

[@CurbalEN]

My pleasure :))) /Ruth

[@jayguilloty6560]

Thank you

[@CurbalEN]

My pleasure :) /Ruth

[@s1ngularityxd64]

on point thanks a lot

[@o_felipecarvalho]

Great Video!

[@CurbalEN]

Thanks!

[@alfredsfutterkiste7534]

Love it. Thanks

[@CurbalEN]

🥳🥳

[@jiovanycarrillovillaverde4093]

Gracias si me sirvió!

[@CurbalEN]

🎉🎉

[@abhishekstatus_7]

This video just save my complete work!! Thank you Ruth !!

[@CurbalEN]

Yey!!!

[@abhishekstatus_7]

@@CurbalEN I am waiting for your reply on the EUR stats data !! LOL

[@CurbalEN]

What is the question? I dont notification for all the posts..

[@abhishekstatus_7]

@@CurbalEN Hi Ruth, I was looking for the latitude and longitude data for the European Statistics video 🎥 .. Thanks again and Happy Halloween🎃👻🍬🎃👻🍬🎃👻🍬🎃👻🍬🎃👻🍬🎃👻🍬🎃👻🍬🎃👻🍬🎃👻🍬🎃👻🍬🎃👻🍬🎃👻🍬🎃👻🍬🎃👻🍬🎃👻🍬🎃👻🍬🎃👻🍬🎃👻🍬🎃👻🍬🎃👻🍬🎃👻🍬🎃👻🍬

[@CurbalEN]

Get my file , it is there ;)

[@bluex5115]

hi .. i am new on PQ .. can u help me pls with this issue ? : i have 2 col and i want to insert a new custom col with value from both col value if there are, if not to omit text "NOx" and write forward if exists .. something like (it is not nested if) : =IF( [TXT1] "" , "NO1 " & [TXT1] , "" ) & IF ( [TXT2] "" , "NO2 " & [TXT2], "" ) thanks

[@rohitkumarz]

thankyou :)

[@mon_rich5366]

can you put course on udemy

[@CurbalEN]

I have courses on my website :)

[@ah87479]

Awesome tyvm ! :) subscribed

[@CurbalEN]

Welcome! /Ruth

[@vv9730]

why we simply cant have one type of syntex working in all softwares..??? excel to power query to VBA..one needs to learn different systex to do the same task at different places..😡😡😡

[@CurbalEN]

🤷‍♀️

[@fernalication]

Go Argentina go!

[@CurbalEN]

😂😂

[@Aquaelus]

I love that Woman, Thx for this Tutorial

[@tonytarragona6655]

Clarisimo.

[@CurbalEN]

Ahh, perfecto! ☺

[@dp70737]

“Otherwise null” 🤯

[@joangarcia6327]

TRY IF(DOYOUWANTLEARN THEN RUTHISYOURMUST ELSE ASKRUTHTOOINCASE) OTHERWISE NULL :)

[@CurbalEN]

😂😂😂

[@johngriffiths4636]

call out from guy in a cube to Ruth! kzbin.info/www/bejne/hJ_CqJ2cfZmjd7c #sharethelove