5 Surprising Power Query Tricks You Need to Know!

folks here are five really really awesome M tricks I don't really want to waste any time let's hit some music let's go check it out all right the first trick is concatenation while summarizing the data I'm sure you've done summarization or group Buy in power query but you can also do some crazy concatenation in that so take a look at the simple example that I have we have a couple of columns date region product ID units and then we have created two additional columns one for the year and one for the month name now let's just say that I would like to create a list of all the products that were sold for every single year every single month and every single region so year month and the region give me the list of the unique products that were sold how do we do that now of course we will have to summarize the data first and I'm going to go over to the transform tab click on the group bu option and in the group buy I'm just going to click on Advanced and that is where I will pick up the first summarization which is a year second summarization which is going to be the month name and the the third one is going to be the region of course region and then in the region let's just say that I want to have total units total units is just a calculation that I'm creating it at the moment but uh of course I would want to have the list of the products as well so units click on okay and then we are kind of good to go and that's what we have it at the moment now if you take a look at this formula up on the top table. group it has created this particular calculation or the column total units right here and that is the formula for that let's just revise this formula for a bit and create one additional column which is going to give me the list of all the unique products that were sold in that month so what do I do I am just going to format this a bit all right if you now take a look at the M code right here this actually creates a list for every single column God damn it lights gone I'm going to continue with this and we are back we were speaking about this formula that we just created which was nothing but total units is a column that we created every single column column that you create within the group row step is going to be a separate list altogether so total units is a list right here you can see that the entire function is within the list should we want to create more such formulas or more such columns I keep saying formulas but more such columns we have to encapsulate within the curly Braes now let's just create one more column and that column is going to be nothing but the list of the products and I'm going to we have a a curly braces to begin with because that is going to be a list the first part of the formula is is what is the name of the column and that is considered that to be products and then I'm going to say hey uh each uncore which is going to give me the entire underlying table now if I kind of press enter right here you're going to see that I do get a table if I peek into the table you're going to see that we have all the columns that are matching this particular criteria which is 2011 the month of Jan and the region of Delhi and these are all the rows which are matching that criteria now from this particular table I just want to extract one column right here which is product ID so I can say something like hey just give me the product ID column so I can say product ID um close the bracket and press enter and what I'm going to get is one column extracted from this table and I'm going to get a list instead and I get the entire list now obviously in this list in a certain region in a certain month many products could have sold multiple number of times as well and that is why we have duplicacy right here I want to remove the duplicates and therefore I can use a function called list. dis now list. distinct is going to give me the unique items of course and I can just close the bracket right here and then I just want to combine all the items of that list so now we have all the uniques and I can now use the text. combine function to combine all the list items which are nothing but texts so text. combine uh it asks you hey give me all the texts but those texts should be in the form of a list so we have all the texts which are nothing but product names they are in the form of a list and that's what I'd like to combine and the separate is going to be let's say a comma and a space between the concatenation that is going to be formed and now we have all the products which are sold in the year of 11 the month of Jan and the region of New Delhi and these are list of all the products now sometimes you're not going to like this because there are like far too many products to display right here and you may want to have all the products split out in different rows not split out in different cells but split out in a single cell but in different rows so what you can do is instead of having the D limiter as nothing but a comma and a space I can have the D limiter as an enter So within the quotation marks I can use the shift enter to move to the next row and that is where I have placed an enter within the quotation marks and as soon as I confirm on this particular formula you're going to see that all the products that were sold in that uh month year and the region combination are now split into different rows although they are not rows like different cells altogether it is a single cell but the products are actually split out in different rows of data so you can choose any combination of how do you want to split the products either by a comma D limiter or by an enter D limiter that is up to you but that was trick number one all right trick number two is transposing the data without actually doing the transpose at the moment if you take a look at my data I've got three columns 1 2 and three and it has got a couple of rows now if I were to go back to the transform Tab and hit the transpose there the rows are going to be moved into columns and the columns are going to be moved into rows so take a look so row number one 2 and three which were vertical initially have now become horizontal and that has become the row of the data ABC also has become the row of the data that was initially a column so is the case with 10 20 and 30 now this is fine but in case your data volumes are very large you have a lot of rows and a lot of columns transposing can make your queries really really slow well what do you do you can actually use a series of M functions to be be able to transpose but not literally transpose the table it's going to give you the effect of the transpose but not really transpose the table but you're going to get the same output nevertheless I'm going to get rid of this and show you that trick I'm going to create a new step in the new step I will convert every single row into a list so this is going to be list number one the first row becomes a list the second row becomes a list and the third row becomes a list the formula for that is table. two rows and close the bracket I feed a table from the previous step which is nothing but the Source I click on okay and we now have three lists now that is going to be my list number one uh that is my list number one that is my list number two and that is my list number three now what I'm going to do is from the three lists that I have got which are nothing but three rows of the data I'm going to form three columns of the table so I'm going to say something like this hey take this list make the First Column take this list make the second column and take this list and make the third column of a table so you can now wrap this function in another function called table do from columns and close the bracket and close the bracket right here and what you're going to get is nothing but the transpose data so that's nothing but uh row number one uh and row number two row number three that is the transpose a b and c again in the rows 10 20 and 30 again in the rows but you have to use these combination of the functions uh table. two rows and table. from columns if you've been liking the video so far I have got a full fledged course on the M language in power query well these are just tips and tricks right but if you want to know the fundamentals of how the M language Works what are lists what are records the different concepts of power query how does the looping work what is each keyword in power query how do you create a custom function in power query and all of the good things that come around with the m language that will take you from the user interface level from solving basic problems to solve even more advanced and more sophisticated problems using the M language in power query I've got a full course that is open for now there are a few last St consider ation that you have to keep in mind if you would like to enroll into the course all of those good details are in the description of the video I highly recommend if you would like to learn the M language with me this would be a great time to get yourself a seat into the course all right trick number three is using records effectively now take a look at this data again a simple sales data that I have and we have to create two additional calculated columns one is going to be the units into price that is going to give me the sales value and then maybe a 10% Commission on the sales value well if you were doing it the way that the user interface suggests so you're going to go to the add columns Tab and maybe make a custom column once for the sales and the second time for the commission well you can create both the columns in just one go how again in the add columns tab make on the custom column and in the custom column I'm just going to maybe Define a calculation in a record and Records start in the square bracket so I'm just going to define the first calculation by saying hey my let's say sales calculation is going to be nothing but units into price so units multip by the price and that is my first calculation put a comma to create a second column and that is going to be my commission and that is going to be let's say sales which is going to be multiplied with let's say a 10% commission and that's pretty much it you close the record and this is good to go this is going to create two columns in just one go click on okay what you get is a record sure enough if you click on the expand button un check the name prefix sales and commission two columns that we created click on okay now we have been able to get the sales and the commission columns created in just one go obviously if you had more columns to create like five or six columns that you typically create in power query you can Define all of those columns within a single record expand them out at once and you have all the columns created in just one go trick number four there happens to be a keyword called the is keyword in B query that allows you to perform checks is that a number is that a table is that a list is that a record pretty much like the way you say it you just have to write it that way let me show you how so I have a column here 1 a and a list and I'm going to create a custom column just to check that is that value a list a number or a text so add columns tab custom column the custom column opens up and I'm going to create nothing but a check and I'm going to say hey is the column is a number so I'll just say is column column is number and that's all that I will say click on okay and what I'm going to get is a bunch of trues and falses now you can see that one is definitely a number therefore I get are true the rest are false you can also do that on structured objects like a list or a table so for example is this nothing but a list I can just press enter and you can see that against the list I do get a true now this is extremely helpful if you want to judge what data type of the value is there in a column or in a table or any other object structure that you're trying to work with and then of course you can use these true and falses to take further actions in your queries or make your queries robust or do whatever that you would want to do but the is keyword is super helpful to check check what data type is it final trick trick number five called the custom errors take a look at this little table that I have I have a person I have the number of hours and how much pay did you get the total pay now I'd like to be able to calculate the pay per hour for which I can just simply take my pay divide that by the number of hours and I'm going to get pay per hour let's just do that add columns custom column and I'm going to say hey pay is going to be divided by the hours I'm going to rename the column to pay per hour and click on okay okay and that's what we're going to get now obviously because here there was a text so 1100 cannot be divided with the text value called nil and therefore it returns you an error you know whatever the error might be what you can do in power query is that you can Define custom errors that means this message that you get right here can be customized to whatever you would want to call it now let's just see how that works I'm going to go over to the ad columns once again create a custom column and this time I'm going to use the try and the otherwise keyword let's just name the column first so called the Colum column paper our custom and here is where I will just paste the M code so at the start of the M code I'm saying hey why don't you just try to do this calculation try this particular calculation pay divided by the hours which is also what we just did while creating the previous column so the same thing pay divided by the hours and I'm using the try keyword here because just in case if this returns me an error I have the otherwise to fall back on the custom error that I have created now if you carefully take a look at it I have written the error keyword and within the square bracket which is nothing but a record I have defined three parameters which are the three key essential parts of what makes the error I mean there are five in total but we are just working with three one is reason of the error the other one is the message of the error and the third one is the detail of the error in the reason I have said error in the calculation in the message I have said pay or hours is not a number and should you fancy you can make it more colorful also by saying why do you make such mistakes you don't do that but hey I'm I'm just going to click on okay and now if you just Peak into the error that we have got well the person would understand now there happens to be a way to even extract what the error is that means these messages or the custom messages that we have written right here there is a way to be able to extract these messages from the errors only get that data and send it out to anybody just to kind of Rectify the data that the data was riddled with such errors all of that and a lot more is something that I talk about in my M language course which is live for enrollment in case you wouldd like to take your power query skills to the next level and learn the language behind the power queries engine which is the m language I highly recommend that you please take a look at the link in the description we've got three parts to the course the first one is Concepts then we talk about recipes and patterns and case studies and finally we talk about the most important functions in the M language like I said there are a few last eight considerations for the course please visit the link in the description of the video and I look forward to see you on the other [Music] [Applause] side

Channel: Goodly

Views: 25,700

Keywords: Power BI, Power Query, Excel, DAX, M Formulas, Power Query tricks, M language tips, Power Query tutorial, data analysis, Excel tips, data transformation, advanced Excel, data cleaning, Excel tricks, Excel functions, Power Query hacks, Excel data analysis, Power Query formulas, M code, Power Query secrets, Excel productivity, data manipulation, Power Query examples, M language functions, Microsoft Excel, data processing, Excel training, data wrangling

Id: 5HS6rj4RL2Q

Length: 14min 14sec (854 seconds)

Published: Thu Nov 02 2023