The CPA Revolution

Hire a PowerBI guru

2023-11-13T15:22:00.000-06:00

I've recently hired a PowerBI analyst to join my team in my full-time industry job. In 3 short months, he's built 3 amazing PowerBI dashboards that have given us tremendous insight in to our accounting data such as the composition of our Journal Entries, our fluctuation (flux) explanations, and our Month End Close Status. It's been a tremendous win.

He started his career as a data engineer, and then taught himself PowerBI from materials like this available online. He's already been awarded our company's high honor quarterly CFO award: the first time someone on my team has won in the last three years. I've been trying to teach accountants how to use these Power tools, but hiring someone who has already made the effort, has been a huge win. I can't wait until the market starts to have more people like him available as they are a key part of my accounting strategy for winning.

So my key takeaway, hire a PowerBI guru if you can. Make the case; they will quickly pay for themselves with their insights (and make you look like a rockstar)!

And if you are the guru already, send me a message, and let's talk. Maybe I have a project or work that you can help me with.

How Do You Learn Best?

2023-06-13T10:12:00.003-05:00

Since building this blog, I've wondered what's the best way to get this information out to the masses of accountants and finance professionals who could really benefit from the automation that PowerBI, Power Query and Power Pivot provide.

In the beginning I tinkered with making some videos and doing live presentations, but I got a lot of feedback that people needed step-by-step instructions and use cases so they could play with the tools and make them their own. However, I wonder what's the best way for you to learn?

Leave me your vote in the comments.

A. Videos posted on this blog, YouTube, or even TikTok (yikes)

B. Live instruction

C. This blog

Prior post: Transforming a PDF

Next post: Coming soon

Transforming a PDF

2023-04-07T01:36:00.009-05:00

For this next exercise, we will build our PowerBI skills further, automating cleaning up the data within the PowerBI application. The post is a continuation of the work that was started in the post, Convert PDF Using PowerBI. In the original exercise, we took a PDF file and converted it to a visualization using PowerBI; now, we will perform transformation work to the PDF file before reloading it to the data visualization.

The Scenario

In this series of exercises, the goal is to pull a list of all names from a PDF. In the first exercise, we just loaded the data as is; however, if you compare the source PDF file to the final visualization, many of the names are missing, with the PowerBI visualization matrix only displaying the names that start from the PDF section Randy Travis.

To begin the transformation process, let's start where we left off in the prior PDF lesson: with the Solution file. Open the Solution PowerBI file (or your prior exercise workbook), and then navigate to the PowerQuery Editor within PowerBI by selecting the query Table025 (Page 11). Right click and select "Edit Query".

Process the data

You've now entered the Power Query Editor: the same one used for Power Query in Excel.

Looking at the data, there's a few things we need to address to get to our goal: a list of all names.

Column2 has a rouge name (Nicole Lopez).
Column1 includes characters (i.e, 2019-10) preceding the name.
Columns 1, 2 & 6 each contain names, but we them all in a single list..

Let's tackle these clean-up items one-by-one.

Addressing the rouge Name in Column2

To fix the name hanging out by itself in Column2, we will combine Column1 and Column2, by adding a Conditional Column. The Conditional Column will be called "1st column" and will look at Column2 and if Column2 has a value of null, then populate [1st column] with the value from Column1. If not null, then it will populate the value from Column2. This is very similar to the manual Excel process of creating a new column and then using an IF statement to populate each row.

ln the PowerBI Power Query Editor ribbon, select Add Column, then Conditional Column,

When the "Add Conditional Column" menu opens, populate the fields as shown in the image below. Remember Power Query is case sensitive so "null" must be lowercase.

After selecting "OK", there will be an extra column named [1st column]. By default, Power Query always adds new columns to the far right of your dataset.

Now, we've got the names from Column1 and Column2 in one column. However, we also have extra data (i.e., 2019-10) in the column that we need to remove. Time for the second cleanup exercise.

Remove digits preceding names

To isolate the name, we need to remove the digits that precede them (i.e., 2019-10). We will use something similar to Excel's Text to Columns: Split Column by Delimiter.

Click on the header for the new column, [1st column], highlighting the entire column. In the ribbon, on the Home menu, select the Split Column option, then By Delimiter.

In the Split Column by Delimiter menu, select Space from the drop down menu, and then Left-most delimiter. This will split [1st column] in to two columns based on the first space it finds, reading left to right. {I love the extra flexibility Split Column offers, such as Left-most, compared to the similar Excel version!}

In the new column [1st column.2], the first few rows look as expected and appear to have achieved the objective. However, as you scroll further down, you can see that "Nicole" is separated from her last name "Lopez". This happened because "Nicole Lopez" didn't have a space in front of it, and so the Left-most Space used to split the column was the space between "Nicole" and "Lopez". There are several approaches we could take to address this issue, but for this exercise we will focus on modifying an existing formula.

Modify Formula for Conditional Column

When we created [1st column], we used the Conditional Column menu to create an if statement that returned Column2's value when Column2 had a value. But, if instead of returning just Column2's value, we returned Column1 + Column2, then the data in Column2 will look the same as Column1. Since the columns look the same after the revision, the Split Column by Delimiter step will work the same, resulting in the correct result for the name of "Nicole Lopez".

If you haven't turned on your formula bar, go to the View menu on the ribbon and check the box "Formula bar". This is a one time set-up.

Now in the Applied Steps area of the Power Query Editor, select the step named "Added Conditional Column".

Selecting an Applied Step changes the Preview to show the data how it looked after that step was performed, but before the subsequent steps. It also updates the formula bar to show the M language formula used to create that step.

Good news is that you don't have to learn the M language in order to make some easy changes using it. In this case, we are going to modify the portion referring to the results of the new Conditional Column. In the formula above, it says if [Column2] is not null then [Column2]. We'll modify the very end of the formula to say "then [Column1]&[Column2]". You can make that change by directly typing in the formula bar above.

An ampersand is used in Power Query to indicate "and", or to join two words together. After pasting in the "[Column1]&" in the formula bar, it should read as follows. Now if we look at the [1st column], all columns have a "2019-##" and a name.

In the [1st column] there is one last issue left to clean-up: there is a space missing on Row 11. Similar to above, we are going to manually type in the instructions to the formula bar. To add text to a column in Power Query, surround texts by quotation marks. Adding a space then is, " "; quotation marks with a space between them. The final modified formula looks like this, and the person listed on Row 11, aligns with the other rows. Even better, you've created the rules once, and if this ever occurs again in the future, the directions are written, and the process will be lightning fast.

Remove other columns

Power Query works faster when you remove any unnecessary columns. Since for this exercise all we need is a final list of names, we can tell Power Query to remove all other columns, leaving only the [1st column.2]. Do so by selecting [1st column.2], right clicking and selecting the option "Remove Other Columns".

Combine columns within the same query

We now have a column that shows all the cleaned-up names from Column1, but we need to add to it the names in Column6. The good news is that the Column6 names are already cleaned up, so no work is required there,

For this trick, I'm going to show you how to append a Table within the same query, combining the new [1st column] with Column6; in plain English, I'm going to show you how to get the new Column1 and Column6 in to a single name column.

Let's start by renaming [1st column.2] to "Column6". This aligns the name of the two columns that contain names: the column we cleaned up and the original column6. The append functions combines columns together based on their headers, so ensure it's spelled exactly the same way including capitalization. Rename a column by double clicking the header.

Next, we will use the append function. In the ribbon, under Home, you'll find Append on the far right. Select the first option, Append Queries. Append makes a current list longer, by lining up data from multiple columns with the same headers.

In the pop-up Append menu, in the Table to append dropdown, select the current query, Table025 (Page 11). This option results in the query appending to itself. Notice in the preview area how the list of names restarts on row 17.

What we need to do next is to change the Append formula manually to look at a version of the query where the original Colunm6 is still included, and then Append that version of the Table to the version that has Column1 renamed as Column6. Essentially, next we need to combine both lists of names from the original columns 1 and 6. Instead of writing the formula from scratch, we used the Power Query menu options to do the hard work, and we will tweak the formula for this scenario. I totally geek out on tricks like these, and how much they expand your capabilities in Power Query, with minimal investment.

If you select on the Applied Step for Appended Query, it will show the formula for that step in the formula bar. Reading the formula from left to right it says "Combine Tables that are located at the Applied Step Renamed Columns with the Table at the same Applied Step Renamed Columns".

To modify the formula, we will replace one of the Renamed Columns references with a reference to an Applied Step that contained Column6. We got rid of Column6 when we performed the step Removed Other Columns; so a Step where Column6 was still in the Table was the Changed Type1 Step.

So while in the Appended Query Applied Step, we are going to replace one of the references to Renamed Columns with the Changed Type1 step. Remember that Power Query is case sensitive, so be sure to spell exactly as shown. The # proceeding the reference indicates that you are referring to a Step in the query, and if a Step is more than one word, quotation marks should surround it. Here's how the final modified formula will look.

Alternatively, you also could have swapped the order and placed Changed Type1 first and Renamed Columns last.

Now that you've manually updated the formula, you'll notice two changes: 1) it's pulled back in all the rows that you removed previously, and 2) Column6 now includes names from both columns of the original table.

Repeat the step to Remove Other Columns, keeping only Column6, and you have a single clean list of names, converted from a PDF to a Power BI query.

Prior post: Convert PDF using PowerBI

Next post: Coming soon

Direct query

2022-11-07T12:25:00.007-06:00

Today, November 7, 2022, I saw that Microsoft has finally launched Direct Query, which allows users to connect to an existing PowerBI dataset. I've been waiting for this feature since I started using PowerBI and PowerQuery in 2018.

It does appear to be in Beta, and so I did have to enable the ability to Preview this feature, but I got prompted as soon as I selected the option to connect to PowerBI Datasets, and the directions were easy to follow.

Since it's brand new, I'm obviously still playing around with it, but was glad to see that my favorite datasets were easy to connect to once the option was selected. I'm really hoping this functionality improves some refresh times. Instead of reconnecting to a source and performing the necessary transformations, I'm optimistic that I'll be able connect directly to the transformed dataset going forward.

Instructions

Ready to get started? Here's a link to Microsoft's instructions for Connecting PowerBI reports to existing PowerBI datasets.

https://learn.microsoft.com/en-us/power-bi/connect-data/desktop-report-lifecycle-datasets

Here's the link to Microsoft's instructions for Connecting Excel to existing PowerBI datasets.

https://learn.microsoft.com/en-us/power-bi/collaborate-share/service-connect-power-bi-datasets-excel

Have you tried Direct Query? Please leave comments on your experience below.

Prior post: Combine All Files in a Folder

Next post: Convert PDF using PowerBI

Convert PDF using PowerBI

2022-09-22T19:49:00.026-05:00

Just joining the blog? Explore prior lessons here.

A PDF file isn't built for complex data analysis, but can hold lots of data we want to analyze. In this exercise, I will show how easy it is to convert a PDF to a data visualization using PowerBI.

The Scenario

You can follow along with this exercise by downloading the source PDF file saved here: PDF.

Let's assume you work in compliance for a large accounting firm in New Mexico. Each quarter, one of your tasks is to go through the NM Accountancy Board Newsletter and pull a list of all people with Disciplinary Reports.

Flipping through the newsletter, you will find the data for this exercise on Page 11.

Choose Data

Haven't downloaded the PowerBI Desktop app yet? Instructions available in this prior post under the header, Install.

Open the application PowerBI Desktop, and let's get started.

Start in the Home menu, then select Get data, scrolling down to the bottom to select More... The More option expands to show more than 100 connections that are already built-in to PowerBI.

At the time of this writing, the PDF option was listed at the top of the All section. If you don't see it quickly, use the Search functionality in the same menu.

Once PDF is selected, then choose Connect.

In the Open menu pop-up, browse to the location where the downloaded PDF has been saved.

Next, the Navigator menu will open, showing a few different options for selecting the data from page 11. Below I've selected the three Page 11 options to highlight them, but we only need to select one to load. To decide which option to select, click on each Page 11 to view the preview.

The option Table025 (Page 11) shows the data that we are after, without the extra text from the top of page 11. So select the option Table025 (Page 11), then Load.

Create Visualization

Now that the data has been loaded, it's time to transform it in to a Visualization.

In Power BI, the Matrix Visualization is a standard menu option, and it resembles a Pivot Table. It's an easy first Visualization type for accountants learning PowerBI, since it aligns with the pivots many of us are so familiar with.

Similar to using Excel's Pivot Table Fields List, in Power BI there are a list of Fields to choose from based on the Data that was loaded above. In this example, since we didn't clean the data before using it, the Column names are generic: Column 5 and Column 6. You can then drag and drop fields in to the Visualizations area, immediately below the area where you select the Matrix option.

After adding the field Column 6 to the Rows area, the Matrix looks like the following, which lists some of the names from one of the columns of the Table we loaded.

While I hope that most of my readers have moved completely away from PDFs, on those occasions where PDFs can't be avoided, I hope this exercise helps transform a manual process to something more automated.

In the next PDF lesson, we'll continue working with this PDF, but explore how to get both columns with names in to the Matrix, using transformation tools in the PowerQuery Editor.

In this lesson, you learned:

How to load data to PowerBI;
How to convert a PDF to a Visualization in PowerBI; and
What a Matrix Visualization is and how to build it.

SOLUTION

Prior post: Combine All Files in a Folder

Next post: Transforming a PDF

Combine all files in a Folder

2021-10-25T09:00:00.094-05:00

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

Query dependencies

2021-08-25T10:00:00.006-05:00

Just joining the blog? Explore prior lessons here.

When you have multiple queries which depend on each other, it's important to understand those connections, especially if you weren't the person who originally wrote the set of queries.

Power Query's Query Dependencies creates a visual map to display how each query is connected to the others.

Query Dependencies

To view Query Dependencies, open the Power Query Editor, and on the View menu select "Query Dependencies" on the far right.

Below is an example of a set of queries. Notice first that the query "Calendar" is not connected to any other query. Then note how the Current Workbook serves as the central source for the remaining displayed queries. The query "Path2" is the foundation for the next queries. If you modified "Path2" it could impact all the queries downstream culminating in to the "Newest Transactions" query which is built from combining all the connected files upstream of it ending in the final query "Working_Budget_Query".

In this lesson, you learned how to:

How to view Query Dependencies; and
How to interpret the query dependency map.

Prior post: A table is not a Table

Next post: Combine all files in a folder, Coming October 25

A table is not a Table

2021-05-25T09:00:00.008-05:00

In this post, we will dig deeper, to discover why an Excel Table is much more powerful than just formatting.

What is a table?

Let's start with some basic definitions.

In Excel, you can freely structure content within cells; cell A3 below is just data.

When you highlight cells A3:C5, the data becomes part of a range.

The range becomes a table when every column has a header (note the lower case "t"), such as A3:C5.

In a table structure, a user might understand that row 3 contains headers, but Excel doesn't know that. Excel also doesn't understand that cell B4 is a Qty of Succulents.

Converting a table to a Table

A table can easily be converted to an Excel Table by selecting the table range and then pressing Ctrl + T. It can also be created from the Home menu by selecting "Format as Table", or from the Insert menu by selecting Table, then Table. The fact that Excel gave us 3 ways to create a Table signals its power.

Once the Table is created, you'll notice cosmetically that the data has been formatted. My default layout changes the rows to blue and white, alternating between each row (i.e, banded rows).

Most importantly, when you select anywhere in the Table, a new Table Design menu becomes available on the ribbon. This is an easy way to confirm that all your data has been picked up in the Table. By using End + arrow keys, you can quickly scroll around the edges of the Table and ensure all data which should be included has been.

What is a Table?

Now that you've created a Table, let's explore some of the key features of a Table.

Structure

An Excel Table is structured. The first row is always the Header row. Everything beneath a header relates to that header; for example, beneath the Product header are types of products.

No cell references

Since Excel understands a Table's structure, you can reference Header names directly when writing formulas. For example, you could write a formula for Total Sales as =[@Qty]*[@[Unit Price]].

In Table formulas, the @ symbol indicates that the formula should be applied @ the current row; it precedes the column name. Column names are in brackets []; if there is a space in the column, two sets of brackets are used, such as in [@[Unit Price]].

This is a significant deviation from the traditional formulas of =B4*C4, but results in much more intuitive formulas which explain what's happening (Qty * Price). When the data is located separate from the formula such as on another worksheet, the efficiency created by using Excel Tables is even more evident.

Formula auto-calculation

When a formula is entered in to a Table, the formula is automatically copied down to the remainder of the Table rows and gets Power-calculated. To me, this is personally the best benefit of using Tables. I work with lots of large data sets containing lookups, SUMIFs, and nested IFs. Copying down a new formula can take a few minutes, or cause Excel to stop responding; but, when using the exact same data formatted as an Excel Table, I rarely ever have issues and formulas take a couple of seconds, no matter how complex.

New rows

When adding a new row to a Table, any formulas automatically copy down. Give it a try by downloading the exercise file below, then copy the additional data and paste in cell A10.

↑

Headers

Since Excel knows the Headers, there's no need to Freeze Panes. When you scroll down in the Table, the Headers float to the top replacing the traditional A:Z column headers.

Ranges defined

Since Excel knows how a Table is structured, as long as your cursor is anywhere inside Table, a few things are easier than ever:

- Sorting: Just select Sort; no need to highlight a range.

- Pivot Tables: Range is automatically the entire range of the Table.

- Power Query: When loading from the current workbook, Power Query needs the data as a Table.

In this lesson, you learned:

The difference between a table and an Excel Table;
How to create a Table; and
The functions of Table including a structure Excel can read, no cell references, formula auto-creation, auto add formulas for new rows, scrolling headers, and a defined named range.

SOLUTION

Prior post: Error Checking

Next post: Query Dependencies

Error checking

2021-04-25T12:00:00.006-05:00

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:

Accept the error, knowing the data won't present appropriately in a Pivot, the Data Model, or a Measure.
Remove the errors using Power Query by right clicking on the column header and selecting "Remove Errors", but this impacts data quality and completeness.
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.
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

2021-03-29T08:00:00.025-05:00

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

Measures: Right & Wrong

2020-08-23T20:00:00.012-05:00

Explore prior lessons on Measures here.

Measures are very powerful custom functions used inside Pivot Tables. However, when I started using them, I found myself getting easily twisted up between when to use SUM vs SUMX.

I built a cheat sheet to help me remember. Hope it helps you too.

The Cheat Sheet

Table1 below has Qty & Price. To calculate Sales, you could add a column to multiple Qty * Price as shown. However, calculating a value for each row uses a lot of memory, especially when you have lots of data.

Measures are a much more efficient way to calculate values for large data sets. There are several ways to write Measures to calculate sales, but only one way which avoids adding the Sales column to the Table above and results in the correct Grand Total of 20.

Note first, in all cases the sales for Item A have been calculated correctly, as well as Item B. The difference is the Grand Total. Adding 11 to 9 is 20, so with this small dataset this is an easy approach to check if a Measure is returning the correct result.

Sum of Sales

Sum of Sales was calculated the traditional way, by pulling the Sales column in to the Values section of the Pivot Table. It achieves the correct result. However, it can't be inserted inside another function such as Item Sales as a % of Total Sales. It also requires having a Sales column in the Table which isn't efficient.

Sales SUM

The next approach does not need a Sales column in the Table. This is a Measure which multiplies Price times Qty. This yields the correct result for the Sales of Items A and B, but the Grand total takes the combined price of Items A & B (11+3=14) times the combined Qty (1+3=4), which equals 14*4=56. This is incorrect as the sum of the sales for A (11) & B (9) is 20.

Sales Sum of Sum

When writing a Measure for the first time, I like starting with something I know will work and then building out from there. The Sales Sum of Sum utilizes the Sales column that was added to Table1. Taking the Sum of the Sales column yields the correct Grand Total. However, having an extra column isn't ideal.

Sales SUMX

SUMX is an iterator: it goes through each row and sums it. First the Sum of the Sales column was calculated, which was 20, then SUMX went through each row of the query and summed the sales. Effectively, this doubled each Item's sales. Using the Sales column in Table1, SUMX summed the total sales in row A & B, essentially doubling total sales. Wrong answer but an iterator has potential.

SUMX Sales

Now that's the right answer! But, it's using the Sales column again. However, it does tell us that SUMX could be used to get to the correct subtotal.

SUMX PQ

Finally, the sweet spot. SUMX PQ has the correct Grandtotal and does not require a Sales column added to the Table. This is the correct way to write a SUMX Measure.

Wrap-up

This cheat sheet shows all the wrong ways to write a Measure. When I tried to write DAX on the fly in the beginning, I've made every one of these errors. Happy DAX-ing!

In this lesson, you learned how to:

Write SUMX Measures the wrong way, and
Write SUMX Measures the right way.

SOLUTION

Prior post: M-language

Next post: Relationship problems

M-language

2020-08-16T02:13:00.006-05:00

Just joining the blog? Explore prior lessons here.

When using Power Query, steps are written using the M-language. Learning a little bit about the M-language can really help expand your Power Query automation skills.

The good news is that most M-language questions can be solved by knowing 3 things.

1. M formulas can be found on Microsoft's website.

2. Steps can be performed first using the tools on the ribbon, and then adding M-language to tweak the final result (see example below).

3. The M-language is case sensitive.

Illustrative example

In this example, the user has a Variance and a Threshold and is trying to determine when a row is above the threshold.

In the Power Query Editor, first create a Conditional Column.

Create a step using the Add Conditional Column prompt. Enter the parameters as shown below to create a column "$ above threshold" which will look at the Variance YOY column. If the value is less than or equal to the value in the Threshold $ column, then the column will have a value of "No" for that row; if not, then the value will be "Yes".

After the column is created, you can look at the M-language in the formula bar. If the bar is hidden, on the ribbon, go to the View menu and check the box for Formula Bar as shown below.

The formula for the conditional column is provided below. The formula can be read as "in the table, add a column, after the Changed Type step. Name the column "$ above threshold". For each row, if the value in the Variance YOY column is less than or equal to the value in the Threshold $, then the value in the new column should be No, else it should be Yes".

Adding to the M-language

What the end user really wants is a column which looks at the absolute value of the Variance YOY and then evaluates if it's above the dollar threshold.

Using the Add Conditional Column prompt, there is no way to add an absolute value evaluation to the formula. However, it can be done in two steps:

Search on Microsoft's website for the M-language for absolute value;
Add the M-language for absolute value to the formula generated using the "Added Conditional Column" prompt.

Search for M-language

On Microsoft's website, in the "filter by title" box starting to type "Absolute" yields a Power Query M function, Number.Abs.

Reading through the description, it is clear Number.Abs is the function we are looking for to modify our existing M-language to calculate the absolute value of the Variance YOY column.

Adding M-language

Now returning to the Power Query Editor, and selecting the step "Added Conditional Column", the formula bar will once again show the function. Modify the formula by typing in Number.Abs (case sensitive) before the column reference for [Variance YOY]. Also surround [Variance YOY] in parens.

M-language with modification

M-language before modification

In this lesson, you learned how to:

Modify an existing M-language formula;
Locate new M formulas to enhance capabilities; and,
Check the case, since the M-language formula is case sensitive.

SOLUTION

Prior post: Calculated columns vs Measures

Next post: Measures: Right & Wrong

Calculated columns vs Measures

2020-05-31T19:08:00.019-05:00

Just joining the blog? Explore prior lessons here.

When playing with the Power tools, there is often more than one way to tackle a task. Read further to explore the pros and cons of calculated columns and Measures.

For this exercise, we'll use the dataset below to find Total Sales for each product using calculated columns and then a Measure.

⇧

Calculated Column

A calculated column expands a dataset by adding an additional column. It's similar to native Excel where you would write a formula in a new column.

New users will feel more comfortable using calculated columns instead of Measures. However, there are some downsides to this approach which we'll explore further down. For now though, let's practice our Power Query skills by adding a calculated column.

Select the Download icon above the arrow ⇧ on the dataset above. With your cursor in the Table, load to Power Query by going to the Data tab and selecting From Table/Range.

Inside the Power Query Editor, on the Add Column tab, select Custom Column.

Create a "Total sales" column like the example below.

After selecting OK, the new Calculated Column has been added to our query.

Change the type of "Total sales" to Decimal Number and then Close & Load To, Table, New worksheet, Add this data to the Data Model, Load.

Advantages of using Power Query instead of native Excel to build the Calculated Column include:

Removes the potential for a formula to get altered accidentally;
Shrinks the file size, since Power Query uses xVelocity to compress data; and,
Allows processing of massive amounts of data since Power Query is limited only by the capacity of your machine, not Excel's ~1 million rows limit: 20 million rows, no problem.

However, it could be even better. Let's try it in Power Pivot.

Build a Power Pivot Pivot Table

Before we write a Measure, let's add a Pivot Table to our workbook, so we can see the results.

Choose to "Use this workbook's Data Model". This creates a Pivot Table using the data loaded in to Power Query.

In the PivotTable Fields, there may be 3 Sales_data sets to choose from when selecting fields for the Pivot.

Hovering over the name yields more information to distinguish between the data sources.

The first is the original Data Table which was loaded to Power Query.
The second is the query that was created in Power Query.
The third is the output of the query.

On the PivotTable Field List, select from the query "Sales_data 1" and pull Product in to the Rows area and "Total sales" in to the Values area.

Measure

Now, let's calculate Total Sales again, but this time using a Measure instead of a calculated column. The results will be the same, but Measures have additional advantages over native Excel and calculated columns.

The Measure will need to multiply Qty and Unit Price for each row of the data and then sum those products to get a grand total. To achieve this result, we will use a SUMX formula, which is an iterator formula, which iterates through each row of the Table and then sums the total.

The SUMX format is

= SUMX(Source Query, function to perform on each row before summing)

In our case, this roughly translates to

=SUMX(Sales_data 1, Qty*Unit Price)

Let's write the Measure.

On the Power Pivot menu, select Measures, New Measure.

Name the Measure, "Total sales Measure".
Start the formula with an equal sign.
SUMX first needs a table to reference, so type an open parenthesis, then 'Sales_data 1' followed by a comma.
Then, start the expression by typing Qty. Select 'Sales_data 1'[Qty], which is the quantity column from the query.
Add a multiplication sign.
Type "price" and then select 'Sales_data 1'[Unit Price].
Close parenthesis.

Check the formula and assign the value a category. Here's the solution.

=SUMX('Sales_data 1','Sales_data 1'[Qty]*'Sales_data 1'[Unit Price])

Return to your Pivot Table, and add in the new Measure.

Advantages of using Power Pivot Measures instead of native Excel or Power Query to build the Total Sales calculation include:

All the advantages listed above for calculated columns versus native Excel plus the following.
Measures use less memory than calculated columns.
The Power Pivot Data Model compresses data 7 to 10 times smaller than the same data in native Excel.

Native Excel uses the in-memory analytics engine to store data in memory (hence why large files with complex formulas or multiple Pivots receive resource error messages).

Measures can be reused in multiple Pivot Tables in the same workbook.
Measures can be referred to in other Measures. For example, if we had to add sales tax of 10%, a Measure could be written to take =Total Sales Measure * 10%.

MeasureNow, let's calculate Total Sales again, but this time using a Measure instead of a calculated column. The results will be the same, but Measures have additional advantages over native Excel and calculated columns.

The SUMX format is

= SUMX(Source Query, function to perform on each row before summing)

In our case, this roughly translates to

=SUMX(Sales_data 1, Qty*Unit Price)

Let's write the Measure.

On the Power Pivot menu, select Measures, New Measure.

Name the Measure, "Total sales Measure".
Start the formula with an equal sign.
SUMX first needs a table to reference, so type an open parenthesis, then 'Sales_data 1' followed by a comma.
Then, start the expression by typing Qty. Select 'Sales_data 1'[Qty], which is the quantity column from the query.
Add a multiplication sign.
Type "price" and then select 'Sales_data 1'[Unit Price].
Close parenthesis.

Check the formula and assign the value a category. Here's the solution.

=SUMX('Sales_data 1','Sales_data 1'[Qty]*'Sales_data 1'[Unit Price])

Return to your Pivot Table, and add in the new Measure.

Advantages of using Power Pivot Measures instead of native Excel or Power Query to build the Total Sales calculation include:

All the advantages listed above for calculated columns versus native Excel plus the following.
Measures use less memory than calculated columns.
The Power Pivot Data Model compresses data 7 to 10 times smaller than the same data in native Excel.

Native Excel uses the in-memory analytics engine to store data in memory (hence why large files with complex formulas or multiple Pivots receive resource error messages).

Measures can be reused in multiple Pivot Tables in the same workbook.
Measures can be referred to in other Measures. For example, if we had to add sales tax of 10%, a Measure could be written to take =Total Sales Measure * 10%.

In this lesson, you learned:

Calculated columns and Measures can return the same results;
The difference between creating a calculated column and a Measure;
The advantages of creating a Power Query calculated column instead of using native Excel; and,
The advantages of creating Measures instead of using native Excel or Power Query.

SOLUTION

Prior post: The Power of the Unpivot
Next post: M-language

The Power of the Unpivot

2019-11-25T10:30:00.008-06:00

Just joining? Explore prior lessons here.

A colleague's jaw literally dropped when I showed him Power Query's Unpivot functionality. Unpivot transformed many days of work in to a data analysis delivered within hours.

The Predicament

Visualize a report similar to the one below, containing 12 months of data.

The initial report provides data in an orderly and logical format, but it's very difficult to take this data and answer analytical questions such as:

What's been the trend in Net sales, current year versus prior year?
How have returns trended as a % of sales?
What does the growth in sales look like in store versus online?
What would next year's monthly sales be if sales online grew 5%?

The Transformation

Transform the flat report to a Pivot Table.

The transformation step-by-step follows.

Load the initial report

Download the initial report by selecting the ↑ below.

↑

To load the initial report to Power Query, start with your cursor inside the Excel Table, Initial_Table. On the Excel ribbon, go to the Data tab and select "From Table/Range".

Process the data

Process the data using Power Query.

Modify the defaults
By default, PQ names the Query the Table name and assigns a Type to each column.

Rename the Query, "Report_data" by selecting the default Name and typing over with the new name.

Delete the Applied Step "Changed Type" by selecting the Red X next to the "Changed Type" step.

Create a Month column
Select Column1 and then on the Transform tab, select Transpose. This flips the Month+Year into the first column of the query.

Select Column1 again and then on the Transform tab, select Fill, Down.

Some quick clean-up

On the Transform or Home tab, select "Use First Row as Headers". When the headers are promoted, PQ applies the Change Type step.

Delete the Applied Step "Changed Type1" by selecting the Red X next to the "Changed Type1" step.

The Unpivot

Compare the query immediately before and after performing the Unpivot function.

Before

After

Unpivot took the column headers (with the exception of Column1 and Column2) and flipped them in to a new Attribute column, placing the amounts in to a single Value column. Now, each row represents a single Value with a unique set of characteristics.

To perform the Unpivot, select Column1 and Column2. On the Transform tab, select "Unpivot Other Columns".

Polish the Presentation

Polish the query and enhance its analytical usefulness with these steps.

Split the Month+Year in to separate columns. Select Column1. On the Transform tab, select Split Column, By Delimiter, Space & Left Most.
Rename the columns to Month, Year, Scenario, Sales type, and Amount.
Change the data type of the Amount column to Decimal Number.
Filter out the Sales type of "Net sales".

Close & Load To a Pivot Table, or as a Connection Only, Add to the Data Model.

Analyze

Use a Pivot Table (and measures) to analyze the data.

In this lesson, you learned how to:

Change the layout of a query by unpivoting columns (flipping columns to rows);

Convert a flat table layout to a layout where each row represents a single value with a unique set of characteristics.

Transform data using Fill Down and "Use First Row as Headers";

SOLUTION

Prior post: Distinguishing datasets in Power Pivot
Next post: Calculated Columns vs Measures

Distinguishing datasets in Power Pivot

2019-09-30T15:35:00.022-05:00

Just joining? Explore prior lessons here.

When building a Pivot Table using Power Pivot, a key advantage is the ability to combine multiple datasets in one Pivot Table.

However, it can be very easy to end up with multiple datasets with virtually the same name. This can result in a user selecting the wrong dataset.

Let's explore the three similarly named datasets shown above.

The Data Model

This scenario started with an Excel Table named Sales_data.

Then, the Excel Table was loaded to Power Query, creating a query named Sales_data.

Once work was complete in Power Query, the query was Closed & Loaded as a Table and added to the Data Model. The output Table by default was named Sales_data_2.

A Power Pivot Table

Next, a Pivot Table was created using the Data Model.

The result is there are now 3 datasets to choose from when building a Pivot Table. This occurred because the Table, query, and output query are all part of the Data Model (the source for a Power Pivot Table).

Which one is which?

The first dataset has an icon which resembles an Excel Table with a database icon. Hovering over the first dataset name tells us the source for this dataset is "Query - Sales_data". This is the database query which was created in Power Query. When building a Power Pivot Table, use the dataset sourced from the query; it is differentiated by the database icon.

While we won't pull the second and third datasets in to a Pivot Table, it is important to understand what these datasets represent. Hovering over the second dataset tells us this Sales_data's source was a Table, named Sales_data, which can be found on the Sales tab in cells A1:C7. This is the original Excel Table.

Hovering over the final dataset reveals the source as the Table, Sales_data_2, which is on the Query Output tab in cells A1:D7. This is the Table which was the output of the query when Power Query was Closed & Loaded To.

Hiding "extra" datasets

Now that you know which dataset to choose, prevent future errors by hiding the other datasets from yourself and others.

On the Power Pivot tab, select Manage. In the Power Pivot data view, Microsoft decided to make things really confusing by renaming the 2nd dataset to Sales_data1. Next steps, we are looking to keep only Sales_data visible in the Pivot Table fields.

Right click on the tab named Sales_data1. Select the option "Hide from Client Tools".

Repeat with the tab Sales_data2. Then close.

Now only the query dataset is visible.

Prevention

Above we identified three sources of datasets: the input Excel Table (or source), the query created from the source, and the output Excel Table created from the query. However, the dataset sourced from the query is almost always the only one we need.

You can avoid two of three scenarios from occurring by altering the input and the output:

Change the input. Instead of starting in the workbook which contains the Sales_data table, create a new workbook. Then, load the table to Power Query From File, From Workbook. This creates the query, but eliminates the dataset created from the Excel Table, or source.
Change the output. When you Close & Load the query, load to a Pivot Table or Connection Only. This eliminates the dataset created from the output Excel Table created from the query.

In this lesson, you learned how to:

Identify a dataset created from a Power Query query;
Distinguish between datasets created from Excel Tables, from queries, and from the Close & Load as a Table process;
Hide datasets which aren't needed; and,
Prevent multiple versions of similar datasets from showing in a Pivot Table.

SOLUTION

Prior post: What's Killing My Excel?
Next post: The Power of Unpivot

What's killing My Excel?

2019-08-25T17:00:00.013-05:00

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). 💥

When Excel crashes, keep track of which add-ins, if any, were enabled. Once an add-in has been identified as a culprit, work with your IT team to address. In the meantime, whenever you need to enable the suspect add-in, save backup copies often.

If Excel crashes at the same frequency when the add-ins are disabled, then add-ins is likely not your primary culprit; read further and see if one of the other solutions helps.

Prioritize Excel

All running applications share processor time. Tell your computer to give Excel more love by changing the priority level.

Use Ctrl+Alt+Del to open Task Manager. On the Details tab, right click on Excel.exe process. Choose Set Priority. Then, select either High or Above Normal.

The priority auto-resets to Normal whenever Excel is restarted making this a temporary solution at best.

While it may be tempting to choose Real Time, rumor has it that this will make your other applications jealous, your CPU's head spin, and your PC crash. 💔

Check your resource monitor

Return to Task Master. On the bottom of the Performance tab, select "Open Resource Monitor". This tool provides utilization visuals for the processes running on your machine. Choose the Overview tab and keep the 4 charts open and visible while you work. When the Resource Monitor charts are at their maximums for an extended period of time, drill down on the applicable Resource Monitor tab to try and isolate what is dragging you down. With data in hand, partner with IT support to achieve improved performance.

In this lesson, you learned how to eliminate Excel crashes by:

Installing 64-bit Excel,

Disabling add-ins,

Prioritizing Excel, and

Checking your resource monitor.

Prior post: Measures Inside Measures
Next post: Distinguishing datasets in Power Pivot

💬Always love hearing how others have tackled Excel performance issues. Drop a comment below to share.

Measures inside Measures

2019-06-14T07:00:00.007-05:00

This is the final post of the Intro to Power Pivot series. Jump to the 1st post of the series here.

It's time to wrap up the Intro to Power Pivot series. This post will work through writing the final three Measures (Var to Budget, Prior Year, and Var to PY).

Budget variance

Let's calculate the variance between Actual and Budget by month.

Start with the workbook created from the last exercise, Measures In-depth, or download this file.
With your cursor in the Pivot Table, go to the Power Pivot menu, and select Measures, New Measure.

In the Measure dialog box, select the Table, Actual_Data.
Change the Measure name to Var to Budget.
In the formula box, type =[Actual] - [Budget].

Start typing Actual, and you will get a drop down box to select from.
Measures have a 𝚺 sign next to them and are noted in brackets.
Press Tab to select.

Check formula. Result should be no errors.
Format by changing the Category to Number, with 0 decimal places.
Ok.

Prior Year Measure using CALCULATE

Let's create a Prior Year Measure to show the 2012 Actual values. The formula for creating that Measure is as follows:

= CALCULATE ( [Actual] , Date_LU[Year] = 2012 )

The CALCULATE function can filter a Measure. The CALCULATE function requires first, the Measure, and then the filter. The Measure is [Actual]. The filter is the 2012 year, which is pulling from the Year column of the Lookup Table, Date_LU. Columns and Measures are in brackets.

Notice how the Prior Year column is showing the 2012 Actual numbers for both 2012 and 2013. Using CALCULATE you've told Excel to always show the 2012 actual value regardless of the year presented in the Pivot Table. This is referred to as filter context and occurs because CALCULATE filters take precedent over Pivot Table filters.

💥BEST PRACTICE TIP
CALCULATE is a very powerful function. Always test that the results are as expected. If others in your organization aren't using Power Pivot yet, consider adding a comment to alert the user to the CALCULATE function. 💥

Pivot

Let's do some quick clean-up before moving on to our final Measure.

In the PivotTable Fields List, move the Year field from Columns to Filters. Filter to show only 2013.
In the Values area, move Prior Year to the top, so it is the first column in the Pivot.

Variance to Prior Year Measure

For our final Measure, let's write the variance to the prior year.

Below is a screenshot of the final result.

By the time you wrote your 5th and final Measure for this Intro to Power Pivot series, hopefully, you were starting to get the flow for Measures. Keep practicing, replacing your normal SUM of Values with Measures and trying out a few CALCULATE exercises. Pretty soon Power Pivot will be second nature.

In this lesson, you learned how to:

Write Measures which reference other Measures;
Write Measures to take the difference between columns in a Pivot Table; and,
Use the CALCULATE formula to create filtered values.

SOLUTION

Prior post: Measures In-Depth
Next post: What's killing my Excel?

Measures in-depth

2019-06-12T11:31:00.007-05:00

This is the 7th post of the Intro to Power Pivot series. Jump to the 1st post of the series here.

Below is the Pivot Table from the last exercise. Let's create the Measure, Actual, and polish up the Table. Then, let's talk more in-depth about Measures.

Writing the Actual Measure

Start with the workbook created from the last exercise, Measures, or download this file.

With your cursor in the Pivot Table, go to the Power Pivot menu, and select Measures, New Measure.

In the Measure dialog box, select the Table, Actual_Data.
Change the Measure name to Actual.
In the formula box, type =SUM(Actual_Data[Sales]).
Check formula. Result should be no errors.
Format by changing the Category to Number, with 0 decimal places.
Ok.

Your Pivot Table now contains the new Measure "Actual". You can compare to the Sum of Sales values you are accustomed to seeing and confirm the Measure resulted in the same output (just formatted).

In the Values area of the PivotTable Fields List, remove both the Sum of Sales metrics. Below is a snapshot of the Pivot Table, which now contains the two Measures, Budget and Actual.

Measures In-depth

With the introduction of Measures, you've unleashed a tremendous amount of calculating power, which hasn't yet been explained. Let's revisit each element of the Measures dialog box to start digging deeper.

Table Name

The Table Name selected determines where a Measure is located on the Fields section of the PivotTable Fields List. Note the Actual and Budget Measures in the image below.

Some people recommend placing Measures on the queries where the related data sits, which was the structure selected for this exercise. Selecting the queries to assign a Measure to is a matter of personal preference. The Measure will return the same result regardless of the query assigned.

Measure Name

The Measure Name will be the default displayed name on the Pivot Table, as Budget and Actual are in our exercise. Like a normal Pivot Table, the displayed name can be changed in Value Field Settings. In the example below, the Measure Name would be "Actual" and the displayed name on the Pivot would be "Actual Sales".

Name your Measures something intuitive to make it clear what the Measure is calculating. As your Data Models start to have multiple Measures, such as Actual Sales and Actual Orders, it will be helpful to easily distinguish between the two. In our Data Model, Actual Sales and Budgeted Sales would have been more robust Measure names than Actual and Budget.

💥BEST PRACTICE TIP
Create robust Measure names. Think about possible future uses for your Data Model. Changing a Measure name later can cause headaches. A renamed Measure is removed from any existing Pivot Tables and requires modifying any dependent Measures. 💥

Formula

The formula area is the meat of the Measure and where the DAX language comes in to play. Measures are calculations which display in the Values area of a Pivot Table. As a calculation, Measures always start with an equal sign.

=SUM(Actual_Data[Sales])

The next element is the function. There are more than 200 DAX functions, many which resemble functions in native Excel, but give more Power to your analytics.

In our exercise, we used the function SUM. SUM adds up the amounts in a specified column. All functions are followed by an open and closed parenthesis: =SUM().

For the SUM function, we specified the column as Actual_Data[Sales] surrounded by open and closed parenthesis. Since Data Models can contain multiple tables (queries) which can have the same column names (such as Sales), when specifying a column, include the table name and the column name. Our table name was Actual_Data, and the column we are summing from that table is Sales. Columns are referenced in brackets.

At this point, we've comparatively done a lot of work to mimic the "Sum of Sales" value normally included in a Pivot Table. This is a simple example, designed to mimic something you are likely very familiar with to lay the foundation for more complex Measures and analysis.

As an additional foundational exercise, I recommend reviewing the official documentation for a formula: Microsoft link. As you venture on your own, understanding how non-accountants reference the elements of a function enhances collaboration.

Check formula

After writing the formula, Always press "Check formula". The green checkmark and "No errors in formula" message confirm the formula is written correctly.

A very common error message is the following, unhelpful message.

When the above message is received, check to make sure there is a closed parenthesis at the end of the formula.

Using Tab and the drop down menus can assist with dropping in the function and column names correctly. Unlike Power Query, DAX is not case sensitive.

Format

When viewing the Measures "Budget" and "Actual" in your Pivot Table, you may have noticed they were formatted with a comma and no decimals.

This was based on the format settings listed in the Measure dialog box. Each time a Measure is used, the format is set and ready to go.

In this lesson, you:

Practiced writing Measures;
Compared Measures to the traditional approach of adding Values to a Pivot;
Explored each component of the Measure dialog box;
Learned how to write robust Measure names; and,
Resolved common Measure errors.

There's a lot more to talk about regarding Measures. Subscribe below to be alerted when the next post is ready.

SOLUTION

Prior post: Measures
Next post: Measures inside Measures

💬 Challenge exercise:
Load the Actual_Data query in to PowerBI, and write the Actual Measure, =SUM(Actual_Data[Sales]).

Measures

2019-06-07T07:00:00.005-05:00

This is the 1st post on Measures and the 6th post of the Intro to Power Pivot series. Jump to the 1st post of Power Pivot series here.

Measures allow the creation of custom formulas to harness big data using Power Pivot. Measures use a formula language called Data Analysis Expressions (DAX). Many DAX formulas are similar to native Excel, such as SUM, but they extract the value from a table, column, or subset of your data, instead of referencing cell ranges, and they can be used across your Data Model.

Writing a Measure

Start with the workbook created from the last exercise, 2 Data Tables on 1 Pivot Table, or download this file.

The Pivot Table in the file currently has two values named Sum of Sales, which is not helpful. Using Measures, let's create more meaningful descriptors, such as Budget and Actual.

With your cursor in the Pivot Table, go to the Power Pivot menu, and select Measures, New Measure.

In the Measure dialog box, select Budget_Data as the Table name.
Change the Measure name to Budget.
In the formula box, type =SUM(Budget_Data[Sales]).
Change the Category to Number, with 0 decimal places.

The Pivot Table now contains the new Measure "Budget".

Learning Curves

Not everyone got that result on the first try. Let's talk about a few things that could have thrown you a curve, and how to fix them.

Measure not on Pivot Table

If the Budget Measure is not on the Pivot Table at all, it's because the cursor was not in the Pivot Table when New Measure was selected from the Power Pivot ribbon. Go to the Pivot Table Fields List, and locate the fx Budget checkbox. Check the box, and the new Measure is added to the Pivot.

Measure in different section of Fields List

If the new Measure is not listed under Budget_Data, in the Fields List search box, type Budget. Check the box to add the Measure to the Pivot. This occurs when the Table Name used in the Measure was something other than Budget_Data.

Measure Name or Formatting is incorrect

If the Measure is on the Pivot Table, but it's not named Budget or formatted with a comma and no decimals, then the Measure needs to be modified by reopening the Measure dialog box. On the Power Pivot menu, select Measures, Manage Measures and modify to match the image above.

In this lesson, you learned:

What a Measure is;
How to write a Measure; and,
Troubleshooting for common Measure problems.

SOLUTION

Prior post: 2 Data Tables on 1 Pivot Table
Next post: Measures In-depth

2 Data Tables on 1 Pivot Table

2019-06-06T07:00:00.007-05:00

This is the 5th post of the Intro to Power Pivot series. Jump to the 1st post of the series here.

This lesson walks through the steps to load the 2^nd Data Table (Actual), build a relationship to the Lookup Table, and add actual sales to the budgeted sales Pivot Table.

Load the Actual Data Table

Start with the workbook created from the last exercise, Building Relationships, or download this file.

Load the Actual data to the exercise workbook by performing the following steps:

Download the Actual file by selecting the Download icon below.

↑

In the exercise workbook, go to the Data tab, Get Data, From File, From Workbook.
Browse to select the downloaded Actual file and Import.
On the Navigator, select the Table, then Transform Data (or Edit).
Rename the Query, Actual_Data.

Establish a common field

The Actual file lists actual sales by day in the format MM/DD/YYYY, The Date Lookup Table is rolled up to the monthly level. Therefore, we need to create a column in the Actual query that we can build a relationship with.

Choose the Date of Sale column, and then on the Add Column tab, select Date, Month, Start of Month.

The query is now loaded and transformed. Go to the Close & Load drop down menu, Close & Load To, Only Create Connection, Add this data to the Data Model and select Ok.

Build a relationship

In the Power Pivot menu, select Manage to open Power Pivot. On the Home tab, select Diagram View.

Rearrange the Actual_Data query so the Data Table looks up to the Lookup Table.

On the Actual_Data box, select the Start of Month and drag up to the Start of Month on the Date_LU.

The connector should indicate a 1 next to the Date_LU Table, since this Table holds unique values.

Close Power Pivot.

💥BEST PRACTICE TIP
Always connect the Data Table to the Lookup Table. Do not connect Data Tables to each other.💥

Pivot Budget and Actual on 1 Pivot Table

In the Pivot Table Field List, there are now 3 queries to choose from including Actual_Data.

Add Sales from the Actual_Data query to the Pivot Table Value section. Remove from Rows the Period field from the Budget_Data query. Also, from the Date_LU remove from Rows the Start of Month and Month, and add to Columns the Year.

With the cursor in the Month Name column of the Pivot Table, right click and Sort by Ascending.

It's not polished yet, but the goal has been achieved. The Pivot Table reflects 2 Data Tables and two different sales figures: budget and actual.

In this lesson, you learned how to:

Handle relationships when there are two Data Tables; and
Pivot 3 data sets on 1 Pivot Table.

SOLUTION

Prior post: Building relationships
Next post: Measures

Building relationships

2019-06-05T07:00:00.009-05:00

This is the 4th post of the Intro to Power Pivot series. Jump to the 1st post of the series here.

In this lesson, you will load the 1st Data Table, build the 1st relationship, and create a Pivot Table using both the Lookup and the 1st Data Table.

Load the Budget Data

Open the workbook you created in the last exercise, Power Pivot Date Table, or download the file here.

Load the budget data to the workbook by performing the following steps:

Download the budget file below.
In the exercise workbook, go to the Data tab, Get Data, From File, From Workbook.
Browse to select the downloaded budget file and Import.
On the Navigator, select the Budget Table, then Transform Data (or Edit).
Rename the Query, Budget_Data.
Close & Load drop down menu, Close & Load To, Only Create Connection, Add this data to the Data Model. Ok.

↑

First Look at Power Pivot

We've loaded two files to our Data Model. It's time to move from Power Query to Power Pivot.

In the Power Pivot menu, select Manage. This opens Power Pivot. Alternatively, you can also access Power Pivot by selecting the smaller version of the Manage icon, in the Data Tools section of the Data menu.

When Power Pivot opens, the initial view is called the Data view. In this view, you will see on separate tabs each of the queries loaded to the Data Model.

Think of this as a read only view, as the original queries can only be modified at the source or in Power Query.

On the Home tab, select Diagram View.

The diagram view shows the column names of each query and is a place to visualize how your queries relate to each other.

First, let's rearrange the queries so the Data Table looks up to the Lookup Table. Rearrange by dragging the Budget_Data box under the Date_LU box. I call this the Golden rule of Power Pivot; it's more commonly referred to as the Collie method, for the creator, Rob Collie of PowerPivotPro.com. Visualize how in the image below the Data Table looks up to the Lookup Table.

💥BEST PRACTICE TIP
Layout your Data Model, so the Data Tables look up to the Lookup Tables.💥

Build a relationship

Next, we are going to connect the queries together based on a common field.

For this Data Model, the Date_LU query column MMYYY equates to the Period column in the Budget_Data query.

To connect the two columns, select Period, and while holding down the left mouse button, drag your cursor to MMYYYY. A line illustrates the connection being built between Period and MMYYYY.

After the two queries are connected, a connector is displayed.

The connector tells us several important things:

Placing your cursor on the connector highlights which columns the connections were made on.
The downward direction of the arrow on the connector tells us the Lookup Table is flowing the value down to the Data Table.
The "1" indicates that the values in the MMYYYY column are unique (no two values are the same). The * indicates that the values in the Period column can be any value. This is referred to as a "One-to-Many Relationship" and is a key concept in building Data Models with Power Pivot.

Building a relationship vs. using VLOOKUP

In a traditional VLOOKUP, you would lookup the data from the Lookup Table and add it to the Data Table. Using Power Pivot, you tell Excel where to look for the value but don't copy the value to the Data Table. This saves enormous resources and dramatically decreases file size and improves calculation speeds.

A Pivot Table with 2 tables

Now that the relationship is built, close Power Pivot.

Insert a Pivot Table, selecting "Use this workbook's Data Model".

Great news! Power Pivot uses the same Pivot Table that you already know and love. Everything looks the same as it did before, with one exception. In the Pivot Table Field List, there are now multiple data sets available to choose from. I get shivers thinking about how this simple change dramatically transformed my work and improved my efficiency.

From the Budget_Data query, place Period in Rows and Sales in the Values area of the Pivot.

Previously, this Pivot Table yields all the data we would have been able to extract from the budget file. If we wanted to add, Year, Month, and Month Name we would need to add 3 new columns to the budget file (and repeat each time we received a new file from the budget team).

Let's look at how the world has changed. In the Pivot Table Fields List, add Start of Month, Month and Month Name to rows.

If you do nothing else in Power Pivot, when your Lookup Table has unique values, load your files and build the relationships to remove VLOOKUPs. You will find immediate improvement in the performance of your files.

The Power Pivot Data Model compresses data 7 to 10 times smaller than the same data in native Excel. Native Excel uses the in-memory analytics engine to store data in memory (hence why large files with complex formulas or multiple Pivots receive resource error messages).

In this lesson, you learned how to:

Navigate in Power Pivot;
Build relationships using Power Pivot;
Interpret the Connector in Power Pivot;
Create 1 Pivot Table using 2 data sets; and,
The value of performing calculations, such as VLOOKUP, in Power Pivot instead of native Excel.

SOLUTION

Prior post: Power Pivot Date Table
Next post: 2 Data Tables on 1 Pivot Table

💬 Questions or challenges can always be addressed in the comment section below.

Power Pivot: Date Table

2019-06-04T07:00:00.007-05:00

This is the 3rd post of the Intro to Power Pivot series. Jump to the 1st post of the series here.

A date seems to rarely be written the same way when derived from different teams or source systems. In our Intro to Power Pivot series, the budget file uses a MMYYY format, and the actual file uses a MM/DD/YYYY format.

Creating a standard Date Table is a helpful tool for using Power Pivot. The file created in this lesson can be used for this series and as a calendar Lookup Table on your future Power Pivot models.

Load Lookup Table

Make a Date Table that lists each month for 2 years using both the MMYYYY and MM/DD/YYYY format and starting with 1/1/2012; or download the file below. Format as Table. Save and close the workbook.

⬆

In Excel, start a new workbook.
Load the Date Table to Power Query, by going to Data, Get Data, From File, From Workbook.
Select the location where you saved the Budget file downloaded above. Import.

On the Navigator tab, select the table and then Transform Data (or Edit) to open the Power Query Editor

[Yep, Power Query still plays a pivotal role in Power Pivot. If you missed those lessons, start here]

Process Lookup Table

Since this will be a Lookup (LU) Table, rename the query Date_LU.

Notice that Power Query applied the step "Changed Type".

Power Query correctly assigned the data type of Date to the Start of the Month column. However, for the MMYYYY column, the leading zeroes were lost. Change the type to Text, which will retain the leading zeroes.

When selecting to change the data type, Power Query will prompt with the alert below. Select "Replace current".

Next, we are going to expand the Date Table by adding in Year, Month, Month Name, and an Index Column.

With your cursor in the Start of Month column, go to the Add Column tab, Date, Year, Year. This adds a new column with the year.

Move your cursor back to the Start of Month column, and go to the Add Column tab, Date, Month, Month. This adds a new column with the month number.

Move your cursor back to the Start of Month column, and let's add the Month Name. Go to the Add Column tab, Date, Month, Name of Month.

Next, let's sort the Table. Select the Start of Month column, and on the Home tab, select the Ascending sort button.

Now let's add an Index Column. On the Add Column menu, select Index Column, From 1.

On the Home tab, Close & Load, Close & Load To. Then select, Only Create Connection and check the box to "Add this data to the Data Model".

Save the workbook.

💥BEST PRACTICE TIP

For your future Data Models, save this workbook to use as a Date Table template. 💥

In this lesson, you learned how to:

Create a calendar Lookup Table;
Add Year, Month, and Name of Month columns to a query;
Sort a column; and,
Create an Index column.

SOLUTION

Prior post: Installing Power Pivot
Next post: Building relationships

2 files, 1 Pivot Table: Power Pivot

2019-06-03T07:00:00.012-05:00

New to this blog? Start here for an Intro to Power Query.

What if you could take 2 (or 20) files and analyze them in a single Pivot Table?

Power Pivot provides the ability to analyze multiple files together in one Pivot Table along with additional data analytic tools and reporting functionalities. And, Power Pivot, operates within the Excel and Pivot Tables you already know and love.

Intro to Power Pivot series

From this Intro to Power Pivot series, I hope you will become as enamored with Power Pivot as I have.

The scenario for this exercise series will feel familiar to most accountants and analysts: there is a budget file and an actual file, which use different formats, and you need a report showing the variance by month.

At the end of the series, you will be able to produce the following Pivot Table.

The series will teach the 5 core steps to robust Data Models using Power Pivot:

These 5 core steps are covered over several posts as follows:

Installing Power Pivot
Creating a Date Table [load Lookup Table]
Building relationships [load Data Table][build relationship][Pivot]
2 Data Tables on 1 Pivot Table
Measures [write Measures]

Select the Next post link below to get started.

Prior post: Joins illustration
Next post: Installing Power Pivot

💬 Already using Power Pivot? What advice do you have for new users? Comment below.

Installing Power Pivot

2019-06-03T07:00:00.009-05:00

Using Excel 2013 or later? Power Pivot is an Excel add-in.

To enable, in the Excel ribbon, go to File, Options, Add-Ins, and select Microsoft Power Pivot for Excel.
Click Go.
In the pop-up window, select Microsoft Power Pivot and then Enable. If Microsoft Power Pivot is not an option in the pop-up window, close the pop-up window and select Ok.

Back in Excel, on the ribbon, you should now have a new Power Pivot tab. If so, you are all set and ready for the next lesson.

If the Power Pivot tab is not on the ribbon, on the Data tab, find the green data model icon under Data Tools, as pictured below. When hovered over, it should say "Go to the Power Pivot Window". A pop-up will ask if you want to enable data analysis tools. Agree. You should now have the Power Pivot tab on the ribbon and are ready to move on to the next lesson.

Since Office 365 updates frequently, differences may exist. For additional troubleshooting, try this link to Microsoft installation instructions.

Using Excel 2010? You will need to download the Add-in from Microsoft. Follow this link.

Excel iOS and Online? Power Pivot tables can be viewed in these Excel versions: no installation required. However, changes can't be made to the underlying data model in these versions.

Excel for Mac and Android? Microsoft does not support Power Pivot on these platforms. Share your voice on the Microsoft user forum.☹️

Prior post: 2 files, 1 Pivot Table: Power Pivot
Next post: Power Pivot Date Table

Joins illustration

2019-06-01T07:00:00.003-05:00

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

This is the 2^nd of 2 posts on joins and is a complement to the Merge post. Link to 1^st post.

In this exercise, we move beyond the conceptual and work through an exercise using 2 data tables to demonstrate all 6 types of joins.

Data table 1, Account, contains a dollar value of $50.00.

Data table 2, EEID, can be matched to Data table 1 based on Company. However, there are multiple rows where the Company is Dallas.

After loading each of the data tables in to Power Query, Merge Queries as New was applied, matching on Company. Account was the top table (left when viewed on a Venn diagram); EEID was the bottom table (right on a Venn diagram).

Download the workbook below to view the data tables and each of the joins.

↑

Left Outer join

The left outer join takes the Account table (left) and brings back each row from the EEID table (right) that has the same company. Notice how the amount of $50.00 is in the new table twice. It's duplicated, because there are 2 rows in the EEID table where the Company is Dallas.

A VLOOKUP in native Excel returns the first match; a Merge in Power Query returns all matches. This critical difference can dramatically skew your analysis if not handled appropriately. Future blog posts, particularly on Power Pivot, will provide alternate solutions for how to handle multiple data sets with duplicate values in the matching column.

Right Outer join

The right outer join takes the EEID table (right) and brings back each row from the Account table (left) that has the same company. Notice how the $50.00 amount is again duplicated, since it matches 2 rows from the EEID table. In addition, the Houston company has been added to the table, since the join starts with all company items in the right table.

Left Anti join

The left anti join starts with the Account table (left) and returns a value if there is no match in the EEID table. In this instance, the Company Dallas was in the EEID table, and so the resulting table is blank.

Right Anti join

The right anti join starts with the EEID table (right) and returns a value if there is no match in the Account table. In this instance, the Company Houston was in the EEID table but not the Account table.

Anti joins are excellent audit tools to monitor exceptions or open items.

Inner join

The inner join returns values only when Company matches in both tables. Notice the $50.00 amount is duplicated.

Full Outer join

The full outer join returns every Company in each table. Notice the $50.00 amount is duplicated.

Append

Append is another option for combining tables using Power Query. Append is designed to take an existing table and expand it by adding on another table that uses the same column names. If a column name doesn't exist in the original table, new columns are added. Notice that append picked up the 1 row from the Account table and the 3 rows from the EEID table exactly as they appeared originally. There is no Company.1 column and the $50.00 amount is NOT duplicated.

💥BEST PRACTICE TIP
As a general rule, Append is intended to make a table longer, while Merge makes a table wider. 💥

Merge and Append allow accountants and analyst to perform a wide array of new analytics. However, extra diligence is required to ensure the steps applied are yielding the analysis intended.

In this lesson, you learned how to:

join 2 data tables using Merge;
watch out for duplicates which could skew results; and,
Append multiple data tables together instead of Merge.

Prior post: Joins conceptually
Next post: 2 files 1 pivot table: Power Pivot