Write if statements like a Pro in Power Query
Vložit
- čas přidán 6. 08. 2024
- As the title says, in this video I will show you how to write if-statements like a pro:
Chapters
00:00 The ultimate if-statement
00:40 if statement in Excel wont work
01:50 Use power query user interface to write if statement
03:00 Nested if-statements
03:38 AND/OR conditions in if statements
04:48 NOT condition in if statements
05:20 Manage errors in if statements
06:13 Advanced if statements
08:19 Order of evaluation if statements
Done!
Here you can download all the pbix files: curbal.com/donwload-center
SUBSCRIBE to learn more about Power and Excel BI!
/ @curbalen
Our PLAYLISTS:
- Join our DAX Fridays! Series: goo.gl/FtUWUX
- Power BI dashboards for beginners: goo.gl/9YzyDP
- Power BI Tips & Tricks: goo.gl/H6kUbP
- Power Bi and Google Analytics: goo.gl/ZNsY8l
☼☼☼☼☼☼☼☼☼☼
POWER BI COURSES:
Want to learn Power BI? How about you take one of our courses? Here you can find the available courses:
curbal.com/courses-overview
☼☼☼☼☼☼☼☼☼☼
ABOUT CURBAL:
Website: www.curbal.com
Contact us: www.curbal.com/contact
▼▼▼▼▼▼▼▼▼▼
If you feel that any of the videos, downloads, blog posts that I have created have been useful to you and you want to help me keep on going, here you can do a small donation to support my work and keep the channel running:
curbal.com/product/sponsor-me
Many thanks in advance!
▲▲▲▲▲▲▲▲▲▲
************
************
QUESTIONS? COMMENTS? SUGGESTIONS? You’ll find me here:
Linkedin ► goo.gl/3VW6Ky
Twitter ► @curbalen, @ruthpozuelo
Facebook ► goo.gl/bME2sB
#CURBAL #SUBSCRIBE
If "Not Curbal" then "it's no Fun" :) .... Thanks Ruth 👍🏻
Ruth - I cant believe how much value that you manage to squeeze into a 12 min video... simply brilliant !
Wonderful to hear, thanks!
I completely agree, i was in awe when i saw the IF condition planted inside replace video!
😊
I was exactly looking for this solution to create a new column with similar conditions. Thank you very much for posting!
You always solve my problems to the point, wish i could take you on a date for that. Love from India !
Thanks SOO MUCH for showing how to customize columns in Power Query!!
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 😁
You are simply the best Power BI tutor out there, Ruth! Thank you so much for your work :)
And thanks a million for the feedback!!
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
Thanks for the feedback!
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.
Neat! Thanks 😊
Great tips! Thank you
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 :)
As ever, Ruth... excellent. Thanks so much. Will definitely help me work faster and with less stress getting to the right result!!
Wondefuk to hear!!
you explain so simply and immediately found my answer with your and example. thank you!!
Fab to hear 👏👏
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.
And thanks for the feedback!!! Well done :)
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" 👍
Thanks ☺😊!
very useful tips ... many thanks Ruth ..
Great Video!! Very help and easy to understand.
Just seen this video, and wow, how simple! Thanks
Thx for this if statments like pro in power query !
Thank you, please go ahead with more power query videos
Very helpful and easy to understand. Thank you for another great video!
Thanks!
This helped me so much! thank you!!
Thank you so much,
such a wonderful video!
please keep on posting the videos related to Power BI
Will do:)
You are the best one stop shop to find a solutions of my problem
At your service!
Did you know you can also search here?
curbal.com/curbal-learning-portal
Thanks for this!
Thank you so much !!!
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 😉
Thanks Paul,
There are not very popular, but I love doing them :)
/Ruth
@@CurbalEN they are invaluable to me 😉
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?
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
thank you so much. learn so much from your channel
Thanks!
Your youtube channel is so useful, thanks !
Thanks for the feedback 😀
simply amazing Ruth!
😊😊😊
Amazing video! very clear and useful! subscribed!
Welcome!
on point thanks a lot
Great content
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!
You have been supporting me for sooo long!! Thanks a million Jimmy!
/Ruth
Thank you for the video it was so practical and useful🤘😍
You are super! Thanks for this educational video. 🙂
1000 thanks! It worked great!!
👏👏
Asome!!! Thanks so much for Sharing!!
My pleasure :)))
/Ruth
I don't know why her face makes me happy, great video !
Because power query makes me happy and it is contagious 😄
YOU ARE THE BEST THANKS SO MUCH FOR YOUR HELP
Yey!!
Very usefull this step with try, thank you for that :)
My pleasure :)
/Ruth
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!
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.
Best comment and better than the video.
Would say, List.Contains is also useful in complex conditionals for reducing verbosity.
Hello M’am could you tell me how the syntax should look when i want to break my if function?
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?
Great Video!
Thanks!
Love it. Thanks
🥳🥳
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.
Yes, the Add conditional column is perfect for that!
/Ruth
amazing tutorial.
Thanks!
/Ruth
Thanks Ruth.
My pleasure!
Gracias si me sirvió!
🎉🎉
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 ?
There are other way to check for conditions like this one:
m.czcams.com/video/UCuqPKg5J0I/video.html
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?
Good question! But I don't know? I have done quite a few without issues but I have never hit the limit.
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?
You don't need an if condition for that, use Text.PadStart instead on the column:
m.czcams.com/video/tu31g_la5VI/video.html
thankyou :)
Great videos, thank you so much! Do you have any Power query or M code course? it will be perfect! Thanks again.
Yes, here:
curbal.com/product-category/courses
Wow 👍 nice explanation 👏
🥳🥳🥳
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!
Like a switch function in DAX?
@@CurbalEN great shout!
Wrote this in above comment. Might help if you use ‘if’ with ‘List.Contains ‘ to reduce the verbosity.
Thank you
My pleasure :)
/Ruth
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.
Hi
Are there any options to filter dataset from visualization selection in Direct Query?
Yes. Use a parameter.
I do it with twitter feed to a dashboard.
That's nice..
but how to add a conditional column inside a nested table?
I cant find any resources online!
Awesome tyvm ! :) subscribed
Welcome!
/Ruth
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?
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.
Yes, time and dates require some special knowledge.
Hey mate, you can probably use DATEDIFF function for calculating the days between each status. docs.microsoft.com/en-us/dax/datediff-function-dax
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
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.
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!!
Do you have any video on conditional logic with contains example if Europe column contains “spa” or “ger” then 1 else 0
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
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.
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.
Maybe this?
m.czcams.com/video/cOXNbaa_02U/video.html
/Ruth
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?
Camtasia, been using it from day 1 :)
/Ruth
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...
This video just save my complete work!! Thank you Ruth !!
Yey!!!
@@CurbalEN I am waiting for your reply on the EUR stats data !! LOL
What is the question? I dont notification for all the posts..
@@CurbalEN Hi Ruth, I was looking for the latitude and longitude data for the European Statistics video 🎥 .. Thanks again and Happy Halloween🎃👻🍬🎃👻🍬🎃👻🍬🎃👻🍬🎃👻🍬🎃👻🍬🎃👻🍬🎃👻🍬🎃👻🍬🎃👻🍬🎃👻🍬🎃👻🍬🎃👻🍬🎃👻🍬🎃👻🍬🎃👻🍬🎃👻🍬🎃👻🍬🎃👻🍬🎃👻🍬🎃👻🍬🎃👻🍬
Get my file , it is there ;)
what's the code for saying if the values in a column start with a number then .... else if they start with letters then .... ?
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.
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).
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.
Hola Jorge,
Aqui tienes un blog post con todos los videos de calendarios:
curbal.com/blog/create-power-bi-custom-calendars
/Ruth
@@CurbalEN Muchas gracias Ruth por tu ayuda, que tengas un gran día.
Thanks for ever you saved my life for 2nd time xD
At your service !! 🎉🎉
Clarisimo.
Ahh, perfecto! ☺
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)
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.
call out from guy in a cube to Ruth!
czcams.com/video/Nn_wjfH4pAU/video.html
#sharethelove
What if i want to use if statement with column in grouped table? {[table], "column name"} ..? doesn't work ? any way ..? :)..
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
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"
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.
In File > options and Settings
can you put course on udemy
I have courses on my website :)
I love that Woman, Thx for this Tutorial
Go Argentina go!
😂😂
“Otherwise null” 🤯
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..😡😡😡
🤷♀️
TRY IF(DOYOUWANTLEARN THEN RUTHISYOURMUST ELSE ASKRUTHTOOINCASE) OTHERWISE NULL :)
😂😂😂
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