Hi! Welcome to my channel, Bsmart2gether. I am the
super KT, here to make you a superstar at work. Let us begin our journey today!
This is the fifth video of the zero to one Power Query tutorials.
If you haven't watched other videos from this series yet.
Then, this post has a link attached to it. And I highly recommend you to watch them.
It's a short minutes video. Today, I will show you how to refresh
queries, Error Handling, and Add Column. Let's open the merge and append workbook
we completed from the previous video. You can download it from the attached link down below.
Go to the Data tab, click on "Queries" and Connections".
Ta-dah!!!!!!!!!!!!!!!! Here are all the queries! Look!
How well we grouped them. It is easy to group queries.
Simply select one or more queries, right-click, then select "Move to Group".
You can move to other existing groups or a new group.
Let's switch off the Queries and Connections for now.
There are a few ways to refresh queries. Let me show you.
Go to the Sales Data tab. One of the quickest ways to refresh queries is
Refresh all. It can be time-consuming
if you have a lot of queries and only want to refresh a particular query.
Let me get out of your way and show you another way to refresh queries.
Select somewhere within the table, right-click and select refresh.
You will see "Running background queries" in the bottom left corner".
It means the query is refreshing. It will be gone once the query is refreshed.
Sometimes, unfortunate events happen—part of the data being deleted from the table.
All you need to do is refresh the query.
Here you go! Everything is back! Happy Day!
I want to warn you. Be careful when you change the column name!
Let me show you what do I mean by that. Go to the "City List" worksheet.
Change the "Suburbs" column name to "Suburb". Now go to the Sales Data worksheet and
refresh the query. The Suburbs column
not found! It is one of the key columns we use as a key identifier for data merging.
Click OK. Then, turn on "queries and connection".
There is an error sign next to the query. Double click on the error query.
We are tracing the error and fixing it. Let's start from the last step,
"Expanded City List". Now you get a pop-up saying that there
are errors from previous steps. Click Cancel.
Now go to the second last step, merge query1, and double click on it.
What we merged before has now gone. WHY?
Where has it gone? Click Cancel.
The highlighted error message indicates an error occurred in the city list.
Expand the query pane. You will see that the city list query has an error sign.
Go to the city list query.
There is a "Go to Error" button in the top right corner within the error message box.
Please click on it, and it will take you to the error step.
You will see "Suburbs" in the formula bar. I want you to highlight the tail-end "S" of
"Suburb". Delete it and then hit enter. One problem solved!.
Now, let's go to the last step, capitalised each word.
We instructed Power Query to capitalise each word in the "Suburbs" column during
data transformation. So, we need to update the naming in the formula bar or redo the step.
Let's switch back to the "Merge Sales Data" query now.
Click on the "Go to error" in the top right corner within the error message box.
It will take you to the error step. All we need is to update the naming
like what we did for the City List query. Go to the last step, Expanded City List.
Hooray! No more errors! It is time to load it back to Excel.
Please think twice before you change! Sometimes change can come with pain.
I will show you in future videos how to be the winner!
Use Excel and Power Query in a smarter way. Let me continue to show you how to
handle errors and prevent errors. I want you to unhide the "Price List" worksheet.
Then, import the price is as a table into Power Query.
Let's change the Query Name before we forget it. Then, select the "Price Range Group" column.
You will notice the errors from the Excel formula, imported into excel as errors.
I want to show you to handle this kind of error in three different ways.
Before that, let's load it as a "Connection Only" query.
The first one is the IF ERROR formula.
We use this formula to help us to replace the error with a specific value.
Let's check the result in Power Query. All the errors have now gone.
Let's close the Power Query Editor. Then, undo the excel formula.
If you don't want to write the IF ERROR formula, double click on the "Merge Sales Data" query,
and I will show you the alternative to handle the error.
One of another great features of Power Query is the Detail pane.
Click on one of the errors, and the detailed error information is available in the detail pane.
Select the Price Range Group column. Then, go to the Transform tab,
select Replace values, and then Replace error. From the pop-up, enter N forward-slash A,
then click OK. You get the same
result as per the previous example. Let's undo it by deleting the step then
showing you the third way. Try, Otherwise. It is the IF Error equivalent formula in Power Query,
Go to the Add Column tab, and click on Custom Column.
Apart from standard Power Query functions, you can write formulas through Custom Column.
Let's type in try, space, and select Price Range Group from the right-hand side available
column box. Then click insert. Go back to the formula area,
continue to type space, otherwise, space, double quote N forward-slash A double quote.
Click OK to continue. The concept of this formula is to
get value from the Price Range Group column, and return a specific value, N/A, if it has an error.
You can now use the three error handling methods I showed you to prevent errors.
One last great bonus function I want to show you in this video, IF function. It
is also known as a conditional column. Go to the Add Column tab, and click on
Conditional Column. For New Column Name,
let's name it "IF NA then TBA". Please select "Third method" for the column name.
If the third method column is equal to N/A, then return the value TBA.
If it does not equal not available, please return the value from a specific column.
You need to choose "Select Column" from the dropdown at the "else" section to achieve this.
Then, select Third Method from the next dropdown list.
Now click OK to continue. Here you go! We can load it back to excel.
The conditional column in Power Query is pretty straightforward.
We are almost finished. Let me show you another way to refresh a query. Please select the Merge
Sales Data query, and you can refresh the query by clicking the refresh icon next to the query.
What's wrong? Oh! The file has moved to a new folder!
Click OK Go to the Data tab,
Get Data, and select Data Source Settings. Click on Change Source, and then Browse.
Select the source file from the folder, then click import.
Click OK to continue, then Click Close. Source file location is now updated.
Refresh the query. We did it! Let's summarise our learning today.
We learned multiple ways to refresh queries, troubleshooting in Power Query, error handling,
add columns, and change of data source. Thank you for watching.
Please click subscribe and turn on the notification to avoid missing useful videos.
Click like if you like the video, or feel free to drop a comment for anything excel
challenge you have, and you like to know what is the best way to deal with it.
I will try to create some example videos in future.