How To Automatically Pull In New Report Data Into Excel Using PowerQuery

One common situation most users of Excel face in making recurrent reports -- daily reports, weekly reports, monthly reports etc -- is that of updating the raw data part of the report with the new data for the period they are creating report for.

As simple as this looks, it is often a boring, repetitive and time consuming task. It could mean having to copy and paste data from the new source data files every day. For some people who get data from different sources (branches, sales people, departments etc), that coping and pasting can easily become a 20 mins task with potentials for human errors.

How do you set up your report to automatically pull in new data from source files? PowerQuery is the answer.

And if you are wondering if this will work for data that you get over emails daily/weekly/monthly. Yes, it works for them too.

The major step is to always save the source files in a dedicated folder. Have a folder on your computer than will hold only those source data files. The files can be Excel files or text files (CSV, TSV etc).

Also, ensure that the files have same table column structure -- same table headers arranged in a consistent manner.

With all these done, you are half way.

In demonstrating this, I have a folder where I put in sales data from different branches of a Pizza restaurant. Everyday, I am required to do sales reports, trend analysis and target/KPI dashboards from all these sales data for management. Before, I would copy paste all the source data in a particular sheet on my daily report template. This could take between 5 to 15 mins depending on how tired/refreshed I was and if any interruption that makes me forget where I stopped.

Now with PowerQuery, all those boring aspect of the reports are fully automated away.

Instead of copying and pasting the content of the different source files into Excel, I go to Data menu, Get & Transform, Get Data, From File and From Folder.

Browse to the folder destination and select it.

You'll see all the files in the folder. Select Combine and Load, in the Combine options.

Pick the sheet that's got the data. That sheet name must be consistent across all the source data files.

Wait a couple of seconds while PowerQuery does its magic.

Notice how all the branches data have been pulled in.

And that's not the best part. The whole essence of my proposing PowerQuery is to handle tomorrow's source data and the days after.

So how does it work?

Very easy.

Just replaced the data files in the folder with tomorrow's data files. Then right click anywhere in the combined table data and select Refresh.

What if there's a new branch that just sent me their data after I had done all of these, what should I do to bring in that new branch's data? 

Again, very easy.

Include/save the new branch's data file in the dedicated folder. And click on Refresh in the combined table.

See an example below. 

We launched a new branch in Lokoja and after creating my report for the day, I suddenly got an email of the sales data for that branch and asked to include it in the daily report.

I simply add the branch data file to the dedicated folder and click on Refresh in my already existing combined table.

And that's all!

No more zombie-like copying and pasting.

You can watch the short video demonstration: https://youtu.be/mKEHb0OWuP8

Enjoy!

For all your Excel training needs, you can check us out at https://www.urbizedge.com/Excel or call 01-6311885 or email team@urbizedge.com

Power BI #3: The Query Editor

When you launch the Power BI Desktop, the start up screen you get has Get Data on the top left side.

The Get Data is the your first window into the Query Editor. If you are familiar with Excel, the Query Editor is the exact replica of PowerQuery. 


In Power BI, it is your main data manipulation and data cleaning tool. Once you connect to the data you want to analyse, it is good to go to the Query Editor to examine the data and, if the data needs some cleaning or transformation, do all that transformation in the Query Editor.



Clicking on Edit, when done connecting to the data, takes you to the Query Editor, and I recommend you always use Edit rather than Load which brings in the entire data without allowing for preview and modification/transformation.

Below is what the Query Editor looks like and it always opens as a separate window from the main Power BI window.


The Query Editor can be divided into four functional sections.


1. Menu section
2. Queries section
3. Data Preview section, and
4. Query Settings section.

The Menu section is more like the control panel housing all the tools you will need for most of your data cleaning and data transformation processes. In the end, it is a section you will have to be very proficient at and we will do a lot of practical demonstration of real world analysis that involves using this section.

The Queries section mainly lists all the data sources you are connected to. Right-clicking on any of the data source gives you some very useful set of options.


The Data Preview section shows a preview of the data selected in the Queries section. This gives the Query Editor some advantages over loading the data directly into the Data model, especially in a case of a large data set that would take too many system resources and time to load. By loading just a preview, one can get working immediately on the data and even set filters and formulas to pull in just the segment of the data set that is needed rather than pulling in the entire data set. It also has some useful features — like filter, rename, delete, replace errors and others.


The Query Settings serves as a very interactive and feature-rich audit trail. It allows you to see all the transformation steps carried out in applied order. You can modify any step and re-order the steps if you want.


In future chapters we will do some real world analysis that will help us further dive into the Query Editor and see its practical usefulness.