Awesome! I didn’t watch this video for quite a long time because I thought I knew everything. Wrong, I was mistaken! I didn’t know the trick with List.Contains. But now, I do :) Thank you so much! 👏
Thank you for this video. At work I get a weekly statement that has about 5,000 invoices. This supplier has 6 supplier ID numbers and the statement has invoices going back to 2018. I have to run a query on each of the 6 supplier ID’s going back to 2018 to compile a table I can do an XLookup against. The table ends up having over 450,000 rows and there’s one column that has 8 nested If/Then formulas that determines the status of each invoice. Right now I I have the Excel file with the If/Then formula on my hard drive and it’s over 100MB but I created that as a Power Query table on the shared drive and it’s only 80MB. I’d like to save time by learning to recreate the If/Then formula in Power Query and I believe this video will help me.
Thank you so much and you are a life saver! I was searching for “conditional column” along with IF statements for several hours and then I found your video. I already subscribed your channel, and thank you again,
Outstanding tutorial. Thank you very much. The acronym, eof, stands for "end of file". This underscores your observation that "Token eof expected" is a vague error message. The meaning of "eof" resolves the ambiguity only slightly. I think Power Query throws the message because the Advanced Editor can only execute a query script after reading a complete chain of syntactically correct M language commands. The uppercase IF ends the translation process unexpectedly.
Paul! Thanks for adding that valuable information here in the comments. I'm sure it will be helpful to others. Would be great if MS updates the error messages to something clearer. Thanks again!
One thing I have found, and that is the order of evaluation can have an affect on the outcome so ; Table.ReplaceValue( Source, "AAA", "NUMBER" , ( current, old , new )=> if Value.Type(current ) = type number then new else if Text.Start( current ,1) = "a" then old else current , Columns ) Works, but if you start by asking text start you need a try / otherwise; Table.ReplaceValue( Source, "AAA", "Number", ( x , y , z )=> try if Text.Start( x ,1 ) = "a" then y else x otherwise if Value.Type(x )= type number then z else x , Columns )
Thank you for this video. Your explanation of the if statement returning a formula is close to what I need, I think. How would I write an if statement if I wanted to check the order number for a certain amount, and then create a column to return the quantity into a new column. I.e. I have four different types of commission codes I want to check in one column, and if commission code a = a certain amount, then, I want the commission amount to populate a new column A,; if commission code B = a certain amount, then I want the commission amount to populate a new column B, and so on. Thank you so much
Hey Courtney. You could work with separate columns. Each column defining the if condition you need. So an if-condition for amount a, then a separate column for amount B. If your goal is to give each amount a separate column, you could also look into the 'Pivot Columns' functionality. It will give each amount a separate column. Just make sure not to aggregate the values!
Excellent presentation and very helpful. Nice and simple. I have to say that it feels odd not putting “end if” or at least “end” at the end of the statement. Just seems wrong 😂. Thank You.
really fantastic , and your teaching also very simple yet effective. I am to use each cell style name in a column by only replacing the second alphabet with symbol. Example - AM1234, should change as A/1234, AL1234 should change as A-1234 etc., and there could be min 10 variant like this. Is this possible in power query if function ?
Hi! Thank you very much for all your videos! I have a problem, I want to write an if statement but the columns should not be hard coded but dynamically selected based on another table. Any ideas?
Great video, Rick. When using list contains with if and the criteria are text, is there a way to make the if ignore the text case. e.g the criteria "ABC" also works for "abc". It would help reduce the number of criteria in the list....
Hey Kevin, You sure can. The M language has a comparer function called Comparer.OrdinalIgnoreCase. You can use it with List.Contains. Here's an example: List.Contains( {"a", "b", "c", "d"}, "D", Comparer.OrdinalIgnoreCase ) For more info, check out: powerquery.how/list-contains/ Cheers!
Hi Apostolos. It depends what you mean. For example, you can write { 1 .. 10 } and it will generate the range from 1 up to and including 10. So you could check whether a value falls within that category. Does that help?
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.
I want to remove the last character with an if function, let's say using this video under packaging, I only want to remove the letter "h" at the end of all words, this will only affect all words that end with "h". I want the new column to list "eac" if affected, but also list the word package if not affected.
How can we work in powerquery for these conditions - Day 1 = P, Day 2 = W, Day 3 = OD = eligible for W Day 1 = LWP, Day 2 = W, Day 3 = LWP = not eligible for W so W should be calculated as LWP or EL Where, P = present, OD = outdoor duty, W = Week Off, LWP = leave without pay, EL = earned Leave we want to apply here that if day 1 employee is P/OD or day 3 employee is P/OD then day 2 wll be calculated as W otherwise LWP.
@Gorilla BI i have a list of 80 items in column A spread out over some 80000 plus rows. Sometimes the item is on row 5th or 125th or 450th row etc. I want power query to look for those items and copy on the same row in a new custom column. How can I do that since the list has large number of items and using a conditional column will not be practical. Thanks.
@@ram-it.damn-it you can save your items in a separate table with two columns. One with the value to look for and one value to return. Then use merge queries in your main query to put the value return next to the original value. Hope that helps! Rick
I would like to create a custom column that takes into account multiple inputs and outputs. To illustrate as an example, the values "BASKET FOLDER" and "SCREENBOX" should be associated with both "PERSON_A" and "PERSON_B" . I tried creating a conditional column, but it'd only return the first output only, how can I go about creating a custom column for this case please ?
Thank you for that video, it comes close to my challenge I have. I have conditions with several results. E.g. different locations like Paris, London... and persons, which are related with this location. How would you write the if statement to get all the names in a column from a certain location? Thank you very much for your tip
Hi Marcel. I'm not sure I fully understand your challenge. If the result of your condition is a list of results, you could for example use Text.Combine( {YourList}, ", " ) to combine them in a comma seperate list. You can see how I applied that technique for grouping in this article: excelgorilla.com/power-query/aggregate-text-values/ Hope that helps. Rick
@@BIGorilla Hi Rick, thank you for your replay. It is very appreciated. Below I send you a concrete example of a table on the top with some conditions, which are marked with "1", when they were fulfilled and with "0" when not. In the example you see, that User1 and User3 fulfills the same conditions (C1 | S1 | T1) and will be grouped in "Group1". I thought that it should be possible to create an array with objects that fulfills the same conditions . ---------- | C1 | S1 | T1 | User1 | 1 | 1 | 1 | User2 | 1 | 0 | 1 | User3 | 1 | 1 | 1 | Group1 | User1 User2
@@MarcelKuster74 The link in my previous post explains how you can do it. To help you out, you can paste below code in the advanced editor in Power Query. Hope that works for you! let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dZNLDsIwDESvgrLuIv6kSQ/CquqSC8D9JUCUYiq/RRXpaTTxZNx1LdfH7X6RMpX6+j7nNu1YD/Q+D2w7Pqk9N2k5ngMO3j14BzxykyWfRCpwyS8VzW8VA/6NWk/+Dfxn8OklfWAZoIe8WsM8Qa+QVxX0Bhyq1QbzQLnagQ/gkNegX5N8fqNFhrwGea2BHvIa9GuQ15bc36Ff/+3I/5+owGGf3UHf8vk95o166NcH+C+5Tzv1uz0B", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [User = _t, C1 = _t, S1 = _t, T1 = _t]), #"Grouped Rows" = Table.Group(Source, {"C1", "S1", "T1"}, {{"Users", each Text.Combine([User], ", "), type nullable text}}), #"Added Index" = Table.AddIndexColumn(#"Grouped Rows", "Index", 1, 1, Int64.Type), #"Added Prefix" = Table.TransformColumns(#"Added Index", {{"Index", each "Group " & Text.From(_, "nl-NL"), type text}}), #"Reordered Columns" = Table.ReorderColumns(#"Added Prefix",{"Index", "C1", "S1", "T1", "Users"}) in #"Reordered Columns"
I'm trying to find the correct Power Query formula to do the following: Columns C-Z have various text in them. I need a formula that looks at columns C-Z and returns the full value of each that contain the word "Significant". I started with adding a Conditional Column and selected columns C-Z with each being 'contains' and returning the same column. The problem that I've ran into is that "Significant" may be in 5 columns but it's only returning the value of the first column that contains "Significant". What is the correct formula to get it to combine all of the values of the columns if they contain "Significant"? Example: Column C - Tom has Significant loss. Column D - Sarah has Significant gain. Column E - Bobby has minimum gain. Colum F - Jen has marginal loss. etc. with various data in columns G-Z that may/may not include "Significant" End result based on above would be: Tom has Significant loss. Sarah has Significant gain.
i need help to input 2 formulas into the power query instead of do it after the query loads. the first one is i want to remove duplicates between 2 columns, example repeat name and date, and only "keep the unique name with unique date". also i have a formula "To Determine Older Dates Of Events And Only Keep Most Recent In Excel" but im not sure how to input that into the power query or if it can even be done
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!!
Yazan, What would your guess be? you can start with if [columnname] = "Name" then if [columnname2] = 3 then if [columnname3] > 1 then true else false else false else false But a2, b2 and c2 should have a column name. Give it a try, the instructions in the video should get you going. Cheers, Rick
Hi, i would like to know if under same column, would it be possible i could treat it as percentage number as well as text? because i have to use text as reference for something else too, thanks, appreciate your help
Hi there, You can use something like: if Text.Lower( [ColumnValue] ) = "x" then true else false. The Text.Lower then first transforms the value to lower text. And after, you can immediately test its results.
Hi Rick, Based on your video I tried to make a conditional column that labels dates as history, present/today of future. Unfortunately it keeps giving me errors. It is the first step to create a column with history / present / future for weeknumbers for conditional formating a visual in PowerBI. What is in your opinion the best approach to achieve this?
Hi I have a similar situation but no solution- I have 1324 equipment numbers in table 1 all are unique values. Then I have 1174 equipment number in table 2 all are unique values. These equipment numbers are connected in the relation table. Table 2 has Hours of work in a coloumn for each equipment. What I intend to do is create a custom coloumn in table 1 where I can bring the hours of work for each equipment from table 1 and then where ever we don't have hours available in table 2 (1324-1174 = 150 equipments will have no hours as they don't exist in table 2). I want the custom coloumns to show 0 hours for these 150 equipments. Can you help.
Hi Julius, You can use all kinds of operators. = (equals) (does not equal) = (bigger than or equal to) and or not You can find the most common operators here: gorilla.bi/power-query/if-function-in-power-query/#common-operators Hope that helps! Rick
Hi Rick, I have a question and a piece of advise for you: Q: what mouse pointer highlighter tool are you using? Adv: please process your audio a bit more (de-esser, possibly a compressor): plosives are a bit annoying and could be suppressed.
Hi Geert! Thanks for watching. I'm using Camtasia to format the mouse pointer. Great software for any recording. And about the audio, I'll make sure to have a look at that. Thanks again, Rick
Excel Gorilla Aha, right, the mouse pointer effects. Well, rumor has it that Camtasia has an audio compressor you can add as an effect... :-) Could help with the plosives. In my opinion Camtasia is a bit limited in the audio processing department, so separate audio treatment might be advised. In your case: judging based on this one video compression may be all you need (all the rest is very clean). And while you’re in Camtasia, I suggest to use the zoom (and pan) options more often so things are a bit bigger on the screen. I tend to watch using my tablet (iPad) and other will simply use their smartphones...
@@BIGorilla Oh, I got it. I'm using power query with Excel indeed, but soon I'll switch to Power BI, cos it's quite hard to write code when it's just a plain text.
I need to add a new column with the values of the previous columns if they are below 0. I tried to apply the formula = Table.AddColumn(#"Filtered Rows", "Custom", each if [Column2] < 0 then [Column2] else null). But it returns an error. Is there any way how to do it? Thank you
Hoi Rick, als je een if opdracht hebt met vijf verschillende condities, hoe gaat PowerQuery daar mee om? Checkt hij regel voor regel wat de conditie moet zijn en vult hij dat dan in, OF, pakt hij de eerste conditie, loopt dan het hele bestand door en vult het in waar nodig en checkt dan de tweede conditie en loopt weer het hele bestand af, etc etc. Ben ik een beetje duidelijk?
Hoi Ferdi, Bij een if-conditie, wordt de eerste conditie die waar is, ingevuld op de regel. Op iedere regel wordt apart de if-statement uitgevoerd. Wanneer er uiteindelijk geen enkele conditie waar is, dan wordt de conditie ná de laatste 'else' clausule gebruikt. Dus bijvoorbeeld in onderstaande voorbeeld: if conditie 1 = true then 1 else if conditie 2 = true then 2 else if conditie 3 = true then 3 else if conditie 4 = true then 4 else if conditie 5 = true then 5 else 6 Zelfs wanneer alle condities waar zijn, dan wordt enkel de eerste conditie die waar is getoond als resultaat. En wanneer geen enkele conditie waar is, dan wordt het antwoord 6 weergegeven. Beantwoord dat je vraag? Cheers, Rick
@@BIGorilla Dankjewel Rick, helemaal duidelijk, elke regel wordt dus apart in z'n geheel afgehandeld op alle condities, dat wilde ik graag weten. Heb je voor mij tot slot de als-functie hoe die in het Nederlands te schrijven ajb?
I'm trying the List.Contains example in 19:00 but instead of having a vector with values between {}, I'm referencing another column instead. This column has some null values, which I would expect List.Contains to simply ignore, but I'm getting an error "Expression.Error: We cannot convert the value null to type List. Details: Value = Type=[Type]" How can I solve this??
Just found a solution: you have to reference the previous applied step before the list so it looks like this: List.Contains(#"Previous Applied step"[column with the values to lookup],[column with the value which u want to check against])
Hi! If I need to filter based on two separates columns to get 1 output column, must I create 2 custom column or just 1 custom column (with the entire conditional formula written in it)?
Hi grace, You can do this in a single column but also in multiple if you prefer. Whatever makes it easier for you. I would prefer taking a single custom column to build the logic if it's easy. Good luck! Rick
IF(J2="NEUBUS3AXXX",K2,LEFT(K2,4)&"/"&IF(D2=202,RIGHT(12,10),IF(D2=210,RIGHT(H2,10))))))) can you please guide how to write this formula using power query
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"
Hi shawn, Thanks for your question. And regarding combining 'and' and 'or', this is possible in Power Query. You could write something like: if ( [Column1] = "A" and [Column2] = "B" ) or ( [Column1] = "A" and [Column2] = "C" ) then "Result1" else "Result2" . I like wrapping my statements between parenthesis just so it's clear where the condition ends. I hope you're able to change the logic to fit your needs Regards, RIck
@@BIGorilla Thank you for your reply. Your videos are great. I actually saw in one of your videos (maybe it was this one) where you talked about Lists in an IF statement. I was able to incorporate this into my AND OR. I think what you described here might be cleaner but I will need to experiment with it. Here is what I came up with. if [ProductLine]="abc" and [#"Domestic / INT"]="Domestic" and List.Contains( {"6","C"},[Top Level SW] ) then "XYZ" else "123" Again, thank you for your great videos.
Hi there, can we use if function to find out start and end date? I know we can use maxifs and minifs in excel but videos in youtube re maxifs / minifs are very complicated when we try to achieve in power query.
Hi Kanwaljeet, That's a great question. One way to approach this, is to add a custom column in Power Query with the formula: if [Date] = List.Max(#"PreviousStepName"[Date] ) then "MaxDate" else if [Date] = List.Min(#"PreviousStepName"[Date] ) then "MinDate" else null This will return the text values MaxDate and MinDate for respectively the highest and lowest date, and null for dates that are not equal to the min or max. You may find that the performance on very big datasets may not be great. So you could experiment if adding the Min and Max value as variable improves performance. Cheers, Rick
To learn more about functions and syntax make sure to check out: powerquery.how/
Enjoy!
Masterclass, Rick! Brilliant, professional presentation absolutely geared to a maximum learning experience for all of us. Thanks!
I salute you for this amazing series of Power BI, really one of the few high quality in the online. Thank you
Dude you are great
Amazingly explained
Thanks Future wire, great to have you on the Channel!
Big help. Thank you.
That's great to hear Louie. Enjoy!
I just started out with PowerBi. This is will be my goto video for all things "if". Thank you for posting!!
Cool! Make sure to check out the written article too. So you can copy paste the code: gorilla.bi/power-query/if-function-in-power-query/
Awesome!
I didn’t watch this video for quite a long time because I thought I knew everything. Wrong, I was mistaken!
I didn’t know the trick with List.Contains. But now, I do :)
Thank you so much! 👏
Thank you for this video. At work I get a weekly statement that has about 5,000 invoices. This supplier has 6 supplier ID numbers and the statement has invoices going back to 2018. I have to run a query on each of the 6 supplier ID’s going back to 2018 to compile a table I can do an XLookup against. The table ends up having over 450,000 rows and there’s one column that has 8 nested If/Then formulas that determines the status of each invoice. Right now I I have the Excel file with the If/Then formula on my hard drive and it’s over 100MB but I created that as a Power Query table on the shared drive and it’s only 80MB. I’d like to save time by learning to recreate the If/Then formula in Power Query and I believe this video will help me.
Wow so glad I found your channel. Love how you explain and use examples!
Thank you very much! 🙏
Beautifully explained. Thank a ton! :)
I'm glad it helped!
Fantastic video that got me from zero to 60 in record time. I’ve bet fighting a problem and the list function will help me.
Really useful, the way you illustrated the expressions will surely help others to understand the formula.. Thank you
Thank you so much and you are a life saver! I was searching for “conditional column” along with IF statements for several hours and then I found your video. I already subscribed your channel, and thank you again,
great pace and clear examples - thanks!
really good, clear and not too long but enough detail with different scenarios. Thanks
Many thanks, this video saved my day.
Outstanding tutorial. Thank you very much.
The acronym, eof, stands for "end of file". This underscores your observation that "Token eof expected" is a vague error message. The meaning of "eof" resolves the ambiguity only slightly. I think Power Query throws the message because the Advanced Editor can only execute a query script after reading a complete chain of syntactically correct M language commands. The uppercase IF ends the translation process unexpectedly.
Paul! Thanks for adding that valuable information here in the comments. I'm sure it will be helpful to others. Would be great if MS updates the error messages to something clearer.
Thanks again!
Very clear presentation. Thanks.
Thanks BigBear. Hope it helps!
This is was awesome. Helped me fix my error.
One thing I have found, and that is the order of evaluation can have an affect on the outcome so ;
Table.ReplaceValue( Source, "AAA", "NUMBER" , ( current, old , new )=>
if Value.Type(current ) = type number then new else
if Text.Start( current ,1) = "a" then old else current ,
Columns )
Works, but if you start by asking text start you need a try / otherwise;
Table.ReplaceValue( Source, "AAA", "Number", ( x , y , z )=>
try
if Text.Start( x ,1 ) = "a" then y else x
otherwise
if Value.Type(x )= type number then z else x ,
Columns )
You're amazing for this one!
Very helpful videos - nice examples to help understand the application. Thank you!
Good job Rick - lots of examples to learn from in there. 👍
Thanks for the heads up, hopefully more is coming soon!
Thank you! This was very helpful!
Thank you for sharing the information. Really useful.
Thanks Viral!
Clearly explained👍
Excelente video, saludos desde Mexico.
Thank you
Salvou meu dia, obrigado!
Thank you Sir!!!
16:30 If not
19:00 List.Contains
Good addition! Thanks
Thank you Thank you!!! Your great information is exactly what I needed. Thank you for sharing your knowledge! 🤩
Really helpful. For the last 3 hours I was searching and finally I found the best video along with the best Channel. Well done sir!
Thanks for the kind words, great to have you visit my channel!
Great thanks Rick for sharing this video on CZcams. It helps me to build custom columns in my Power BI report..thanks again!
Happy to help Shantanu!
Very Helpful! Thanks!
Thanks mate! This will help me! I appreciate the time you took to make this.
Glad to have you on the channel. Thanks for the support MTA! 😁
Greate starter video. Cheers
Thanks
My pleasure !
Simple amazing. Thanks very much for this video. I learnt a lot.
Thank you for posting this! Also thank you for going though the errors that might occur and the why.
I'm glad it was helpful :) thanks Sabrena
AMAZING tutorial…. Wow!
Thanks soo much
It worked with me using condition column
Excellent video........thanks BI Gorilla
Great video!! Thanks a lot. Lots of lessons in one video
Thanks Rick. Super Useful..
THANK YOU
Thank you for this video. Your explanation of the if statement returning a formula is close to what I need, I think. How would I write an if statement if I wanted to check the order number for a certain amount, and then create a column to return the quantity into a new column. I.e. I have four different types of commission codes I want to check in one column, and if commission code a = a certain amount, then, I want the commission amount to populate a new column A,; if commission code B = a certain amount, then I want the commission amount to populate a new column B, and so on. Thank you so much
Hey Courtney. You could work with separate columns. Each column defining the if condition you need. So an if-condition for amount a, then a separate column for amount B.
If your goal is to give each amount a separate column, you could also look into the 'Pivot Columns' functionality. It will give each amount a separate column. Just make sure not to aggregate the values!
Excellent explanation. Very helpful!!
Thank you ❤
Excellent presentation and very helpful.
Nice and simple.
I have to say that it feels odd not putting “end if” or at least “end” at the end of the statement. Just seems wrong 😂.
Thank You.
I feel the oddness J. Every language has its perks!
Excellent
Extremely helpful!
Super excellent video! Can you please recommend a course for Power Query and M language..TIA!
Thank you!!!!!!
really fantastic , and your teaching also very simple yet effective. I am to use each cell style name in a column by only replacing the second alphabet with symbol. Example - AM1234, should change as A/1234, AL1234 should change as A-1234 etc., and there could be min 10 variant like this. Is this possible in power query if function ?
Hi Rick, amazing videos, many thanks !
Very good video my friend.. clear real life examples. Keep up the good work!
Awesome, thanks for sharing!
You are awesome!
Hi! Thank you very much for all your videos! I have a problem, I want to write an if statement but the columns should not be hard coded but dynamically selected based on another table. Any ideas?
Great content 🙌🔥🙏
Great video, Rick. When using list contains with if and the criteria are text, is there a way to make the if ignore the text case. e.g the criteria "ABC" also works for "abc". It would help reduce the number of criteria in the list....
Hey Kevin,
You sure can. The M language has a comparer function called Comparer.OrdinalIgnoreCase. You can use it with List.Contains. Here's an example:
List.Contains( {"a", "b", "c", "d"}, "D", Comparer.OrdinalIgnoreCase )
For more info, check out: powerquery.how/list-contains/
Cheers!
thank you .. very helpful
Glad it helped!
This is so amazing!!! Thank you! 🤗💞
Thank you so much for this
Ofcourse, and thanks for visiting the channel!
Subscribed 🙌🔥🙏
Awesome!!!
Thanks Gorilla Excel, excellent tutorial!
Is there a way to use the List function to filter ranges > < ?
Hi Apostolos. It depends what you mean. For example, you can write { 1 .. 10 } and it will generate the range from 1 up to and including 10. So you could check whether a value falls within that category. Does that help?
Rick, great work, as always! Have you dealt much with complex and/or combos in Power Query?
Hey kirill,
I think I've dealt with complex and and or combos. What situation are you thinking about?
thx BRO
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.
I want to remove the last character with an if function, let's say using this video under packaging, I only want to remove the letter "h" at the end of all words, this will only affect all words that end with "h". I want the new column to list "eac" if affected, but also list the word package if not affected.
Is it possible create a list in excel then import in PQ and use that list to select which columns we want?
Do you have any tips on using the if function? Let me know in the comments!
How can we work in powerquery for these conditions -
Day 1 = P, Day 2 = W, Day 3 = OD = eligible for W
Day 1 = LWP, Day 2 = W, Day 3 = LWP = not eligible for W so W should be calculated as LWP or EL
Where, P = present, OD = outdoor duty, W = Week Off, LWP = leave without pay, EL = earned Leave
we want to apply here that if day 1 employee is P/OD or day 3 employee is P/OD then day 2 wll be calculated as W otherwise LWP.
@@sameerbhaiful I'm not sure I follow your case
@@BIGorilla Thanks a lott
@Gorilla BI i have a list of 80 items in column A spread out over some 80000 plus rows. Sometimes the item is on row 5th or 125th or 450th row etc. I want power query to look for those items and copy on the same row in a new custom column. How can I do that since the list has large number of items and using a conditional column will not be practical. Thanks.
@@ram-it.damn-it you can save your items in a separate table with two columns. One with the value to look for and one value to return. Then use merge queries in your main query to put the value return next to the original value. Hope that helps!
Rick
I would like to create a custom column that takes into account multiple inputs and outputs. To illustrate as an example, the values "BASKET FOLDER" and "SCREENBOX" should be associated with both "PERSON_A" and "PERSON_B" . I tried creating a conditional column, but it'd only return the first output only, how can I go about creating a custom column for this case please ?
Thank you for that video, it comes close to my challenge I have. I have conditions with several results. E.g. different locations like Paris, London... and persons, which are related with this location. How would you write the if statement to get all the names in a column from a certain location? Thank you very much for your tip
Hi Marcel. I'm not sure I fully understand your challenge. If the result of your condition is a list of results, you could for example use Text.Combine( {YourList}, ", " ) to combine them in a comma seperate list. You can see how I applied that technique for grouping in this article:
excelgorilla.com/power-query/aggregate-text-values/
Hope that helps.
Rick
@@BIGorilla Hi Rick, thank you for your replay. It is very appreciated. Below I send you a concrete example of a table on the top with some conditions, which are marked with "1", when they were fulfilled and with "0" when not. In the example you see, that User1 and User3 fulfills the same conditions (C1 | S1 | T1) and will be grouped in "Group1". I thought that it should be possible to create an array with objects that fulfills the same conditions .
---------- | C1 | S1 | T1 |
User1 | 1 | 1 | 1 |
User2 | 1 | 0 | 1 |
User3 | 1 | 1 | 1 |
Group1 | User1 User2
@@MarcelKuster74
The link in my previous post explains how you can do it. To help you out, you can paste below code in the advanced editor in Power Query. Hope that works for you!
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dZNLDsIwDESvgrLuIv6kSQ/CquqSC8D9JUCUYiq/RRXpaTTxZNx1LdfH7X6RMpX6+j7nNu1YD/Q+D2w7Pqk9N2k5ngMO3j14BzxykyWfRCpwyS8VzW8VA/6NWk/+Dfxn8OklfWAZoIe8WsM8Qa+QVxX0Bhyq1QbzQLnagQ/gkNegX5N8fqNFhrwGea2BHvIa9GuQ15bc36Ff/+3I/5+owGGf3UHf8vk95o166NcH+C+5Tzv1uz0B", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [User = _t, C1 = _t, S1 = _t, T1 = _t]),
#"Grouped Rows" = Table.Group(Source, {"C1", "S1", "T1"}, {{"Users", each Text.Combine([User], ", "), type nullable text}}),
#"Added Index" = Table.AddIndexColumn(#"Grouped Rows", "Index", 1, 1, Int64.Type),
#"Added Prefix" = Table.TransformColumns(#"Added Index", {{"Index", each "Group " & Text.From(_, "nl-NL"), type text}}),
#"Reordered Columns" = Table.ReorderColumns(#"Added Prefix",{"Index", "C1", "S1", "T1", "Users"})
in
#"Reordered Columns"
Can i for example say if column is null then give mi value from different column else look at original column and give me that value?
I am able to add lists to the if statements, does this mean I can also add lists to the if statement based on columns from other tables?
I'm trying to find the correct Power Query formula to do the following:
Columns C-Z have various text in them. I need a formula that looks at columns C-Z and returns the full value of each that contain the word "Significant". I started with adding a Conditional Column and selected columns C-Z with each being 'contains' and returning the same column. The problem that I've ran into is that "Significant" may be in 5 columns but it's only returning the value of the first column that contains "Significant". What is the correct formula to get it to combine all of the values of the columns if they contain "Significant"?
Example:
Column C - Tom has Significant loss.
Column D - Sarah has Significant gain.
Column E - Bobby has minimum gain.
Colum F - Jen has marginal loss.
etc. with various data in columns G-Z that may/may not include "Significant"
End result based on above would be:
Tom has Significant loss.
Sarah has Significant gain.
i need help to input 2 formulas into the power query instead of do it after the query loads. the first one is i want to remove duplicates between 2 columns, example repeat name and date, and only "keep the unique name with unique date". also i have a formula "To Determine Older Dates Of Events And Only Keep Most Recent In Excel" but im not sure how to input that into the power query or if it can even be done
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!!
Yazan,
What would your guess be?
you can start with
if [columnname] = "Name" then
if [columnname2] = 3 then
if [columnname3] > 1 then
true
else false
else false
else false
But a2, b2 and c2 should have a column name. Give it a try, the instructions in the video should get you going.
Cheers,
Rick
Great video!!
Appreciate the support!
Mate do you have any other videos on lists like the list contains you used here?
Hi, i would like to know if under same column, would it be possible i could treat it as percentage number as well as text? because i have to use text as reference for something else too, thanks, appreciate your help
Hey I'm really struggling to build an of statement that picks up lowercase
Hi there,
You can use something like: if Text.Lower( [ColumnValue] ) = "x" then true else false.
The Text.Lower then first transforms the value to lower text. And after, you can immediately test its results.
I hv question, want create new coI. I got 2 columns DOJ n DOL if DOJ is blank then Today() - DOL else DOJ minus DOL
Dave Portnoy is an excel wizard!
Amazing. Who's dave though?? 😂
^^ Rick
Hi Rick, Based on your video I tried to make a conditional column that labels dates as history, present/today of future. Unfortunately it keeps giving me errors. It is the first step to create a column with history / present / future for weeknumbers for conditional formating a visual in PowerBI. What is in your opinion the best approach to achieve this?
Hi I have a similar situation but no solution-
I have 1324 equipment numbers in table 1 all are unique values.
Then I have 1174 equipment number in table 2 all are unique values.
These equipment numbers are connected in the relation table.
Table 2 has Hours of work in a coloumn for each equipment.
What I intend to do is create a custom coloumn in table 1 where I can bring the hours of work for each equipment from table 1 and then where ever we don't have hours available in table 2 (1324-1174 = 150 equipments will have no hours as they don't exist in table 2). I want the custom coloumns to show 0 hours for these 150 equipments. Can you help.
Hi, i do have a question regarding operators. Can we only select = or = not as operators? Not =?
*Edit. Solved it. Je bent top! thanks
Hi Julius,
You can use all kinds of operators.
= (equals)
(does not equal)
= (bigger than or equal to)
and
or
not
You can find the most common operators here:
gorilla.bi/power-query/if-function-in-power-query/#common-operators
Hope that helps!
Rick
Usually EOF means End Of File, but seeing this as an error-reason?
Hi Rick,
I have a question and a piece of advise for you:
Q: what mouse pointer highlighter tool are you using?
Adv: please process your audio a bit more (de-esser, possibly a compressor): plosives are a bit annoying and could be suppressed.
Hi Geert! Thanks for watching. I'm using Camtasia to format the mouse pointer. Great software for any recording. And about the audio, I'll make sure to have a look at that. Thanks again,
Rick
Excel Gorilla Aha, right, the mouse pointer effects.
Well, rumor has it that Camtasia has an audio compressor you can add as an effect... :-)
Could help with the plosives.
In my opinion Camtasia is a bit limited in the audio processing department, so separate audio treatment might be advised.
In your case: judging based on this one video compression may be all you need (all the rest is very clean).
And while you’re in Camtasia, I suggest to use the zoom (and pan) options more often so things are a bit bigger on the screen.
I tend to watch using my tablet (iPad) and other will simply use their smartphones...
Great video. How is your syntax highlighted everywhere and when you type List it offers attributes of the function? Is it some kind of a plugin?
Hi Vyacheslav,
The syntax highlighting is done by default in recent version of Power BI. Are you using a version of Excel?
@@BIGorilla Oh, I got it. I'm using power query with Excel indeed, but soon I'll switch to Power BI, cos it's quite hard to write code when it's just a plain text.
I need to add a new column with the values of the previous columns if they are below 0. I tried to apply the formula = Table.AddColumn(#"Filtered Rows", "Custom", each if [Column2] < 0 then [Column2] else null). But it returns an error. Is there any way how to do it? Thank you
The code looks good to me. What error do you get?
Hoi Rick, als je een if opdracht hebt met vijf verschillende condities, hoe gaat PowerQuery daar mee om? Checkt hij regel voor regel wat de conditie moet zijn en vult hij dat dan in, OF, pakt hij de eerste conditie, loopt dan het hele bestand door en vult het in waar nodig en checkt dan de tweede conditie en loopt weer het hele bestand af, etc etc. Ben ik een beetje duidelijk?
Hoi Ferdi,
Bij een if-conditie, wordt de eerste conditie die waar is, ingevuld op de regel. Op iedere regel wordt apart de if-statement uitgevoerd. Wanneer er uiteindelijk geen enkele conditie waar is, dan wordt de conditie ná de laatste 'else' clausule gebruikt. Dus bijvoorbeeld in onderstaande voorbeeld:
if conditie 1 = true then 1 else
if conditie 2 = true then 2 else
if conditie 3 = true then 3 else
if conditie 4 = true then 4 else
if conditie 5 = true then 5 else
6
Zelfs wanneer alle condities waar zijn, dan wordt enkel de eerste conditie die waar is getoond als resultaat. En wanneer geen enkele conditie waar is, dan wordt het antwoord 6 weergegeven. Beantwoord dat je vraag?
Cheers,
Rick
@@BIGorilla Dankjewel Rick, helemaal duidelijk, elke regel wordt dus apart in z'n geheel afgehandeld op alle condities, dat wilde ik graag weten. Heb je voor mij tot slot de als-functie hoe die in het Nederlands te schrijven ajb?
@@ferdirosa7547 helaas, ik hen geen idee hoe dit in het Nederlands is. Zo ver ik weet is Power Query alleen Engels.
Gr.
Rick
I'm trying the List.Contains example in 19:00 but instead of having a vector with values between {}, I'm referencing another column instead. This column has some null values, which I would expect List.Contains to simply ignore, but I'm getting an error "Expression.Error: We cannot convert the value null to type List.
Details:
Value =
Type=[Type]"
How can I solve this??
Just found a solution: you have to reference the previous applied step before the list so it looks like this:
List.Contains(#"Previous Applied step"[column with the values to lookup],[column with the value which u want to check against])
Hi! If I need to filter based on two separates columns to get 1 output column, must I create 2 custom column or just 1 custom column (with the entire conditional formula written in it)?
Hi grace,
You can do this in a single column but also in multiple if you prefer. Whatever makes it easier for you. I would prefer taking a single custom column to build the logic if it's easy. Good luck!
Rick
@@BIGorilla Thanks Rick! Will give it a try
I cracked soft soft today
IF(J2="NEUBUS3AXXX",K2,LEFT(K2,4)&"/"&IF(D2=202,RIGHT(12,10),IF(D2=210,RIGHT(H2,10))))))) can you please guide how to write this formula using power query
How can I write an IFERROR?
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"
Hi shawn,
Thanks for your question. And regarding combining 'and' and 'or', this is possible in Power Query. You could write something like:
if ( [Column1] = "A" and [Column2] = "B" ) or ( [Column1] = "A" and [Column2] = "C" ) then "Result1" else "Result2" . I like wrapping my statements between parenthesis just so it's clear where the condition ends.
I hope you're able to change the logic to fit your needs
Regards,
RIck
@@BIGorilla Thank you for your reply. Your videos are great. I actually saw in one of your videos (maybe it was this one) where you talked about Lists in an IF statement. I was able to incorporate this into my AND OR. I think what you described here might be cleaner but I will need to experiment with it. Here is what I came up with.
if [ProductLine]="abc" and [#"Domestic / INT"]="Domestic" and List.Contains( {"6","C"},[Top Level SW] ) then "XYZ" else "123"
Again, thank you for your great videos.
@@shawnvargas9152 that looks sublime. I wouldn't know how to improve it any better :) enjoy!
Hi there, can we use if function to find out start and end date? I know we can use maxifs and minifs in excel but videos in youtube re maxifs / minifs are very complicated when we try to achieve in power query.
Hi Kanwaljeet,
That's a great question. One way to approach this, is to add a custom column in Power Query with the formula:
if [Date] = List.Max(#"PreviousStepName"[Date] ) then "MaxDate" else
if [Date] = List.Min(#"PreviousStepName"[Date] ) then "MinDate" else null
This will return the text values MaxDate and MinDate for respectively the highest and lowest date, and null for dates that are not equal to the min or max.
You may find that the performance on very big datasets may not be great. So you could experiment if adding the Min and Max value as variable improves performance.
Cheers,
Rick
@@BIGorilla Thank you Rick for your help.