If "Not Curbal" then "it's no Fun" :) .... Thanks Ruth 👍🏻
@FM-qw3eo4 жыл бұрын
Ruth - I cant believe how much value that you manage to squeeze into a 12 min video... simply brilliant !
@CurbalEN4 жыл бұрын
Wonderful to hear, thanks!
@mihirhardas82973 жыл бұрын
I completely agree, i was in awe when i saw the IF condition planted inside replace video!
@CurbalEN3 жыл бұрын
😊
@yearofmars4 жыл бұрын
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.
@CurbalEN4 жыл бұрын
Neat! Thanks 😊
@eq2home3 жыл бұрын
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
@CurbalEN3 жыл бұрын
Thanks for the feedback!
@alreadydeadfunk4 жыл бұрын
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.
@yearofmars4 жыл бұрын
Best comment and better than the video. Would say, List.Contains is also useful in complex conditionals for reducing verbosity.
@lindabond93072 жыл бұрын
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 :)
@joanneosborne24283 жыл бұрын
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" 👍
@CurbalEN3 жыл бұрын
Thanks ☺😊!
@jessicavea13792 жыл бұрын
you explain so simply and immediately found my answer with your and example. thank you!!
@CurbalEN2 жыл бұрын
Fab to hear 👏👏
@kevinhenry16812 жыл бұрын
Thanks SOO MUCH for showing how to customize columns in Power Query!!
@sujitnair839510 ай бұрын
You always solve my problems to the point, wish i could take you on a date for that. Love from India !
@nancmadi3 жыл бұрын
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.
@CurbalEN2 жыл бұрын
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
@excelbelajar4 жыл бұрын
thank you so much. learn so much from your channel
@CurbalEN4 жыл бұрын
Thanks!
@Lyriks_2 жыл бұрын
I don't know why her face makes me happy, great video !
@CurbalEN2 жыл бұрын
Because power query makes me happy and it is contagious 😄
@maheshbhosale46613 жыл бұрын
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
@SolutionsAbroad4 жыл бұрын
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!
@CurbalEN4 жыл бұрын
Like a switch function in DAX?
@SolutionsAbroad4 жыл бұрын
@@CurbalEN great shout!
@yearofmars4 жыл бұрын
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?
@magnusvangekjrchristensen19962 жыл бұрын
You are simply the best Power BI tutor out there, Ruth! Thank you so much for your work :)
@CurbalEN2 жыл бұрын
And thanks a million for the feedback!!
@pancholitachiu232 жыл бұрын
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.
@YazzaY12 жыл бұрын
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.
@hcedenop4 жыл бұрын
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 😁
@vida17194 жыл бұрын
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.
@CurbalEN4 жыл бұрын
Yes, the Add conditional column is perfect for that! /Ruth
@MichaelBrown-lw9kz3 жыл бұрын
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.
@paspuggie484 жыл бұрын
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 😉
@CurbalEN4 жыл бұрын
Thanks Paul, There are not very popular, but I love doing them :) /Ruth
@paspuggie484 жыл бұрын
@@CurbalEN they are invaluable to me 😉
@Soulenergy312 жыл бұрын
Thx for this if statments like pro in power query !
@miked19062 жыл бұрын
Great Video!! Very help and easy to understand.
@EricaDyson4 жыл бұрын
As ever, Ruth... excellent. Thanks so much. Will definitely help me work faster and with less stress getting to the right result!!
@CurbalEN4 жыл бұрын
Wondefuk to hear!!
@sreekumarmenonk76782 жыл бұрын
That's nice.. but how to add a conditional column inside a nested table? I cant find any resources online!
@soy34mb4 жыл бұрын
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!
@CurbalEN4 жыл бұрын
You have been supporting me for sooo long!! Thanks a million Jimmy! /Ruth
@NirmalanKrishnan4 жыл бұрын
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.
@CurbalEN4 жыл бұрын
Yes, time and dates require some special knowledge.
@SolutionsAbroad4 жыл бұрын
Hey mate, you can probably use DATEDIFF function for calculating the days between each status. docs.microsoft.com/en-us/dax/datediff-function-dax
@alterchannel25012 жыл бұрын
what's the code for saying if the values in a column start with a number then .... else if they start with letters then .... ?
@mahdiyeanbari40522 жыл бұрын
Thank you for the video it was so practical and useful🤘😍
@erictsang7893 жыл бұрын
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
@stucknthematrix772 жыл бұрын
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!
@sa21444 жыл бұрын
Do you have any video on conditional logic with contains example if Europe column contains “spa” or “ger” then 1 else 0
@dsherwind3 жыл бұрын
This helped me so much! thank you!!
@glmrenard4 жыл бұрын
Your youtube channel is so useful, thanks !
@CurbalEN4 жыл бұрын
Thanks for the feedback 😀
@romeop23454 жыл бұрын
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)
@yearofmars4 жыл бұрын
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.
@pavan3933 жыл бұрын
Thank you so much, such a wonderful video! please keep on posting the videos related to Power BI
@CurbalEN3 жыл бұрын
Will do:)
@prachibagade4952 Жыл бұрын
You are super! Thanks for this educational video. 🙂
@rdaleprice71842 жыл бұрын
Very helpful and easy to understand. Thank you for another great video!
@CurbalEN2 жыл бұрын
Thanks!
@carloscerezo50064 жыл бұрын
Thank you, please go ahead with more power query videos
@benoitmbelo82392 жыл бұрын
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
@vikkukumar72783 жыл бұрын
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
@gisgis59852 жыл бұрын
Great tips! Thank you
@Ingles4allYou2 жыл бұрын
1000 thanks! It worked great!!
@CurbalEN2 жыл бұрын
👏👏
@zubairso4 жыл бұрын
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?
@chamilam4 жыл бұрын
very useful tips ... many thanks Ruth ..
@dinolapa38203 жыл бұрын
YOU ARE THE BEST THANKS SO MUCH FOR YOUR HELP
@CurbalEN3 жыл бұрын
Yey!!
@nehachak3 жыл бұрын
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).
@danielsierra89193 жыл бұрын
Great videos, thank you so much! Do you have any Power query or M code course? it will be perfect! Thanks again.
@CurbalEN3 жыл бұрын
Yes, here: curbal.com/product-category/courses
@nimishjain33134 жыл бұрын
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
@neulzzi49423 жыл бұрын
What if i want to use if statement with column in grouped table? {[table], "column name"} ..? doesn't work ? any way ..? :)..
@marklemus95482 жыл бұрын
Thanks for ever you saved my life for 2nd time xD
@CurbalEN2 жыл бұрын
At your service !! 🎉🎉
@mehdihammadi61453 жыл бұрын
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?
@CurbalEN3 жыл бұрын
Good question! But I don't know? I have done quite a few without issues but I have never hit the limit.
@blankpages38814 жыл бұрын
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?
@CurbalEN4 жыл бұрын
You don't need an if condition for that, use Text.PadStart instead on the column: m.kzbin.info/www/bejne/qqaWYpqVocZojKs
@nancmadi2 жыл бұрын
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.
@CurbalEN2 жыл бұрын
In File > options and Settings
@mattsnow23764 жыл бұрын
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?
@HarshNathani4 жыл бұрын
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 ?
@CurbalEN4 жыл бұрын
There are other way to check for conditions like this one: m.kzbin.info/www/bejne/i3TYooOBnJp9Zqs
@atiry8394 жыл бұрын
simply amazing Ruth!
@CurbalEN4 жыл бұрын
😊😊😊
@stevedoherty66494 жыл бұрын
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?
@CurbalEN4 жыл бұрын
Camtasia, been using it from day 1 :) /Ruth
@yearofmars4 жыл бұрын
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...
@VinceM7973 жыл бұрын
Thanks for this!
@shawnvargas91523 жыл бұрын
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"
@niranjansaikrishnac43944 жыл бұрын
Thanks Ruth.
@CurbalEN4 жыл бұрын
My pleasure!
@vacilando864 жыл бұрын
amazing tutorial.
@CurbalEN4 жыл бұрын
Thanks! /Ruth
@politempofu3 жыл бұрын
Amazing video! very clear and useful! subscribed!
@CurbalEN3 жыл бұрын
Welcome!
@giorgioiribarren4 жыл бұрын
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.
@CurbalEN4 жыл бұрын
Hola Jorge, Aqui tienes un blog post con todos los videos de calendarios: curbal.com/blog/create-power-bi-custom-calendars /Ruth
@giorgioiribarren4 жыл бұрын
@@CurbalEN Muchas gracias Ruth por tu ayuda, que tengas un gran día.
@SrinivasaRDandamudi4 жыл бұрын
Hi Are there any options to filter dataset from visualization selection in Direct Query?
@yearofmars4 жыл бұрын
Yes. Use a parameter. I do it with twitter feed to a dashboard.
@jorgevalentin47563 жыл бұрын
Thank you so much !!!
@RajaParivlog3 жыл бұрын
Wow 👍 nice explanation 👏
@CurbalEN3 жыл бұрын
🥳🥳🥳
@rafaeltan86864 жыл бұрын
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.
Very usefull this step with try, thank you for that :)
@CurbalEN4 жыл бұрын
My pleasure :) /Ruth
@lfvc19844 жыл бұрын
Asome!!! Thanks so much for Sharing!!
@CurbalEN4 жыл бұрын
My pleasure :))) /Ruth
@jayguilloty65604 жыл бұрын
Thank you
@CurbalEN4 жыл бұрын
My pleasure :) /Ruth
@s1ngularityxd642 жыл бұрын
on point thanks a lot
@o_felipecarvalho3 жыл бұрын
Great Video!
@CurbalEN3 жыл бұрын
Thanks!
@alfredsfutterkiste75344 жыл бұрын
Love it. Thanks
@CurbalEN4 жыл бұрын
🥳🥳
@jiovanycarrillovillaverde40932 жыл бұрын
Gracias si me sirvió!
@CurbalEN2 жыл бұрын
🎉🎉
@abhishekstatus_73 жыл бұрын
This video just save my complete work!! Thank you Ruth !!
@CurbalEN3 жыл бұрын
Yey!!!
@abhishekstatus_73 жыл бұрын
@@CurbalEN I am waiting for your reply on the EUR stats data !! LOL
@CurbalEN3 жыл бұрын
What is the question? I dont notification for all the posts..
@abhishekstatus_73 жыл бұрын
@@CurbalEN Hi Ruth, I was looking for the latitude and longitude data for the European Statistics video 🎥 .. Thanks again and Happy Halloween🎃👻🍬🎃👻🍬🎃👻🍬🎃👻🍬🎃👻🍬🎃👻🍬🎃👻🍬🎃👻🍬🎃👻🍬🎃👻🍬🎃👻🍬🎃👻🍬🎃👻🍬🎃👻🍬🎃👻🍬🎃👻🍬🎃👻🍬🎃👻🍬🎃👻🍬🎃👻🍬🎃👻🍬🎃👻🍬
@CurbalEN3 жыл бұрын
Get my file , it is there ;)
@bluex51153 жыл бұрын
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
@rohitkumarz2 жыл бұрын
thankyou :)
@mon_rich53662 жыл бұрын
can you put course on udemy
@CurbalEN2 жыл бұрын
I have courses on my website :)
@ah874794 жыл бұрын
Awesome tyvm ! :) subscribed
@CurbalEN4 жыл бұрын
Welcome! /Ruth
@vv97302 жыл бұрын
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..😡😡😡
@CurbalEN2 жыл бұрын
🤷♀️
@fernalication4 жыл бұрын
Go Argentina go!
@CurbalEN4 жыл бұрын
😂😂
@Aquaelus4 жыл бұрын
I love that Woman, Thx for this Tutorial
@tonytarragona66554 жыл бұрын
Clarisimo.
@CurbalEN4 жыл бұрын
Ahh, perfecto! ☺
@dp70737 Жыл бұрын
“Otherwise null” 🤯
@joangarcia63274 жыл бұрын
TRY IF(DOYOUWANTLEARN THEN RUTHISYOURMUST ELSE ASKRUTHTOOINCASE) OTHERWISE NULL :)
@CurbalEN4 жыл бұрын
😂😂😂
@johngriffiths46364 жыл бұрын
call out from guy in a cube to Ruth! kzbin.info/www/bejne/hJ_CqJ2cfZmjd7c #sharethelove