In this section, you will learn to overcome common problems when it comes to importing external data. For example:

Make collaboration easier

When you work with your colleagues on Excel files that collect external data, you will most likely run into "refresh" errors. Unless you're using a common SharePoint drive, each person might have the source file in a different location on their drive. 

This means every time each of you opens the file, you have to go back to the Source step in Power Query and browse for the source file on your drive. 

This can get quite annoying. 

In this section we take a look at 2 alternatives to make collaboration easier.

Import files from a folder which have a mixed letter case and some have trailing spaces

Learn how to use a Power Query function to harmonize the letter case and remove any trailing spaces in the headers of the different files before appending the data.

Append data from multiple sheets but the start of the data is always in a different place

We will use a function to figure out the correct start of the data for each sheet before we append.

Append data from multiple sheets and get the name of the category from a cell outside the data set

Sometimes we might need additional information from each file that's outside the data range. Usually to provide context for each of the data sets that we copy (origin of the data), we can use the file, sheet or table name. 

But what if, we can only get this information from another cell? 

We'll use Power Query formulas to grab the information from an "outside" cell and put it inside our final appended table. 

Let's get started!