Combine all files in a Folder

Just joining the blog? Explore prior lessons here.

 

Imagine a folder with 100 journal entry files, and in less than in a minute you can combine them all together in to one report. Power Query makes it very easy to take all the files in a folder and combine the files together to create a single query. 

The biggest challenge is how to modify the query when your report changes. Let's walk through all steps, so this will also serve as a reference guide for when an inevitable change occurs in the future.

---

File types

For this illustration, we are going to use Trial Balances saved as Excel files all in a single folder. Reports which you frequently export from an ERP system and then manipulate for analysis are excellent files to use when trying this out initially.

While you can combine any files, it works best when:

• The layout of the files are similar such as consistent column headers and number of columns.
• The files are all of the same file type (i.e, .xlsx, .csv, .txt); and
• The file layout rarely changes.

If you want to follow along, download these Trial Balances and save in a folder on your network.

                   ↑💾↑                              ↑💾↑

---

Get Data

Let's build a query using the folder where the above downloaded files are saved. First step is to load the data. On the Excel ribbon, go to Data, Get Data, From File, From Folder. Browse to the folder where the files you downloaded above are saved.

Once selected, the navigator screen will display a list of all the files in the folder. Select Combine, then Combine & Transform Data.

Next, Power Query wants to know which file in the folder should be used as the template when combining the files. By default, the Sample File will be the First File in the folder. You can also select the option to exclude any files which have errors.

After selecting OK, Power Query has now taken all the files in the folder and combined them together.

---

The Helper Queries

When a folder is the source for a query, Power Query creates several Helper Queries.

    Parameter1: tells Power Query which file needs to be used as the example. 

    Sample File: grabs the file to be used as the model, or example.

    Transform File: provides instructions on how to convert the Sample to rows & columns.

    Transform Sample File: converts the Sample File in to a query with rows & columns.

    Now let's look at each of these queries to understand more about how they each perform and where challenges may be hidden.

The Parameter Query tells Power Query which file needs to be used as the example. This will always be the "Sample File"; I can't think of a single issue I've ever encountered with the Parameter Query. If something breaks, this is not likely your culprit.

The Sample File Query grabs the file which was chosen to be the example. 

The challenge which I've encountered in the past is when the first file in the folder is no longer representative of what the data should look like, such as when the source updates the file layout (frequently I see this issue with third party files). In this case, filter the Source to select the correct file to use as the template for combining the data.

The Transform File query is actually a custom function (note the fx next to the query name). When the function is ran (called invoked in Power Query), all the steps are performed as defined in the query.

Applied Steps aren't visible in a function query; instead, to view what the query is doing you'll need to select the Advanced Editor from the Home menu to view the M language behind the query. In brief, the code below is saying, transform the Sample File (Parameter1) from the file format in to the Excel Workbook format of a query, which includes columns an rows, using the data from the worksheet Sheet1. When the function is run it creates the query Transform Sample File.

The Transform File query is where you are most likely to encounter an issue. Note above that when the function runs it is looking for a worksheet named "Sheet1". If the Sample file has a different sheet name, the query will fail. To correct, simply open the Advanced Editor and replace "Sheet1" with the appropriate worksheet name from the Sample File.

A similar type of issue can occur when the files in the folder are .csv. Below is what the M language looks like when the files are csv. Notice the "Columns=5" portion of the code; this means that only the first 5 columns of the csv file will be pulled in to Power Query when the function is invoked. If your file format changes and now has 6 columns, you can simply open the Advanced Editor and change "Columns=5" to "Columns=6". This is the most frequent issue I've had with my queries from folders. Bookmarking this tip will save you time in the future.

---

💥BEST PRACTICE TIP

Quickly fix From File from Folder queries by making quick alterations to the queries in the Advanced Editor view. In the Transform File function query, modify the M language for Excel Files by updating the worksheet name, or for csv files update the number of columns.💥

---

The final Helper Query is the Transform Sample File query. This query is generated when the function query, Transform File, is invoked. It brings together all the helper queries to convert the sample in to an Excel workbook type of query.

To sum up what we've discussed:, the Helper Queries are:

    Parameter1: tells Power Query which file needs to be used as the example. 

    Sample File: grabs the file to be used as the model, or example.

    Transform File: provides instructions on how to convert the Sample to rows & columns.

    Transform Sample File: converts the Sample File in to a query with rows & columns.

---

The Converted File

After all the Helper Queries are created, then the final query is created. This query is the end result we were trying to achieve. In this query, you'll perform the normal Power Query transformations such as data clean-up and modifying columns and headers, achieving an end result similar to below, which combines trial balances from multiple dates.

First note that all the Applied Steps in the screenshot below were automatically created by Power Query. The first step is the source, which is the folder file. Then, Power Query filters for any hidden files. Third, it invokes the function query, Transform File, transforming all the files to Excel workbook query types adding a column called Transform File. It then formats the file like the Sample File by renaming the Header Name to Source.File and removing extra columns. Finally, it expands the transformed data and changes the types.

---

Power Query's ability to Combine all files in a folder is revolutionary. It's allowed me to personally create queries and analyze data in ways I never imagined before learning about it. While extremely easy to implement, when it fails, the existence of 4 Helper Queries plus the final query, can make it hard to identify where things go wrong. 

In this lesson, you learned:

• How to Get Data using From File From Folder;
• What each Helper Query does; and
• How to troubleshoot common issues.

SOLUTION

Prior post: Query Dependencies

Next post: Direct Query

Error checking

Just joining? Explore prior lessons here.

If you've played around with Power Query a bit, you may have experienced query errors, where loading a query shows a message such as "4 errors". This post shows an easy way to proactively check for errors before loading.

---

The Data

For this exercise, we will use a simple data set of Actual Sales which contains two columns: Date & Sales. Download the file below, or follow along with a similar workbook of your own.

                                   ↑

---

The Problem

If you look at the dataset, the yellow highlight is a date error, since Excel isn't ready yet for year 20122. 

When the data is loaded in to a Pivot Table, the Pivot isn't going to treat it as a date as shown below.

Similarly when the same data is loaded in to Power Query, and the column is tagged as Data Type "Date", then Power Query is going to treat it as an error. If you don't catch it at the onset, when the query is loaded, you'll note an error message similar to below.

---

The Solution

Identifying the errors proactively, before loading a query, is ideal. To do so, we need to modify the View. Inside the Power Query Editor, on the View tab, there are two options which will provide the error information we seek: "Column quality" and "Column profile".

Column Quality provides three quick facts at the top of each column: they are the percent of data which is Valid, an Error, and Empty. By focusing on the Error metric, it is easy to identify that this query has errors in columns "Date of Sale" and "Start of Month". 

Then you can focus on those columns and identify where in the column the error is occurring. This leads to one of the following decisions which will need to be made:

1. Accept the error, knowing the data won't present appropriately in a Pivot, the Data Model, or a Measure.
2. Remove the errors using Power Query by right clicking on the column header and selecting "Remove Errors", but this impacts data quality and completeness.
   
3. Fix the error at the source. Now that you know where the error is, it will be easier to go back to the source and fix the problem. When you are dealing with very large data sets, finding the problem is half the battle.
4. Fix the error using Power Query. Using transformation tools, such as Replace Values, the error can be corrected before the Changed Type step. 

---

Column Profile

In addition to the Column Quality option, another View which was enabled above was the Column Profile. Where Column Quality stays on for each column, Column Profile only displays information when an individual column header is selected.

When selecting the Date of Sale column, on the left side are statistics about the selected column including row count and number of errors. However, since there is an error in this column, Column Profile doesn't provide any additional information.

Let's contrast this with the Column Profile of a column without errors, such as the Sales column.

When a column has no errors, the Column Profile view is able to provide more information as shown above. The left side has more statistical data such as the number of empty rows, number of distinct or unique values, or the average of all values. On the right side, there's a distribution graph showing the frequency of each value. 

---

One of these two views is sure to be helpful in your Power Query journey.

In this lesson, you learned how to:

• Find errors using Column Quality and Column Profile; and,
• Address errors using four alternative approaches.

SOLUTION 

Prior post: Relationship Problems

Next post: A table is not a Table

Relationship problems

Just joining the blog? Explore prior lessons on Relationships here.

Ah, relationships: they always seem to have at least some problems.

In Power Pivot, the most obvious alert to a relationship problem is when all the values in a Pivot Table are the same. 

Let's walk through a few examples illustrating how things go wrong, so you can spot and solve issues when they occur in your own data sets.

---

The Data

For this exercise, there are two data sets: a Costs Table and a Sign Table. Download the data file by selecting the icon above the arrow below.

                                                                   ↑

Let's explore the data in the Tables. The Sign Table is a Lookup Table for the natural sign based on transaction type. There are the two typical accounting transaction types (debit and credit) plus an additional type, Other (for statistical transactions).

The Costs Table contains 12 accounting transactions; most transactions have a typical Transaction Type of Credit or Debit, but 2 are listed as Total and 1 is listed as Zero.

In native Excel, if you did a VLOOKUP to find the sign for the last transaction with a Transaction Type of Zero, the result would be #N/A.

How will a Power Pivot Table handle the same exercise?

How should the Pivots be set-up to obtain the correct count of transactions by type?

 

---

Lookup Table field as value

On the Pivots tab, 2 Pivot Tables have been built which essentially are trying to gather the same information: what is the count of the type of each transaction?  The first set is pulling in to the rows area Transactions Type from the Costs Table (Data Table). The second set is pulling in Type from the Sign Table (Lookup Table). 

Let's look first at the impact of pulling in to the values area of each Pivot Table the type from the Lookup Table (Type from the Costs Table).

Immediately noticeable is the left Pivot showing the count of Type as 3 in each row. Since Zero occurs only once in the Costs data, how can there be a count of 3? If the Grand Total is 3, how can each row also be 3? 

Power Pivot gives us a hint to why the left Pivot is returning 3 for each Transaction Type. When the Relationships alert shows up, it usually means we need to look at our relationship or in this case, build one.

---

Build the Relationship

The relationship error message isn't extremely helpful; selecting Auto-Detect shows "No new relationships found". Selecting Create opens a box to Create Relationship, but requires understanding which type should be the Foreign and Primary keys. I prefer instead to use the Power Pivot menu.

Let's build the relationship by going to the Power Pivot menu on the ribbon, then select Manage. Inside Power Pivot, select the Diagram View. Remember that the Data Table is the Costs Table and the Lookup Table is the Sign Table. So we'll need to have the Costs Table lookup to the Sign Table. To do so, in the Costs Table, select Transaction Type and drag over to the Sign Table Type field. This connects the two fields establishing a Relationship.

Looking at the Pivot Tables now, you'll note nothing has changed. Further exploration is required.

---

Data Table field as value

In the exercise above, type was pulled in to the Pivots from the Lookup Table, Sign. This returned a value of three which represents the number of types listed in the Lookup Table (Credit, Debit, Other). Each type being represented once, the Pivot on the right provided the correct count of the lookup, but not the count of the transactions by type.

Now let's pull in to each Pivot the Transaction Type from the Costs query.

Both Tables show a Count of 12. The left Pivot shows the count for Zero and Total, whereas the right Pivot only shows Blank, since those labels aren't in the Sign Table. On the surface, it seems like the left Pivot is the correct approach. 

Let's perform one more test.

---

The Final Test

Let's add the Sign to both Pivots.

Now this view highlights an important issue with the left Pivot. While it's not returning a result of 3 in each row of the Pivot, showing 1 as the result is also incorrect. If you were to write Measures off the Table like this, your results would be inaccurate.

Why did this return the wrong result? The cause is because the left Pivot is asking for data from the Lookup Table (Sign) and from the transaction table (Transaction Type). In Power Pivot, Lookup Tables can see in to Data Tables, but Data Tables can't see in to Lookup Tables. Regardless of the rows field, the relationship flows down from the Sign Table to the Costs Table.

When I first started using Power Pivot, I frequently had results where all rows were the incorrectly the same frustrating number. 

---

💥BEST PRACTICE TIP

Always use the columns from the Lookup Table, not the Data Table, as the fields in the Pivot. If the columns from the Data Table are used, the Pivot can not correctly see the Lookup Tables or other Data Tables, and the Pivot Table will return inaccurate results.💥

---

Now that you know the Best Practice Tip, the right approach to building these Pivots was the Pivot on the right. Since the values area includes fields from both the Lookup and Data Tables, the rows (and column) areas can only include fields from the Lookup Table.

In this lesson, you learned:

• Always use the columns from the Lookup Table, not the Data Table, as the fields in the Pivot;
• Always build relationships where the data table lookups to the lookup table; and
• Just because it looks right, doesn't mean it is. Robustly test for accuracy.

SOLUTION 

Prior post: Measures: Right& Wrong

Next post: Error checking

What's killing My Excel?

Across the globe, Excel crashing is an accountant or analyst's bane. I've been there and know how exhausting it can be.  Fortunately (knock on wood), I've found some tips which have virtually eliminated crashing for me.  

• Get 64-bit Excel

• Disable add-ins

• Prioritize Excel

• Check your resource monitor

---

Get 64-bit Excel

The #1 way to fix Excel crashing issues is to install the 64-bit version of Excel on Office 365.  

By default, IT departments across the globe install the 32-bit version of Excel on Office 365.  While that works for most, Excel's 32-bit version is not sufficient for running the more complex Excel workbooks used by finance and accounting teams.  See this Microsoft Office article about why multiple Pivot Tables, slicers and VLOOKUPs can constrain your Excel resources.   

Finance and Accounting users are frequently the largest Excel consumers, but your IT support team may not realize it when deploying PCs to users.  If your Finance and Accounting teams aren't using 64-bit Excel today, there may be some support team resistance to making the change and deploying 64-bit.  Historically, Microsoft Office recommended 32-bit installs by default; however, it did not apply to power users like Finance and Accounting teams.  Here's a link to another Microsoft Office article which might help persuade.

Incapability with existing add-ins, such as Hyperion Essbase, is the primary issue which may need to be addressed. 

• First, I recommend seeing if there is a 64-bit version of your add-in.
• If not, consider installing anyway and testing the impact.
• If impacted, explore with your IT team other solutions, such as a 32-bit Excel version with the add-in installed inside a Citrix environment and the 64-bit Excel version on your desktop.

If you are experiencing install setbacks, don't give up; I assure you the 64-bit version is worth it.  While you wait for IT to validate add-ins, try one of the other solutions below. 

---

Disable Add-Ins

Excel Add-ins sometimes behave badly, especially when the version of Excel the add-in was built on and the current user version of Excel deviate.  This seems to be especially true in the Office 365 environment where updates can occur monthly.

Initially, try disabling all add-ins and turning them on only when needed.  To effectuate, in Excel go to File, Options, Add-ins, Manage, Excel Add-ins, Go.  Uncheck all the add-ins.  Next, repeat these steps, but select Manage, COM Add-ins, Go.

---

💥BEST PRACTICE TIP
Speed up the add-in disabling process by adding a short-cut to your Excel Quick Access Toolbar.  In Excel, go to File, Options, Quick Access Toolbar, Choose commands from: All Commands, Add-ins (the first one, which has no icon & a drop down menu). 💥

---