Refresh

New to this blog? Start the Intro to Power Query series here.

The scenario 

Assume you've just finished the last exercise when the Cash Accountant calls and says, "we are booking another GL entry to account 1235; the GL balance will now be 1,000.99. We'll put the entry in tomorrow."   

You leave for vacation tomorrow, and so you need to deliver the report today.

---

The new data file

Since the GL isn't updated yet, let's manually change the balance for account 1235 by making the change directly in the previously downloaded source file "GL Balance".  Change the balance from 0.99 to 1,000.99.  Then save and close the file. 

---

Refresh

Next, open the file you created from the prior Load Data exercise.  Alternatively, start with the solution file from that exercise. 

On the Data tab, select "Refresh All". 

The GL balance in your query is now refreshed, showing the GL balance for account 1235 as 1,000.99. 

---

Real Data

Use your own data to apply the knowledge learned so far.

Exercise 

Start with a simple dataset.  

Load your file as described in the prior lesson. 

Then replace it with another version of that file.  

Select "Refresh All". 

Some tips for right now:

• Make sure column headers are the same in both files;
• Make sure the column order is the same in both files; and,
• Save the 2nd file in the same location with the same name as the 1st file.

Once you embrace Power Query, last minute changes to your most beastly Excel files will no longer cause you gray hairs and overtime hours.

---

Troubleshooting

⚠ Security Notice

A Microsoft Excel Security Notice may pop-up. The external data source referenced in the notice is Excel connecting to the source files you have saved in your own location.  Choose "OK".

⚠ Error due to open file

If the source file is open when selecting "Refresh All", the refresh may fail resulting in a DataSource.Error that states "the process cannot access the file...because it is being used by another process". Not all versions of Excel permit the source workbook to be open when getting data from an Excel workbook. Close the source file, and select "Refresh All" again. 

---

In this lesson, you learned how to: 

• Refresh a query when data changes,
• Use real data to practice loading and refreshing using Power Query, and
• Troubleshoot the most common errors.

SOLUTION


Prior post: Load data
Next post: Process GL Data

💬 Please share in the comments below how the exercise went for you.