10 awesome Power Query tricks you NEED to know! | Excel Off The Grid

• I've got 10 awesome power query tricks for you today that will hopefully change the way that you think about Power Query.
• Let me know in the comments which was the best trick and which was the worst.
• For our first tip, we want to combine our address, town, and postco columns into a single column with each element on a separate row.
• The second tip involves renaming columns dynamically to avoid hardcoding names.
• The third tip focuses on how to work with nested tables to combine columns.
• Our last tip demonstrates how to create complex custom columns with clear and manageable steps.

I've got 10 awesome power query tricks for you today that will hopefully change the way that you think about Power Query. Now all I need you to do is to watch through the video and then in the comments let me know which was the best trick and which was the worst. So if you're ready, let's get started.

For our first tip, what we want to do is to take our address, town, and postco columns and we want to combine them together into a single column but have each element on a separate row. So what we could do is come to the transform ribbon and then merge columns within there. We'll say the separator is a comma and we'll call this full address, then click ok.

Next, we would select that address, then go to replace values and we could find the comma and replace it with, then in advanced options replace using special characters and then we could select a line feed and I'll click ok.

Now, it doesn't look like there has been any change. However, when we click on that value, you can see at the bottom that we now have three rows inside that value. And if we were to load this into Excel, if we had wrap text turned on, that is exactly what we would see in Excel too.

Now, the thing is that Power Query has told us that the code that we need for a live feed is hash and then in brackets lf. So what we can do is delete the replace value step and then in the merged column step rather than the comma, we can replace that with our line feed and when we commit that, we get exactly the same result, but we did it in one less step.

For our second tip, we have a table with three columns, product, region, and April 2024. Now the problem is that the change types step has hard coded April 2024 as the column name, which means that when we get to May, June or July, our query will break. So how can we handle this?

Well, let's start by deleting our change types step and then we're going to rename our column manually. I'm going to call that values. You'll see April 2024 is hard coded into the rename columns transformation.

So what we're going to do is to rename our column by position. For that we're going to use table column names open bracket. Now our previous step was called source and then we want to rename our column based on position.

Now, Power Query is 0 based, which means column 1 is 0, column 2 is 1 and column 3 is 2. So in curly brackets, we'll enter the number 2, and when we commit that, you'll see that we've now renamed our column without using its original name.

Now we can select our column detect data types, and there we go. We've now changed our data types without hard coding our column name.

For our third tip, we have connected to a workbook that contains three worksheets, and we can see them here. So, April to June 2024, if we click on the white space in the data column, we can see what each of our worksheets contains.

The problem is, our first worksheet has a column called April 2024, the second worksheet May 2024, and then the third worksheet June 2024, which means if we expand these columns, they will have a separate column for each of those.

When what we want to do is to combine them all into a single column. So how can we rename a column inside a nested table? Well, for that we're going to use some code stealing. So we're going to come to our name column and then from the transform ribbon, let's go to extract, and let's extract first characters and let's just enter one and click ok.

So that gives us the table transform column syntax. This is the function that transforms a column. Now what we want to do is to go back to our previous tip, and you can see in our renamed column step, this is the code that transforms the second column or the third column into the word values.

So I'm going to copy that code, come back to our third tip, and then in this table transform columns, we want to perform this on the data step go on the data column, and we want the output to be a table.

And we're going to paste our previous rename columns transformation. Now, at the time that we created our table rename columns, the previous step was called source. But now because we have a nested table, we want to replace each of those instances of source with an underscore.

And then when we commit that, if we click inside each of our tables, you can see it’s product region and values. And that is the same for each of our columns.

So now when we expand that, I'll click ok. We now have that data stacked one above the other. The previous tip works if the column is always in the same position. But what if it's not in the same position? What can we do in that scenario?

Well, here in Power Query, I've got a separate query called example 4 list, and we have two columns. The old name so for example, April 2024, May 2024, and so on. But we also have a column called new name. This is what we want to rename that column too.

So when we come back to our query and we have our table rename column step, what we want to do is to remove everything in those curly brackets. And then we need to create our list using our list zip function.

So list zip, open bracket. And then within a list, we want to give our old column name and our new column name. So from our example 4 list, we've got old name, then we have a comma, and then our example 4 list and then new name.

Now when we commit that, unfortunately, we get an error. You can see here because the column May 2024 of the table wasn't found. And the next one, it tells us that various columns weren't found. And that's because it's trying to find each of our items.

So it's trying to find April 2024 and rename it to value. However, only one of those tables has April 2024 as a column. So what we need to do is to add an optional parameter into our table rename columns. So we'll enter a comma and we can call this missing field ignore.

And when we commit that, the error is gone. And now all of our tables contain the correct column names.

For tip five, we're working with the same dataset and let's suggest that we want to have April, May and June as separate columns. So what we might do in power query is expand the column. We have April, May, we need to load more data. Then we've got June, we'll click ok, that's now expanded those columns.

But the thing is that our column names have been hard coded inside the table expand table column transformation. Which means if we get any new data for July, August and so on, when we expand those columns, it won't include those columns.

So what we need to do is to use a combination of table combine and table column names. So we'll delete that section there. And then for our first list, we're going to use table column names. So that will give us the column names, but we want the column names of our entire dataset.

So open bracket, table combine, and we want that of our data column. So that will be our source step and our data column, we've got those brackets and now we have got our April, May and June columns, but we haven't hard coded any of those names, which means when we get more data, our query will continue to work.

For our next tip, we have a table with three columns, product, region, and value. And what we want to do is to group by product, showing the total value, but also listing all of the regions that relate to that product. But that list needs to be inside a single cell.

So how can we do that? Well, I'll click on the product column and then from the transform ribbon, I'll click Group by. So we've got our product. And then we'll click Advanced. The first calculation we want, it's going to be called total value.

And this is going to be the sum of the value column. Let's add another aggregation. We'll call this region list. Now, there aren't any text transformations within this list, so we're going to select sum and then select region.

Now, as soon as I click ok, this will create an error because the region list is text. So what we need to do is to find our region list, and that is currently the list do sum transformation. We're going to change that for text do combine.

So we're going to combine our region and we want to combine it with a comma and a space. So when we commit that, we now get a list of all of those regions. However, what you can see is that we have north listed twice. Let's say we just want a distinct list.

So inside text combine, we'll do list distinct open bracket and then close that bracket after region. And when we commit that, you can see that we now just have one instance of each of those regions.

This tip follows on from the previous tip. Let's suggest that our dataset has our values for product, but it contains items which contain lowercase values. That means that when we group those items together, those lowercase items are deemed to be separate items.

But what if we don't want them to be separate items? What if we want uppercase and lowercase to be treated exactly the same? Well, we'll come to our table group step. Before the close bracket, we'll enter a comma.

Now, for the group kind, we want the default value, so I'll enter null. And then for the comparer, if we look at those options, you can see we have comparer, ordinal, ignore case. I'll select that, commit that function, and now it includes all those items, but it ignores case.

So that's how we can group by ignoring case.

I'm Mark from Excel off the grid. That's the place where we teach people how to automate their work using Excel and the key piece of that is Power Query. So if you want to take your Power Query skills to the next level so you can automate Excel and stop working late, then head over to excelthegrid.com and check out our training program.

For our next tip, we're working with a debtors report, which we have our customer name and how much they currently owe us. What we want to do is to add a separate column which contains the date of the report. The problem is the date is here at the top of our report. So how can we add that as a separate column?

Let's find out. We're going to start by removing all of those blank rows. We can then promote the headers and this is the point that we can now add our date. So if we go back at the source step in column one, it was inside row two.

So we'll come back to our last step. Click Add column, custom column. We'll call this date and then for the source step, open bracket column one, close that square bracket and if you remember it was in the second row. So because power query is 0 based, that means it will be 1 and then I'll click OK.

That now gives us that text. So we can select that, go to transform, extract text after delimiter and we can use colon space. Okay, and now we can change that data type to a date. And there we go. We now have a separate date column, even though that date originally appeared in the header of the report.

This next tip is useful if we want to add a custom column, but the formula that we need to write for that custom column is very complex. Let's go take a look here. I've got a reference column and we want to transform this reference column using various rules. I'm going to go to add column and then add a custom column.

Now in here we could start typing our formula as normal. But what we can do is to break this formula down into separate steps. For that we start with the keyword let. And then we could add our first step. So step one equals and then we could enter what step one is.

We enter a comma and then start with the next line. Step two equals and then whatever step two happens to be, enter a comma. We can even use step one and step two inside another step.

So step three equals step one plus step two. Once we've finished all of our calculations, we don't add a final comma, but we can enter the word in. And then after that, we state which step we want to return. So step three.

So that's how we can use it. I've already prepared an example, so I paste that in there and let's call this new reference and you can see that we have let at the start lots of individual steps that then gets us to our in statement and finally we are returning our last step which was called return.

So I click ok and that's how we can add a custom column which has lots of individual steps so we can break down a very complex formula.

Our last tip follows on from our previous tip because we created our custom column. Unfortunately, when we create a custom column, it doesn't have a data type. You can see here it has a type of any. So we'd have to create a new step to change that column into a text data type.

But we can see here it's type text. However, what we can do is rather than changing it inside a change type step, let's delete that step inside the added custom step. What we can do at the end of that formula, before the close bracket, we enter a comma and then we can paste our data type.

And when we commit that, you can see that our new reference is a text data type and we haven't had to add a new step.

And that's it. They were our 10 Power Query tips. Remember to let me know in the comments which one's the best and which one was the worst. Once you've done that, click there to subscribe and click there for more Excel goodness. Thanks for watching and I'll catch you next time.