How to Write Power Query if Statements incl. Nested ifs, if or, if and with the IF function

[@sankeshsankesh.sawang196]

The easiest way to learn Excel and power bi from online platform That is you...👌

[@MyOnlineTrainingHub]

Glad you think so! 🙏

[@sjdyt]

I’m really embracing PQ in my new job. Since learning the basics (mostly from you thank you) this is the first time I’ve used it for real work tasks. I have already dabbled with nested ifs etc. and they are so simple and logical now I’ve done it a few times. Loads more opportunities for me to clean things up and make processes more efficient using PQ 😀

[@MyOnlineTrainingHub]

Wonderful to hear!

[@mohamedtalaat5836]

Awesome as usual, thanks. I want to ask when microsoft Copilot will be available in Excel?

[@MyOnlineTrainingHub]

Thank you! Copilot should be available soon. I don't have it yet, and we MVPs haven't been given an ETA.

[@jackcurl2005]

Having a more expressive mechanism (if/then/else vs if/,/,/) makes what you're doing so much more obvious and easier to fix when/if necessary. Microsoft has historically been less-than-helpful in the code-formatting arena (worst example: Access SQL), so preserving whatever formatting you have used is super helpful. Thanks for a nicely-done video!

[@MyOnlineTrainingHub]

Thank you!

[@thiruvallurusrikala5980]

I was taking my baby steps in Power BI and this nested if then, solved my requirement. Thank you for a detailed explanation.

[@MyOnlineTrainingHub]

Awesome to hear. Have fun with Power BI 😊

[@chrism9037]

Very clearly explained, thanks Mynda !

[@MyOnlineTrainingHub]

Cheers, Chris!

[@Seftehandle]

This is what i needed to get into dax or M. Simple way to get me the basics! Great job Mynda 🎉🎉🎉🎉🎉

[@MyOnlineTrainingHub]

Glad it was helpful! 🙏

[@craigsmall7984]

I love Power Query (Phil told me it would change my life, and he was right), but it has an annoying quirk when it comes to the "if". If I write a "simple if", Power Query will take it upon itself to convert it to a conditional column. This is all very well, unless I was to go back and use a slightly more complex test - the dialogue box is no longer exposed! I know, First World problems ... Can I suggest that you consider using indenting and multiple lines for your if statements? I feel that it's a good practice, and in my experience can make the statement easier to read. Just a thought.

[@MyOnlineTrainingHub]

Yes, I agree that is annoying, but you can always modify it in the formula bar, which is what I usually do. Indenting is a good idea, but I'm so used to writing formulas in Excel without it, that it's easy to do it that way. That said, if I'm writing a complex nested formula, then I sometimes will indent it to make it easier down the track.

[@debashiss7410]

The learning never stops!

[@MyOnlineTrainingHub]

Indeed 😁

[@loriwhittaker4577]

Hello. I just wanted to say Hi and thank you!! You are an amazing instructor! I have learned so much from you and I cannot wait to learn more! You break down everything very thoroughly to where it is understood. Thank you for your videos!

[@MyOnlineTrainingHub]

Wow, thanks for your kind words!

[@michaelp9061]

Thank you so much for this step-by-step tutorial.

[@MyOnlineTrainingHub]

You're very welcome!

[@aynellrodriguez7348]

This was excellent and couldn't be more clearly explained, thank you very much for creatings these videos!

[@MyOnlineTrainingHub]

Glad it was helpful!

[@shiffamohammed5818]

Thank you so much for the informative power query tutorial video, Mynda.

[@MyOnlineTrainingHub]

You are most welcome!

[@peterlewis2255]

Thank you so much Mynda, filled a glaring gap in my PQ skills

[@MyOnlineTrainingHub]

Glad I could help, Peter!

[@lopher70]

you did it again, this open a window of opportunities to improve my process!!

[@MyOnlineTrainingHub]

Great to hear!

[@Quidisi]

Totally agree that conditional functions in PQ are easier than Excel. PQ in also more helpful in finding errors, too.

[@MyOnlineTrainingHub]

Great to hear 😊

[@rimkach]

Quick and easy ! thanks for sharing your knowledge with us

[@MyOnlineTrainingHub]

Thanks for watching!

[@kenmcmillan2637]

Great video! You are right, If/And/Or are much easier to write that Excel. Thank you so much for excellent tutorials.

[@MyOnlineTrainingHub]

Glad you like them!

[@Dravidan1971]

Please make a video for beginners for errors while drafting dax formulas, like syntax error, missing parenthesis or unexpected expression or any other technical word errors please.

[@MyOnlineTrainingHub]

Thanks for the suggestion.

[@famoussuperstar2287]

Thank you for explaining in very easy method, Thanks

[@MyOnlineTrainingHub]

You are most welcome 😊

[@DB-Au]

Better than Chat GPt and gemini, Queenslander!

[@alparkson5494]

Nice! So helpful. Thanks Mynda

[@MyOnlineTrainingHub]

Thanks so much!

[@davidfamilydoctor9430]

Thanks for confirming 'null' means blank. Struggled with this recently and figured it out, but nice to know for sure.

[@MyOnlineTrainingHub]

Glad it was helpful 😊

[@llatyntseva]

Thank you for the tutorial :)

[@MyOnlineTrainingHub]

My pleasure 😊

[@rajeshkhanna-ew6eb]

Very well explained. 👍

[@MyOnlineTrainingHub]

Thank you!

[@stanTrX]

thanks, what about referring a formula for the next row? how can we do that?

[@MyOnlineTrainingHub]

There are a few ways we can do this as explained here: www.myonlinetraininghub.com/referencing-next-row-power-query

[@mattmatt245]

Would it be possible to create custom type of join in PQ ? Say, that you want to merge all records from one table with their overlapping records from the second table (start date/end date). Is it possible ? Thank you Matt

[@dbtech6548]

Useful information about function. Thank you so much 😊

[@MyOnlineTrainingHub]

My pleasure 😊

[@firstninja2198]

Great Video. How can I scrap of the url does not have a page start number?

[@MyOnlineTrainingHub]

The URL doesn't have to have a page number as such, it just needs a different URL for each page of data that uses a pattern to the URL that you can predict. If you're able to change the view of the data on the page, but the URL doesn't change, then Power Query can't scrap the data.

[@JaniceCook-jx8pw]

Really useful thanks. How can I refer to other cells in the statement. In your example, the 5 and 10% would ideally be held in cells that you can refer to in the if so that if they change the %age you don’t need to change the formula. I also have an example where if a condition is true I want to take a value from the row above… how do I refer to that? Many thanks!!

[@MyOnlineTrainingHub]

There are a few ways you can do this in PQ, e.g. variables, separate tables/queries etc. as explained here: www.myonlinetraininghub.com/power-query-variables-3-ways Referencing rows above is covered here: www.myonlinetraininghub.com/referencing-next-row-power-query

[@shrikantbadge3978]

I am learning. Thank you.

[@MyOnlineTrainingHub]

Wonderful!

[@KarolLacko-b9l]

Very useful! Thank you

[@MyOnlineTrainingHub]

Glad to hear that!

[@宋萌-b3u]

Thank you so much. Very useful.

[@MyOnlineTrainingHub]

Great to hear 😊

[@fbjrivers3354]

The video is wonderful and almost gives me what I need. I have a data table where each person can have phone number in 4 separate columns. I want to add a column that give a total how many numbers they have. I can get the answer of 1, but not 2 or 3 or 4.

[@MyOnlineTrainingHub]

Thank you! You can use List.NonNullCount e.g. if your phone numbers are in columns named No1 through No4: = List.NonNullCount({[No1], [No2], [No3], [No4]}) If you get stuck, post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum

[@hadouken3xl]

Thank you very much!! this is exactly what I was looking for. 🥰

[@MyOnlineTrainingHub]

Glad I could help 😊

[@RussellJones-k9p]

What do you put for else if you want no change to the data if the condition is false? I have a column of numbers and I want to change the zeroes and negative numbers to null but want the positive numbers to stay as they are. My next step will be to filter out the nulls.

[@MyOnlineTrainingHub]

In that case the else will be the column you're checking. e.g. if [column1]

[@mogarrett3045]

great tutorial

[@MyOnlineTrainingHub]

Glad you think so!

[@dhaniatami3718]

hi! i have a data master connect with the query. it is possible: - to make a formula when i input a new data master a number but when i refresh the query it become a text? - when i input " - " in data master it become delete rows in query? so when there is " - " in data master, the query doesn't insert it into the table

[@MyOnlineTrainingHub]

Not sure I follow what you mean. Please post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum

[@stevebulls7744]

It's a shame that Excel itself doesn't support IF/THEN/ELSE statements. It would be much easier to write formulas! As always, Thank you, Mynda, for this interesting nugget!! I always enjoy the info you have to share and look forward to your videos. Take care.

[@MyOnlineTrainingHub]

Glad you enjoyed it, Steve! 🙏

[@willm7994]

Great video ❤

[@MyOnlineTrainingHub]

Glad you liked it!!

[@Skip2MiLuu]

Can you show how do sumifs in a follow up video

[@MyOnlineTrainingHub]

Good idea. In the meantime I have this tutorial: www.myonlinetraininghub.com/excel-sumif-and-sumifs-formulas-explained

[@Skip2MiLuu]

Thanks for the reply and link, but I am quite familiar with excel SUMIFS. I am however trying to figure out how to convert the below SUMIFS function to one I can copy into a Power Query Custom Column that will calculate on each row. SUMIFS Excel: -Sum_range: Country_Variety_Sales[2023 TOTAL] -Criteria_range 1: Country_Variety_Sales[Secondary No] -Criteria1: Country_Variety_Sales[@[Secondary No]] -Criteria_range 2: Country_Variety_Sales[Country] -Criteria2: Country_Variety_Sales[@[Country]]]

[@jawadahmadehssan6251]

Great content thank you.

[@MyOnlineTrainingHub]

My pleasure!

[@MrDhunpagla]

Thank you ☺️ again for this super informative tips...need more on PQ if possible. Visuals & animations are awesome 👍

[@MyOnlineTrainingHub]

Thanks so much!

[@sajilprkkv]

How to activate to show all available formulas while we start writing the formula in power query? Anyone knows ? 2:55

[@MyOnlineTrainingHub]

This is available in later versions of Excel. If you don't see the function list appear, it's because your version of Excel doesn't have this feature. You'd have to upgrade to get it.

[@sajilprkkv]

@@MyOnlineTrainingHub thank you for the reply, I have office 2019, do I need to turn on any options to get or this version don’t have it?

[@MyOnlineTrainingHub]

I can't remember what version is came in, but it's not something you turn on/off. It's either there or it isn't. The best you can do is update your installation of 2019 and see if you get it in an update, but remember that perpetual licenses like 2019 do not usually get updates for new features, only bug fixes.

[@satishgopal2406]

Thank you Ma’am🙏😌

[@MyOnlineTrainingHub]

Most welcome 😊

[@varko5083]

Just amazing person... thank you

[@MyOnlineTrainingHub]

So nice of you 😊

[@claudiobarbosa4702]

Como faço para utilizar a formula SE e E no power query envolvendo horas?

[@MyOnlineTrainingHub]

Not sure what you mean by SE and E. Please post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum

[@nazarkamal8831]

This is amazing ❤❤❤

[@MyOnlineTrainingHub]

Thank you!

[@mohang5925]

What if we have different data in two different columns how to analyse can you pls help with that

[@MyOnlineTrainingHub]

Not sure how your data is structured. Probably best if you post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum

[@kumaryadav4256]

is there a way to avoid overwriting rule?

[@MyOnlineTrainingHub]

Not sure what you mean?

[@prasadpatil8242]

Hi ,Can you take session on how to convert Descriptive data into tables through Power Query or other Excel tools

[@MyOnlineTrainingHub]

'Descriptive data', do you mean descriptive statistics?

[@lmbob703]

How would you write an if statement to identify a specific word in a cell?

[@MyOnlineTrainingHub]

Use the drop down 'text contains'.

[@shoppersdream]

Nice, thanks!

[@MyOnlineTrainingHub]

You're welcome!

[@stephenclarke7351]

useful and to the point.

[@MyOnlineTrainingHub]

Glad it was helpful!

[@povsok8939]

How to use if statement for reciprocal count based on the date?

[@MyOnlineTrainingHub]

Please post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum

[@danishnawaz3651]

plz make video for below. If 1 table has 9 column, 2 table has 10 and 3 table has 8 . Then how can we make equal all column for all table.. like we want to insert dummy column for missing column in tables and make equal like 10 column for each table.

[@MyOnlineTrainingHub]

If you append tables 1 and 3 to table 2, the data will be in a 10 column table.

[@gospelmoto2833]

Lovely!...as you are.

[@MyOnlineTrainingHub]

😊 thank you

[@hazemali382]

more than greet Mynda ♥

[@MyOnlineTrainingHub]

Glad it was helpful!

[@hiteshjal143]

Thanks very Nice.

[@MyOnlineTrainingHub]

Most welcome 😊

[@danishnawaz3651]

Plz make Video for below Formula "Power Query", Video Time Fram is Max 10mint 1. List.RemoveMatchingItems 2. List.ReplaceMatchingItems 3. List.TransformMany 4. List.ReplaceValue 5. List.PositionOfAny 6. List.PositionOf 7. List.NonNullCount 8. List.Intersect 9. List.Durations 10. List.Difference 11. List.FindText 12. List.InsertRange 13. List.Union

[@astrokier]

Please consider a video of creating an unbreakable form for everyone to enter data, so that it can be translated in PowerBI dashboard 😊

[@MyOnlineTrainingHub]

Have you tried Excel Forms: kzbin.info/www/bejne/e6rWZIyKotl-aNU

[@Kimwenman]

This was a great video and so easy to follow. I have an issue with my nested IF statements in Power Query when referring to dates. I have a column with a date and a column with a Stage. My formula is:if[Start Ship Date] = "3/1/2024" and [Stage] = "stage 3" then "green" else if [Start Ship Date]= "3/1/2024" and [Stage] = "stage 2" then "green" else if [Start Ship Date] = "3/1/2024" and [Stage] = "stage 1" then "red" else if [Start Ship Date] = "9/1/2024" and [Stage] = "stage 0" then "red" else if [Start Ship Date] = "9/1/2024" and [Stage] = "stage 1" then "green" else if [Start Ship Date] = "9/1/2024" and [Stage] = "stage 2" then "green" else if [Start Ship Date] = "9/1/2024" and [Stage] = "stage 3" then "green" else "yellow" The issue is all the results are turning out to be YELLOW - when I know the if [Start Ship Date] = "9/1/2024" and [Stage] = "stage 2" then "green" else - I should be getting green. What am I missing? Do I need to choose a different way to have my date = ? I am confused.....hoping you can help. Thanks!!

[@MyOnlineTrainingHub]

Thank you! Your formula is probably looking for something that's not there, hence the last else "yellow" being returned. It could be that the data is written 'Stage 1" etc. rather than "stage 1". Power Query is case sensitive, so these are not the same. It could be that in the column called 'Stage' and the actual data is only the number, not prefixed by 'stage'. If it's not that, then please post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum

[@Kimwenman]

@@MyOnlineTrainingHub thank you - I realized that the formula to equal date needed to read: [Start Ship Date] =#date(2024,9,1) I updated all those instances and it worked! ;)

[@Cahangir]

Thanks

[@MyOnlineTrainingHub]

You're welcome 😊

[@melacesar2130]

Thnx😍

[@MyOnlineTrainingHub]

You're most welcome 😊

[@IamTheReaper911]

IF this ...... 🤪😜 👍😎

[@reng7777]

Hi amiga! thanks for your video..i have a comments/ questions for you... i have implemented some small solutions with Power Query but sometimes i found that it is better to write the code in the Advance Editor in order to make it more efficient ( for some scenarios the function is not in the menu option in the Power Query window).. ..I see that nobody provides an speciization tranining on M LANGUAGUE CODE, when this is a key tool that allows us to set a better performance and provide faster solutions.. so my question to you is have you considering on providing an ON DAMAND full course just focus on M LANGUAGUE with real life examples? if you take the time to considere it, do not hesitate that i will be paying for your course!!! actually whay if you can collect some problems that in our day to work we face( it will be obvious that the figures will not be from our company )and then with that information you can set the course( OBVIOUSLY NOT FOR FREE) .

[@MyOnlineTrainingHub]

Thanks for the suggestion! M is a vast topic indeed and I do cover some of it in my Power Query course: www.myonlinetraininghub.com/excel-power-query-course you can see the topics at the above link under the syllabus section.

[@tamma369]

null doesn't work for me only ""

[@MyOnlineTrainingHub]

That'll be because those cells aren't properly blank. Blanks returned by formulas that specify a blank with two double quotes are not 'blank' in the true sense. Hope that clarifies why you're having to use "".

[@nazarkamal8831]

❤❤❤

[@MyOnlineTrainingHub]

🙏

[@Acheiropoietos]

Managers should get 5%! Workers 10%!

[@MyOnlineTrainingHub]

👏

[@chasingdogs]

Wishful thinking! :)

[@ssmith8717]

“” is not working properly with formulas in my dimension table. =if[PL]="Z0" and [#"$ Decrease"]="" then [Z0 FABRIC] else 0 The code is returning 0 but the data in [Z0 Fabric] is the desired return. 2nd question: How can I properly calculate Roundup located in the dimension table? The dimension table holds the percentage and dollar increases along with the roundup to the dollar specified. The Then part of the code below should calculate as follows: [Z0 Fabric] which is a price * [#"% Increase"]} which is a percentage and [Roundup] to the value located in the dimension table. if [PL]="Z0" and [#"$ Increase"]""and [Roundup $] ”” then {[Z0 FABRIC]*[#"% Increase"]}*[Roundup] else [Z0 FABRIC]

[@MyOnlineTrainingHub]

There are a few odd things in your formulas because you're referencing steps and columns in the same IF: =if[PL]="Z0" and [#"$ Decrease"]="" then [Z0 FABRIC] else 0 You could try: =if [PL]="Z0" and [$ Decrease]="" then [Z0 FABRIC] else 0 And this one has curly braces, which aren't required: if [PL]="Z0" and [#"$ Increase"]""and [Roundup $] ”” then {[Z0 FABRIC]*[#"% Increase"]}*[Roundup] else [Z0 FABRIC] I think it's best if you post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum

[@ssmith8717]

Thank you! I appreciate your quick response and suggestion.

[@DavidKing-wk1ws]

Have been looking for the right specs for excel. No one has an answer. Of you fill every cell in a spreadsheet and fill every spreadsheet that can be in a workbook. How much ram/cpu would you need for it to run without taking a month to calculate?

[@MyOnlineTrainingHub]

Probably because if you have that much data that you fill every cell in every sheet, then you should be using something else, otherwise you'd probably need a super-computer. If it helps, I have a 3.7GHz CPU with 32GB RAM and a solid state drive. My PC is lightning fast and Excel never has a problem coping with the work I do, but I'm by no means filling every cell in every sheet.

[@DavidKing-wk1ws]

@@MyOnlineTrainingHub Thank you for your response. It seems memory is key here so I will go with the odroid h3+ as it allows for 64 gb ram and a 2 ghz cpu with 3.3 burst or sustained high speed. Will find out if this fits the bill. Thank you.

[@erikv2382]

"null" causes errors for me

[@MyOnlineTrainingHub]

null it doesn't go inside double quotes in the formula. I wonder if that's what you're doing.

[@mohammadsoori9712]

great

[@MyOnlineTrainingHub]

Thank you!

[@vikashdwivedi2902]

IF(J2="NEUBUS3AXXX",K2,LEFT(K2,4)&"/"&IF(D2=202,RIGHT(12,10),IF(D2=210,RIGHT(H2,10)))))))

[@vikashdwivedi2902]

How to right in power query can you please guide

[@Carlitos117]

Marry me! 😍🤩🥹

[@MyOnlineTrainingHub]

Already married, I'm afraid.