Best Practices

Here's a quick snapshot of the Best Practice Tips published on The CPA Revolution blog. For additional context, select the header to be directed to the related post.

(By major topic, from newest to oldest)

---

Excel

What's killing my Excel?
Speed up the add-in enabling/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).

---

Power Pivot

Relationship problems

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 Look-up Tables or other Data Tables, and the Pivot Table will return inaccurate results.

Measures inside Measures
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.

Measures In-Depth
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.

2 Data Tables on 1 Pivot table
Always connect the Data table to the Lookup table. Do not connect Data tables to each other.

Building Relationships
Layout your data model so the Data tables look up to the Lookup tables.

Power Pivot Date Table
For your future data models, save the Date Table workbook to use as a date table template.

---

Power Query

Combine all Files in Folder, coming October 25

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 updating the number of columns.

Joins illustration
As a general rule, Append is intended to make a table longer, while Merge makes a table wider.

Choose your own data adventure
There is no undo in Power Query. Be careful when removing steps. If you accidentally delete a step, you can close the query and discard any changes; however, you will loose all changes made during the current session.

-------

Learning new tricks is an investment. Consider building out a few steps in PQ, loading the query and finishing your transformation in native Excel. This progressive approach blends both worlds until PQ becomes second nature (which it quickly will with a little practice).

Automation
Create queries by connecting to the source whenever possible. Before loading data in to Power Query, evaluate if the data is coming from the best source. Receiving data directly from a source system is best.

Process bank account listing
Don't leave number and date columns as data type, ABC 123, Any. When queries are not working as expected, ABC 123 is a primary culprit as Power Query (PQ) functions frequently rely on a specific data type in order to operate correctly.

PowerBI and Power Pivot
As you start to explore on your own and Google how to perform new processes, alternate using "Power Query" or "PowerBI" in your search terms to get expanded results.

Process GL data
If a column contains numbers you would never sum, then change the type to "Text". In our example, we would never sum the GL account numbers or department numbers; therefore, they should be "Text" not numbers.

Load data
Carefully name your query when loading data.
Using No Spaces and labeling the query as either a Data query or a Lookup (LU) query will be helpful when building your data model and formulas.