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,
Awesome tutorial, excelente coverage of the if conditional statment. The power query syntax is clear. In addition to the three diferences covered in syntax when compared to Excel we a small consideration worthy to be mentioned. The condition or expression to be evaluated, in Excel it takes the value (points to the cell value) Not the column/header. If(@[package] = "Each",..........) (using the table referencing) or if(CellA1= "Each",...) (using the column row cell referencing style)
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! 👏
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
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.
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 )
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! 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?
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!
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])
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 ?
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.
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?
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
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
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 ?
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.
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
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?
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 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
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 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.
@@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.
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?
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 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
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
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.
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 if there are .. something like : =IF( [TXT1] "" , "NO1 " & [TXT1] , "" ) & IF ( [TXT2] "" , "NO2 " & [TXT2], "" ) thanks
Hi Gabi, that's a good exercise. You can try something like: if [TXT1] "" then "NO1 " & [TXT1] else if [TXT2] "" then "NO2 " & [TXT2] else "" Just make sure your [TXT1] and [TXT2] are formatted as text. 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...
Thank you! I learned a lot from your video. But there is a nested if problem on power query that I haven't found the answer yet : What is the equivalent of this excel formula on power query ? If(or(and([@[GA-FL X]]>1,[@[GA-FM X3]]=0,[@[GA-LM X3]]=0,[@HUB]=1),and([@[GA-FL X]]>1,[@[GA-FM X3]]=0,[@[GA LM X3]=0,[@HO]=1),[@[GA-FL]]/2,0)
Hi @Victoria, I could give it a shot. Can you double check the formula? I tried correcting since there seems to be a mistake in there. Is below the correct regular formula? IF( OR( AND( [@[GA-FL X]] > 1, [@[GA-FM X3]] = 0, [@[GA-LM X3]] = 0, [@HUB] = 1), AND( [@[GA-FL X]] > 1, [@[GA-FM X3]] = 0, [@[GA LM X3]] = 0, [@HO] = 1) ), [@[GA-FL]] / 2, 0 )
@@victoriasawyer1176, I changed the fields in your formula by easy alphabet. You can try and add below formula in the custom column box in Power Query: if
( [A] > 1 and [B] = 0 and [C] = 0 and [D] = 1 ) or ( [A] > 1 and [B] = 0 and [C] = 0 and [E] = 1 ) then [F] / 2 else 0 I hope that helps!
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
Wooow Thank you so much, you´ve helped me to develop my project, i´m watching you from Mexico
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/
Wow so glad I found your channel. Love how you explain and use examples!
Thank you very much! 🙏
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,
Awesome tutorial, excelente coverage of the if conditional statment.
The power query syntax is clear. In addition to the three diferences covered in syntax when compared to Excel we a small consideration worthy to be mentioned. The condition or expression to be evaluated, in Excel it takes the value (points to the cell value) Not the column/header. If(@[package] = "Each",..........) (using the table referencing) or
if(CellA1= "Each",...) (using the column row cell referencing style)
Thank you Sir!!!
16:30 If not
19:00 List.Contains
Good addition! Thanks
great pace and clear examples - thanks!
really good, clear and not too long but enough detail with different scenarios. Thanks
Really useful, the way you illustrated the expressions will surely help others to understand the formula.. Thank you
Very helpful videos - nice examples to help understand the application. Thank you!
Dude you are great
Amazingly explained
Thanks Future wire, great to have you on the Channel!
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.
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! 👏
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!
Very clear presentation. Thanks.
Thanks BigBear. Hope it helps!
Great thanks Rick for sharing this video on UA-cam. It helps me to build custom columns in my Power BI report..thanks again!
Happy to help Shantanu!
You're amazing for this one!
Good job Rick - lots of examples to learn from in there. 👍
Thanks for the heads up, hopefully more is coming soon!
Thank you for sharing the information. Really useful.
Thanks Viral!
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
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! 😁
Great video!! Thanks a lot. Lots of lessons in one video
Thank you Thank you!!! Your great information is exactly what I needed. Thank you for sharing your knowledge! 🤩
Many thanks, this video saved my day.
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
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.
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!
Excelente video, saludos desde Mexico.
Excellent video........thanks BI Gorilla
This is was awesome. Helped me fix my error.
Thank you! This was very helpful!
Big help. Thank you.
That's great to hear Louie. Enjoy!
Greate starter video. Cheers
Excellent explanation. Very helpful!!
Clearly explained👍
Extremely helpful!
Simple amazing. Thanks very much for this video. I learnt a lot.
AMAZING tutorial…. Wow!
Thanks soo much
Awesome explanation.. thanks
Very Helpful! Thanks!
Thanks Rick. Super Useful..
Very good video my friend.. clear real life examples. Keep up the good work!
Awesome, thanks for sharing!
Salvou meu dia, obrigado!
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 )
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! 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?
Super excellent video! Can you please recommend a course for Power Query and M language..TIA!
Hi Rick, amazing videos, many thanks !
This is so amazing!!! 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!
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])
Subscribed 🙌🔥🙏
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 ?
Great content 🙌🔥🙏
Mate do you have any other videos on lists like the list contains you used here?
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!
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?
Is it possible create a list in excel then import in PQ and use that list to select which columns we want?
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?
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
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?
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
You are awesome!
Thank you ❤
thank you .. very helpful
Glad it helped!
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 ?
Thank you so much for this
Ofcourse, and thanks for visiting the channel!
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?
Great video!!
Appreciate the support!
It worked with me using condition column
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.
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
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?
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 hv question, want create new coI. I got 2 columns DOJ n DOL if DOJ is blank then Today() - DOL else DOJ minus DOL
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?
Excellent
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.
Awesome!!!
Thank you!!!!!!
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.
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.
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
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
THANK YOU
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.
Thanks
My pleasure !
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
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.
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 if there are .. something like :
=IF( [TXT1] "" , "NO1 " & [TXT1] , "" ) & IF ( [TXT2] "" , "NO2 " & [TXT2], "" )
thanks
Hi Gabi, that's a good exercise. You can try something like:
if [TXT1] "" then "NO1 " & [TXT1] else
if [TXT2] "" then "NO2 " & [TXT2] else
""
Just make sure your [TXT1] and [TXT2] are formatted as text.
Hope that helps!
Rick
@@BIGorilla THANKS
Dave Portnoy is an excel wizard!
Amazing. Who's dave though?? 😂
^^ Rick
How can I write an IFERROR?
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...
Usually EOF means End Of File, but seeing this as an error-reason?
Thank you! I learned a lot from your video. But there is a nested if problem on power query that I haven't found the answer yet :
What is the equivalent of this excel formula on power query ?
If(or(and([@[GA-FL X]]>1,[@[GA-FM X3]]=0,[@[GA-LM X3]]=0,[@HUB]=1),and([@[GA-FL X]]>1,[@[GA-FM X3]]=0,[@[GA LM X3]=0,[@HO]=1),[@[GA-FL]]/2,0)
Hi @Victoria, I could give it a shot. Can you double check the formula? I tried correcting since there seems to be a mistake in there. Is below the correct regular formula?
IF(
OR(
AND( [@[GA-FL X]] > 1, [@[GA-FM X3]] = 0, [@[GA-LM X3]] = 0, [@HUB] = 1),
AND( [@[GA-FL X]] > 1, [@[GA-FM X3]] = 0, [@[GA LM X3]] = 0, [@HO] = 1)
),
[@[GA-FL]] / 2,
0 )
@@BIGorilla hello! Yes that's the right formula. Can you please try it? Thank you
@@victoriasawyer1176, I changed the fields in your formula by easy alphabet. You can try and add below formula in the custom column box in Power Query:
if
( [A] > 1 and [B] = 0 and [C] = 0 and [D] = 1 ) or
( [A] > 1 and [B] = 0 and [C] = 0 and [E] = 1 )
then [F] / 2
else 0
I hope that helps!
@@BIGorilla I already solved it but thanks a lot! I appreciate you actually took your time to help my problem