Our Excel Consultants Leverage Power Query

One of the most used and respected tools used by leading Excel experts is Power Query.

A Powerful Data Transformation Tool, integrated into Microsoft Excel.  Power Query is like a mini database inside of Excel.  Power Query is more advanced than Excel functions when it comes to transforming your data, that is what it does.   As such, at Excel and Access, LLC, our top Excel consultants leverage Power Query in our most advanced Excel solutions for our clients.

In this post we will show an example of a recent Excel project where we focused on 1) Excel Tables, 2) Power Query, and 3) Dynamic Array Functions.   Super quick, ultra-compact solution. 

 

 

 

Dynamic Excel, there is no better way to fly – Excel Consultants Leverage Power Query.

 

Image of Power Query being used in Excel.

1) Excel Tables, 2) Power Query, and 3) Dynamic Array Functions, and maybe a little vba.

Excel without Power Query is like Excel without VBA.

 

 

 

 

This post covers our Excel Consultants Programming in Power Query

We go over a recent project where we merged Power Query with Excel Tables.  The solution is ultra compact, with only a handful of tabs in the file.  The original solution was spread across dozens of workbooks, with over a hundred tabs per workbook.

Excel Tables were the backbone of this solution.  Tables greatly simplify data management in Excel.  Power Query just takes it to a higher level.

 

Image of Excel Table being used to enter data.


Excel consultants use Excel Tables to store your data. Use Power Query to manipulate the data in the Excel Table.

 

 

 

Our Excel Consultant’s Solution:

The Primary Problem:   The files were protected, and they did not know the password, and as such they could not revise the files to keep up with their changing business needs.  So, they hired our team of Excel consultants to build them a new solution from scratch, one that is easy-to-use.

Our Custom Solution: Based on their needs, our consultants determined that Power Query with Excel Tables, and the Dynamic Array Functions would be the backbone of the custom solution.  Minimal amounts of vba were used to move the data.

Project Results:  We reduced file size by 97%.  We increased ease of use.  Now anyone can use the file.

Project Summary:  Not only were our Excel consultants able to decrease the complexity of the application, but we also simplified the update process with a few small macros.

The solution exceeded their expectations and saved countless hours of unnecessary work every day.

 

Experts Note:  Seasoned Excel consultants have learned that basing your Excel solutions on Power Query and Tables builds a solid foundation for a dynamic Excel solution. Often without the use of VBA.

 

Image of Power Query Table and Excel Table

Our top Excel consultants load Excel Tables (Blue) into Power Query, Transform the data, then download back as an Excel Table (Green). 

 

 

Hire our Team of Expert Excel & Power Query Consultants

If you need to hire an Excel consultant to assist you with your Excel Power Query projects, please give us a call today.

Toll-Free: 877.392.3539 | Irvine, California: 949.612.3366 | Manhattan, New York: 646.205.3261

Contact us for a Free Consultation Today.  Excel Consultants Leverage Power Query. We will build your 100% custom Dynamic Excel Solution

 

 

The Client’s Excel Challenge is Two-Fold

The original file is no longer working, and they have lost the password.  As such they cannot revise the file, nor correct the formula errors.  On top of that, the solution they were using had too many tabs and too many files.  So they hired our team of Excel consultants to build a custom solution that matched all of their needs, including ease of use.

But their problem gave them the opportunity to simplify their system and to save large amounts of time, so it is not all bad.

It is the norm:  80% of Excel users spread their data across multiple tabs, and often multiple files.  ( See Image Below ). It is the norm, but this is not the optimal way to program Microsoft Excel. If you want to analyze the data, across those tabs and files, that can be problematic.  So many Excel users do this that Microsoft created the VStack Function.  It stacks data from multiple tabs, into one vertical data table.  As data should be.

 

Excel Consultants Expert Advice:  Or you can use Microsoft Power Query to easily consolidate data from multiple tabs and files.  Power Query is a game changer; consolidate data, interactively, usually without the use of vba.

 

Image of Sales data across multiple tabs. The VStack Function resolves this issue for Excel programmers.


Example Image – Multiple Data Ranges – common practice.

 

Why they do Excel users do this:  The reason they found themselves in this spot, they did what 80% of Excel users do, they spread their data across tabs, instead of in one proper Excel Table.

You don’t know what you do not know, in this case they did what most Excel users do.  But they did not do what most Excel consultants would do, use Power Query with Excel Tables.   For a dynamic Excel solution.

 

 

Our Excel Consultant’s Advice 

Instead, you should put like data in one vertical Excel Table.  In a database, you have a table for each type of data, sales, expenses, staff, etc.  You should do the same thing in Excel; One Table per type of data.  ( See Image Below )

 

Your Thoughts:  Which is easier to reference via functions and dashboards?

 

Image of Excel Table. Using a Vertical Excel Table eliminaes the need to use the new VStack Array Function.


Example Image – One Vertical Excel Table

 

 

Their Original Excel Solution was Not Working

They were in a bad spot as their files were password protected, and they had lost the password.  There were broken formulas, which they could not fix.  Instead of one easy to use file, they use hundreds of files.

So if they were going to rebuild their solution, why not take the opportunity to build a solution that better fit their current set of needs.  So we did.

Below is their original solution, it worked, before they lost the password, but it did not work well, was not intuitive, had a lot of manual steps, not to mention dozens of files.

 


The Main Tab would summarize the 50 locations for this file. There were dozens of files with the same layout.  The file used many direct cell references and If Statements. We were sure our Excel consultants could help them.

 

 

Their Original Excel Solution:

  1. Dozens of Excel workbooks
    1. Each workbook has:
      1. 50 Data Entry Tabs
      2. 50 Report Tabs
      3. One Summary Tab
      4. One Client List
  2. Cell, Workbook, and VBA Protected.
  3. There were broken formulas which could not be accessed.
  4. They lost the password and were unable to edit their files.

 

80-90% of Excel Users place data across multiple tabs, and multiple files.   Please, STOP It!

 

Our Excel Consultant’s Focus: 

To make a single-file solution, that is 100% automated, and ultra easy to use. Point, click, done.

 

 

 

Our Power Query & Excel Tables Based Solution 

For this client, we created a solution that was ultra easy to use, and simple to maintain.  Now all of the data is in one place, one Table, and it can easily be access via Power Query or Excel functions.  Excel should be easy to use; our Excel consultants make it so.

  • An Analysis Tab
  • A Customer Data Table
  • A Milestone Data Table
  • Detailed Transaction Table
  • One Lists Tab
  • Bonus: Dashboard Analysis Tab
  • Use of the Dynamic Array Functions
  • Conditional Formatting
  • Validation Controls
  • Slicers
  • VBA / Macros

 

 

 

Power Query should be in a Consultant’s Top 5 Tools for Advanced Excel Programming ( Read More ):

  1. Power Query
  2. Excel Tables
  3. Data Model
  4. Dashboards
    1. Pivots Tables / Power Pivot
    2. Pivot Charts
    3. Slicers
    4. Dynamic Array Reports
  5. Dynamic Array Functions ( DAF’s )

 

 

In Conclusion: Our Excel Consultants Program Power Query in our most Advanced Excel Projects

If your company uses Microsoft Excel, and if you would like to automate your processes, Power Query is the way to do it.  Combine Power Query with Excel Tables and DAFs, and your solutions will be based on the new Dynamic Excel.  Easy to use, fewer formulas, less vba.

If you need help, our team of expert Excel consultants can program Power Query for you.  Or if you prefer, we also offer one-on-one training in Power Query.  Either way, we are here to help you and your business, to get the most out of Microsoft Excel and Power Query.

 

 

 

Images of Tabs in File

 

Below are images of the tabs in the file.  You can see, that there are just 6 user tabs in the workbook, far fewer than the hundreds of tabs per file.

Summary Tab:

The Tab below is the Summary Analysis tab.  It is based on Power Query, and it uses dynamic functions for the quick analysis to the right.

 

Image of Power Query Table


Point your dynamic array functions at the Power Query Table.

 

Customer Tab:

The tab below houses an Excel Table, where the user places data related to each property.  This is the one side of the equation.

Excel Tables are literally the best place to store your data in Microsoft Excel.  A Table is a container, and it makes maintaining and referencing the data in it, much easier than traditional ranges.

 

Image of Excell Table with Slicers for manual data entry


Adding Slicers to Excel Tables is a way to simplify the user experience.

 

 

 

Base Data Tab:

This is the tab where the base data is housed.  This is moved to the main data table each time a new property is added.

 

Image in post: Excel Consultants Leverage Power Query


Step 3

 

Main Data Tab:

This is where the data on each property is stored and maintained.  The Slicers simplify the process.

 

Image in post: Excel Consultants Leverage Power Query


Step 4

 

 

Dashboard Analysis Tab:

Quick mini dashboard based on the selection in the drop-down list.  Uses the dynamic array functions.

 

Image in post: Excel Consultants Leverage Power Query


Dashboard

 

 

Lists Tab:

We use Excel Tables for our Lists in Excel.   Validation Controls and Calcs reference these throughout the file.

 

 

 

Power Query Results Tab:

We used Power Query in Excel to calculate results, based on data in the Excel Table.

 

Image in post: Excel Consultants Leverage Power Query


Power Query Output

 

 

 

 

 

Does your business need help with Power Query?

Our Excel Consultants Leverage Power Query for Business, Government and Education

This is not a post on how to program Power Query.  If you would like help with that, please reach out so we may discuss our Power Query programming & training services.

 

Toll-Free: 877.392.3539 | Irvine, California: 949.612.3366 | Manhattan, New York: 646.205.3261

Contact us for a Free Consultation Today. We will build you a Dynamic Excel Solutions with Power Query