Skip to content

POWER SOLUTIONS

A few Power Solutions outside the normal Excel Mindset:

Power Query for Automation and Data Preparation

It’s not uncommon for users to spend hours each week or month copying files, cleaning data manually, and repeating the same steps month after month. Power Query eliminates this entirely. Instead of repeating tasks, you build a workflow once and refresh it with a single click.

Power Query is commonly used for:

  • Importing CSVs and accounting system or database exports
  • Cleaning and standardizing messy data or otherwise normalizing data
  • Combining files from multiple folders
  • Fixing date formats, text issues, and column structures
  • Merging tables without formulas
 
You now have a workable and time saving workflow and have reduced the entire process to a one-click process.

We have assisted many clients to reduce financial reporting from a 12-18 hour process to 15-30 minutes all with Power Query. We have also achieve the same thing with VBA Macro before Power Query became a feature so we were well positioned for this new feature. While VBA was the tool at the time, it was very time consuming to set-up where Power Query is set-up using the step you would normally use.

Power Pivot and DAX for Large-Scale Analysis

Power Pivot fundamentally changes how Excel works. It’s almost like a different program It allows you to analyse millions of rows, connect multiple tables like a relational database, and build models that update with a single refresh.  Many users still think in terms of complex databases and even legacy Access but Power Pivot has replaced these tool for us analysts and end-users.

DAX, the calculation language behind Power Pivot, enables advanced analytics that standard PivotTables cannot handle.

With these tools, you can quickly answer questions like:

  • What is our average revenue per customer by region and month?
  • Which products generate the highest profit margin?
  • How did sales change compared to the same period last year?
  • Which customer segment is driving growth?
 
Before you say “we can do that will regular Excel”. Yes, but it would require multiple files and complex formulas but in this case we are working with a million rows imported from five data sources. With Power Pivot and DAX, the process becomes fast, reliable, and scalable. DAX, they become fast, reliable, and scalable.
 

To say all this another way, your Power Pivot data models analysing over 2 million records across multiple sources, enabling real-time profitability reporting and reducing monthly reporting time minutes rather hours and days.

Power BI

Power BI builds on the same Power Query and Power Pivot foundations. You now have another tool to:

  • Connect to databases and cloud data sources
  • Transform and model large datasets
  • Build interactive dashboards for teams and executives
  • Share web-based reports with automatic refresh 
 
This is where Excel evolves into full business intelligence capability. Not everyone has this skill set or experience.
 
Interactive Dashboards
 
This requires a mindset that not everyone has. Many user summarize the data and say, “the report is done” even though additional manipulation is needed to have the needed decision making points. Management wants to understand quickly and clearly and have some tools to drill down or otherwise see more.
 
Your dashboard or other report page should allow the reader to:
  • See KPIs at a glance
  • Explore trends using filters and slicers, allowing them to further understand
  • Drill into regions, products, or time periods
  • Identify what is working and what is not