Master Your Data: Advanced Power Query Tips and Tricks [Tutorial]

As a technology professional and popular instructor who has spent years wrangling data, Holly French knows firsthand how Power Query has transformed from a "neat Excel add-on" into the absolute backbone of modern data preparation. Whether you are building complex models in Power BI or simply trying to automate your monthly reporting in Excel, the goal is always to create a more streamlined, resilient workflow. 

If you need to take a step a back and learn some Power Query fundamentals, view our How To Create a Query in Excel Power Query tutorial. 

In this deep dive into advanced Power Query techniques, our expert, Holly, shares her decades of expertise on moving beyond the basics.

The Most Efficient Method to Update the Data Sources

You may be going into Power Query to update your data sources but there is a faster method without having to take those extra steps. The fastest way to update your queries is directly from the Get Data button within Excel using the Data Source Settings option.

If you add this option to your Quick Access Toolbar, then you are able to update your data sources even faster! To do this, you can right-click on the Data Source Settings…option and then select Add to Quick Access Toolbar (QAT).

Now the button will be added to your QAT.

Solving the Problems with Connecting to a SharePoint File

It can be challenging connecting to data stored on a SharePoint Site. The biggest challenge is using the correct URL. The correct URL to use is NOT the URL for the SharePoint site or the link using Copy link. The best URL is the direct link to the file in the details within SharePoint. You can get to the details of a file by clicking on the ellipsis (…) and selecting Details.

Within the Details pane, scroll down until you see the Path option. Click on the Path button to copy a direct link to the file.

Within Power Query, you can then select Get Data --> From Other Sources --> From Web…and paste the URL.  

Solving the Problems with Connecting to a SharePoint Folder

It can also be challenging combining data from a SharePoint folder. Here are the steps to do this: 1. Get Data --> From File --> From SharePoint Folder

Enter the URL for the SharePoint site. Everything AFTER sharepoint.com/ needs to be removed.

Click on Transform Data…NOT Combine

Scroll to the end of the Data Set and Filter by the Folder Path

Scroll back to the beginning of the Data Set and click the Combine Files button on the Content Column

Select the data set that you want

Click OK.

Importing from Folder when the Excel Files Have Different Worksheet Names

It is easy to use the Import from Folder option when each workbook in the folder has same worksheet name. However, what if your Excel export files are exported with a different worksheet name every month? There is an advanced solution to make this work:

1. Get Data --> From File --> From Folder and select your folder
2. Click on CombineCombine and Transform Data
3. Select the worksheet from the First file and click OK (Warning: it will produce errors)
4. Click on the Transform Sample File option in the Queries pane
5. Click on the Navigation step in the Query Settings pane
6. In the formula bar on the top of the screen, remove everything between the left square bracket and the word Kind. 
• a. = Source{[Item="Inventory_Transactions",Kind="Sheet"]}[Data] (original code) 
• b. = Source{[Kind="Sheet"]}[Data] (modified code)
7. Click on the Source step in the Query Settings panel
8. Filter the Kind column for Sheet 
9. Click back on your query and the errors should be gone.
10. It should now be combining data from the FIRST worksheet (no matter what it is called) from all workbooks in the folder. 

Changing the Default Settings for Power Query

If you are an advanced Power Query user, you may benefit from changing some of the default settings for Power Query. To get into the settings, go to Get Data --> Query Options…

Some of the options to consider: 

Global --> General…Turn off Query peek (You will no longer see the Pop Up Peek screen when you hover over a query in the Queries and Connections panel). If you ever want to peek at the information, you can right-click on a query in the pane and select “Show the peek”

Global --> General…Turn on Use default table style for query tables (whenever your query loads into Excel it will apply your default table style)

Global --> Data Load…Decide whether you want it to detect column types

Global --> Data Load…Consider Default Query Load Settings 

Excel Power Query Training for Your Team

Mastering Power Query isn't just how to click buttons but understanding the underlying mechanics to make your data work for you. By optimizing your SharePoint connections and streamlining your workspace, you move from being a data processor to a data architect. The goal is always to find the most impactful and logical way to handle information. Whether you are a Microsoft Office Specialist or a casual user looking to save hours of manual work, these small adjustments to your workflow pay massive dividends in accuracy and speed. 

Ready to take your team's data skills to the next level? Explore Ascendient Learning’s Advanced Power Query course. We also have an Introduction to Power Query course, and Excel Power Query in a Day course. Browse our Excel Training and contact us for private, customized training for your team. Any of our courses can be tailored to your group’s needs and business goals.