14 Power Query Hacks that Feel Illegal to Know

in this video I'm going to reveal 14 power query hacks that feel illegal to know for our first hack let's say we have these month names and they only show the first three letters of the month now if we had to try and convert this to a date type we get an error as power query doesn't recognize these as dates let's remove the change type step and with our month column selected click on the transform Tab and over here we get formats and click on ADD prefix and it says we can add a text value to the front of each value in the column so let's type 2023 comma and hit OK and we get the YO in front of our months but that's not what we want so let's click on the ABC icon that shows this is text and let's change this to a date format and we have converted our month names that were text to a date format the dates that it will show is the first of the month for each month name in power query there are various functions available to us if you don't know where exactly to find what you want then there is this drop down next to the table icon and if you click on it it gives you a list of functions that you can easily access like copy the entire table use for Australia's headers add index column choose columns merge or append queries so there's lots of different Transformations that you can find right here now we have our data in our query editor and we want to add a column that shows an additional field so here we want to show that these cells are for Manhattan in the add column tab click on custom column in the custom column dialog box let's call the new column City and here in the formula section let's type Manhattan in double quotation marks as texts are always in double quotations and hit OK and we have this new column with our city name for our next tag instead of using the change type step to transform our city column to text as it currently shows this unstructured data type latent State go to our formula bar and after our download quotation marks here for Manhattan let's insert a comma and type the word type and text and let's click out of our formula bar and hit enter and our city column has now changed to type text let's say we want to add a comment to one of our applied steps all we need to do is right click on a step go down to properties and select it and here in this dialog box we can rename the step in the name field but I'll leave this as is for now and here in the description is where we can type our comments and let's click ok and we get this little information icon next to our applied step and when we hover over it we can see our comments and if we go to our Advanced editor here is the code for our query and we can see the comments that we added comments are indicated by these two forward slashes and they're in green so you can also just add the comment here in the advanced editor but I prefer to edit in my applied steps pane to avoid any potential mistakes in my code let's say we want to see only the cells that were made for products pp004 all we need to do is right click on it go to text filters and select equals and we get all our cells for pp004 let's say we want to see the sales values less than 310 let's right click on it go to number filters and select less than and we get ourselves less than 310 and if we want to see cells greater than let's say 150 right click on it go to number filters select greater than and we get our values greater than 150. next if we have data where our text is not in the proper format so here we have names where there are not capitalized all we need to do is select the color column that we want to do this transformation on right click go to transform and click on capitalize each word and hey Presto our names are now in the proper format let's say we have these three tables in power query and we want to combine them all into one all we need to do is in our home tab go to append queries and click on the drop down next to append queries and click append queries as new we have three or more tables to append so let's select that and it brings up our available tables let's select the tables that we want to append our Jan sales data table is already in our tables to append section so let's click on Feb and hold down the control key and select the March sales data table and click on ADD and let's click OK and we've successfully appended all our tables in this new query called append when you can rename the query if you like just to note for this a PIN to work the order of our columns doesn't matter but the column names do need to be the same and remember power query is case sensitive if you do have data with different column headers that you want to combine then definitely watch this video here I'll leave the link below also I've created this query that determines whether or not the sales rep receives a bonus each month based on a condition if you would like to see exactly how I created this query please check out this video here I'll leave the link in the description also to turn this query into a function right click on the query and click on create function let's click create for now as we will add the parameters shortly let's call this function sales bonus function you can call it whatever you like and click OK if we click invoke we're going to get the query that we created for January but we won't be able to input other months into our function as well but there is no input parameter for this so let's create one let's select function and go to the advanced editor this edit function dialog box pops up let's click OK and here's the entire script for our function if the advanced editor is new to you I highly recommend you watch this video here I'll leave the link in the description below also the first part of our code is the input to our function the error operator is telling power query that this is a function and the code afterlit is telling power query what to do to the input that we give it here in our source step our table called January underscore 22 is being referenced as our source data so this table January underscore 22 will be the input at the beginning here for our entire function but this is not Dynamic so let's instead delete this first let and source as well as the equal sign and let's delete this entire line of code of the source step that brings in our January table now let's define what we want as our input jar function let's type hash sales data remember to include the hash and double quotation marks as we want to space in our field name you can call your field name or parameter whatever you like I'm calling this sales data as that's what I will be inputting into this function next we need to Define our input my inputs will be tables so let's type as table next wherever else we referenced Source we need to replace it with hash sales data in double quotation marks and let's delete these last two lines of code so the in and source as we want our table to be returned from our last step in our function which is this change type step and let's it done and hey Presto we now have a field called sales data to input our parameters I've imported the February 22 sales data table so let's select that and hit invoke and our function correctly calculated the Reps that are entitled to receive sales bonuses for February let's right click on our function that we just created and click on properties I'm going to leave the name of our function as is and you can type whatever description you want and let's hit OK and now when we hover over our function we can see our very important bit of information relating to this function next we have these dates here and we want a new column showing us the day and date all we need to do is click on the add column Tab and over here on the left we have column frame examples let's click on the drop down and click from selection and we get this new column one where we can enter our sample values 7th January 2023 was on a Saturday we can type that or we can also just select it here from the intellisense and let's insert a comma and type 7 January and let's hit enter power query hasn't yet picked up the pattern so let's type our next day being Sunday a January and hit enter and it's completed the rest of our days let's perform a quick check on our values and we see that our February dates still show January and the same for our March dates so let's hop it along and type February the day is correct and let's hit enter and it's correctly returned the dates for February including those for March let's hit OK and we have our new column with the date format that we prefer if you want to see the formula behind this custom column let's click on the gear icon for the added custom column step and here you can see the formula that power query is using the formulas combining three different elements using text Dot combine into a single text string the three elements combined are the weekday shown by the 4ds in lowercase the day of the month shown by the percentage sign and the letter D and the full month name shown by the four M's in uppercase and all these values are returned by using the date time.2 takes function and they are all concatenated into a single text string by using the tech start combine function so this would be a great way to learn M code next let's say we wanted this filter to be dynamic so if we input a sales value into a cell like this and hit refresh it's going to give us our values that are equal to or greater than that value here's how to do this I have both my tables in power query but before we get to that 80 of my viewers are still not subscribed if you're getting value from this video I would really appreciate it if you could please hit the Subscribe Button as this will really help me hit my 2023 goal of attaining 50 000 subscribers now let's get back to our video the first table has my transactional data and the table that has my parameter in it is called sales value parameter let's remove the change type step as we don't need that then let's right click next to our value and click on drill down and only our value is returned and if we look at our queries on the left here it shows is that this is now an object that we can use let's go back to our sales value data and select any value and right click go to number filters filter and select greater than or equal to you can do this with any value it doesn't really matter as it's a placeholder and we get the filtered row step over here in our formula bar let's delete our value and start typing sales value parameter the intellisense brings it absolute selected let's send this back to Excel and let's add a value in our parameter table and hit refresh and our query correctly updates to show cells that are greater than or equal to 200. we have our sales data and let's say we want to know what's our average quantity sold so that we can see which of our products which are shown here in the product ID column are selling higher than the average quantity sales and we want this average to be dynamic so when new sales data is added our average changes also and this is where moving the order of your applied steps to manipulate your M code comes in handy we have our two steps here which is our source step that brings in our source data and our change type step now let's select our quantity sold column and in the transform tab let's go to statistics and click on average and only the average of our quantity sold is returned but we still need our table to perform more Transformations so let's go to the view Tab and click on the advanced editor and here's the code that power query generates for our query now to get our table back all we need to do is Select our change type step and cut it and paste it here after a calculated average Step then remove this comma after the slice bracket of the change type step and instead add the comma here after our calculated average Step as there is a comma after each step except the step before the in statement let's move our calculated average step one line up just Anita now code and here we have calculated average in the in section let's replace it with our change type step as that's the step that has our table that we want returned and hit done and hey Presto we have our table back so now we can perform more Transformations so let's go to the add column Tab and insert a custom column and let's type if our quantity sold is greater than our calculated average remember the hash sign and double quotation marks as that's how we tell power query that our hard-coded text has a space then return yes else return no and let's hit OK and we have a new column that shows us whether or not our quantities are greater than the average let's filter out all the products with the no and we can see only our products that are performing better than the average and remember this is dynamic so whenever new sales data is added the calculated average will automatically update we have these two tables in our query editor one shows the quantity sold each month by product ID and the other shows the price per unit for each product ID now let's say we want to know the sales amounts let's click on our table with the quantity sold and in the Home tab let's go to merge queries and select merge queries as new in our merge queries dialog box we have our first table that we selected let's click on the drop down to select our second table that we're going to use which is the price data table next let's select the common column in verse with our tables which is the product ID column we will leave our join kind as the default which is the left utter and we can see that power query matched 9 out of 9 rows for us and let's click OK and we get a new column with our tables from our price data table let's click on expand and only select our price per unit column as we have our product ID column already in our quantity Soul table and be sure to uncheck use original column name as prefix and click ok and we've successfully performed our vlookup then all we need to do is multiply these two columns and we have our sales value for each line the merge function is really powerful if you would like to learn more ways to use the merge function I highly recommend you watch this video here

Channel: Miss Microsoft

Views: 6,081

Keywords:

Id: izsOzHJAiuo

Length: 17min 16sec (1036 seconds)

Published: Sat Apr 22 2023