Case Study:  How Power Query is Used in Business Finance

Case Study on How Power Query is Used in Business Finance is important for Business, covers how Power Query can help you to automate your solutions.  

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

 

Finance uses Power Query:  A Powerful Data Transformation Tool, integrated into Microsoft Excel.  Excel’s Power Query is like a mini database inside of Excel.  MS 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.

  • Power Query Case Study covers how to use Power Query in a Business Finance setting.
  • Many Finance Departments have Power BI, but many Business Executives choose not to use it
  • Leading Finance Professionals use Microsoft Excel with Power Query Instead

 

But so few people are actually using Power BI.

SHOCKING:  Power BI is paid for, but now always used:  Many organizations use Power BI, and similar applications.  But most of the time, they actually download the data from their app, into Microsoft Excel, and they manipulate it there.  They do this to avoid working in Power BI, DAX, and all.  Many Directors, VPs, CFOs, etc, use Excel and Power Query to get the job done. 

 

Most of the time, they actually download the data from Power BI, into Microsoft Excel, and they manipulate it there.  They are increasingly turning to the use of Power Query.  If they do not know Power Query, they hire our team of expert Excel consultants. Contact us for Power Query programming services.  877-392-3539.

 

 

 

Case Study:  Sample Power Query Based Solutions:

We will show several examples of how Power Query is used in business finance.  In one example, we built the same basic solution, twice, for the same client, one with zero vba and zero Excel functions.  We did the second solution in a fraction of the time, and it was all based on Power Query, Excel Tables, Pivots, and that is it.

The focus on these solutions is on the degree of integration and automation being used – can we remove the user from the update process, that is our ultimate goal?

 

~ Dynamic Excel 365 Business Solutions – No User Needed ~

 

 

 

Power Query Mentoring and Programing Services for Business Finance

In this post we cover the use of Power Query in Business Finance. If your finance department needs help with Excel, and specifically custom Excel solutions, based on Tables and Power Query, give us a call, we offer professional programming and training services to business.

877-392-3539

Power Query Programming Services – Excel and Access, LLC

 

 

 

~ How do Finance Departments use Microsoft Power Query ~

 

Finance Departments are used to dealing with large amounts of data.  They copy, they paste, they type, they run VBA, they open this, close that, etc.  They think this is normal; they think this is how you use Excel.

What a waste of time; do these people walk to work?

This process can take them from hours to days or even weeks.  It is 100% not necessary; use Tables with Power Query instead.  Write less functions, write less code, increase the time spent on analysis and decision making. Most Finance professionals do not know Excel well at all; as they never take the time to learn new methods, new functions, new tools, they stick with INDEX/MATCH.

 

Expert Opinion:  I spent my professional career building complex financial solutions for companies such as Tenet Healthcare, Kaiser Permanente, Toshiba Finance, Honda Finance, El Pollo Loco Finance, RGP Finance, Robert Mondavi Finance, etc.  

What they wanted most were fully integrated and automated Excel based solutions to drive Dashboards, Reporting, Analysis.  

 

Excel Expert Says:  I know what businesses, and particularly finance want and need; they are using the Excel solutions we built; this is what we do.  We make fully automated and integrated solutions in Microsoft Excel 365.  These solutions do not require a user to manually perform the update process; the solution runs itself in seconds.   No User Needed.

 

Most Common Uses of Excel in Business Finance:

  1. Month-end Financial Reporting
  2. Month-end Close
  3. Financial Analysis
  4. Budgeting
  5. Sales Analysis
  6. Dashboard
  7. etc.

 

If you work in Business Finance:

If you work in accounting or finance, and you have a lot of data to manipulate and transform, you will want to use Power Query. Forget thousands of calculations, and massive amounts of VBA, just use Power Query.

 

What does Power Query do in a Business Setting:

Power Query “Transforms” your data.  Remove rows and columns, run calculations, create reports, populate Pivot Tables.  Don’t Copy & Paste, do not copy-down.  Remove the user from the Excel update process.   Power Query is how finance is done.

 

 

 

Power Query Mentoring and Programing Services for Business Finance

In this post we cover the use of Power Query in Business Finance. If your finance department needs help with Excel, and specifically custom Excel solutions, based on Tables and Power Query, give us a call, we offer professional programming and training services to business.

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

Power Query Programming Services – Excel and Access, LLC

 

 

CASE STUDY #1:   Program the Same Excel w/ Power Query Solution – Twice.

It is not every day you have the opportunity to basically rebuild your own custom Dashboard solution.  When you do, you get the chance to see how good you are, how much progress you have made in learning new methods and techniques and features.  What improves can be made in the second solution.  That is where the fun is.

  1. The second solution was 100% based on Excel Tables, Power Query, and Pivots.
  2. The first solution included the use of onsheet Excel functions as well as VBA.

 

Yes, the same basic solution, for the same client, at two different companies. That never happens, the same basic solution, what an opportunity.

The client worked at two very similar companies, both listed on the NYSE.  These are leading providers in the healthcare industry.  They have lots of data, and their organization uses Power BI, which the executives have decided NOT TO USE POWER BI.  They hired me instead, for my custom Excel with Power Query Dashboard solutions.

So why did I build two similar solutions?  The executive I primary worked with was recruited for the same position, at one of their competitors.  Same industry, just a different drug.  So when I got the change to work with them again, I jumped at the chance.

 

Goal, to make a model similar to the past one, but to make this one 100% automated via Power Query.  And that is what I did.

 

  • I built the first solution over a period of 6 months.
  • I built the second solution in just 45 hours.
    • The second solution was better than the first solution.
      • The second solution can be run by anyone, no Excel skills needed.
        • It refreshes in a matter of minutes.

 

Excel and Power Query, the Ultimate Automation Combo in Excel.

Case Study:  How Power Query is Used in Business Finance Post: Image of Pivot Table based Excel Dashboard.


A single Dashboard can allow the executive team to drill down on demand. So much detail here.
Populate an Excel Pivot Table, with external data. No onsheet functions, no vba, just Power Query and Pivots.

 

Why did they Hire Us:  CEO Says to me, “Our finance department cannot do anything near what you can do, that is why I hired you”.  This is a multi-billion-dollar company, on the NYSE.

Question:  Who hired their finance team, that is an important question, as they hired the wrong people, as they do not know the “New Excel” ( Post 2018 ).

How fast is the turnaround time when new data is received:  Within 15 minutes of receiving his four data files, his monthly sales dashboard is up to date and in his inbox.  Power Query takes 1.25 minutes to update, that is all there is to do, press RefreshAll.    No VBA, no onsheet Excel functions, just Power Query and Pivots.  No user needed.

How does Power Query help in the automation process?  Microsoft Power Query is a data transformation tool, built on Tables, via Queries.  Excel based Power Query allows the system to update for new data, without the need of a user.    Note:  Power Query is how dynamic Excel automation is done.

 

Google 5-Star review on our Excel data visualization consulting services.

 

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

 

 

 

Senior Level Executives are Forced to be Excel Programmers

I have seen many a CEO, CFO, COO, VP Finance, Controller, Director, etc., copy data out of Power BI.  They they paste it into Microsoft Excel.  They spend hours or days further manipulating the Excel data.  Goal, to ultimately get an Excel Pivot Table for their Dashboard.  This is a waste of their time and the firms money.

 

WHAT A WASTE OF TIME AND MONEY AND TALENT.   Are you a decision maker or an Excel programmer?  Hire us, we will build you a custom Excel based business solution.

 

The Finance Team has access to Power BI but hates it so much that they will not use it.    They have it right there on their computer, but instead of using it, they are spending large amounts of time, building their own Excel models, manually manipulating data, just so they can get the monthly reports out the door.

Instead, they hired me, to build a complete solution, in Power Query, outside of their Finance Department.  As the client says, our Finance Department cannot do anything near what you can do.  That is why he hired me, twice.

 

Power Query will populate Pivot Tables, Pivot Charts, and Power Pivot.

~ No Excel user needed ~

Case Study:  How Power Query is Used in Business Finance Post: Image of Pivot Table, using Power Query data.


To populate an Excel Pivot Table and Pivot Charts, take the data into Power Query, and use the results directly in a Pivot Table, without the need of placing the Table on a worksheet, (Load as Query only).

 

 

 

The Same Finance Dashboard Solution – Twice

  • The first time we built this we used Excel Tables, Power Query, Pivots, Dynamic Functions and VBA.  Six month effort.
  • The second time built this we used Excel Tables, Power Query, and Pivots.  No use of functions or VBA.   45 Hour effort.

 

Same basic solution, other than one is 100% dynamic, no user needed, no user needed at all.

 

Case Study:  How Power Query is Used in Business Finance Post: Image of Excel Report, based on dynamic array functions.


Dynamic Array Functions are an amazing tool in Microsoft Excel. Great for creating custom fully interactive, and automated reports. Users LOVE them

 

 

Two Power Query Solutions; for the same client, at two similar, but different companies:

It is a rarity that you get the opportunity to build the same solution, twice.  But that is exactly what happened with this client.  You see, I built a custom Dashboard for their executive team.  It was the basis of the Monthly PowerPoint Presentation.  Then  8 months later, I was contacted by my primary contact, who is now working at a different company, and he wanted a similar Excel Dashboard.  You rarely get the chance to rebuild a complicated solution.   Read the Case Study:  How Power Query is Used in Finance

Focus, how can I make this model superior to the first one I built.  Every model you build should be your best work; you should always be improving your design.

What made this extra special is that the first model and the second model were built much differently.  But they provided the same results.  What was the difference?  The second solution was 100% Power Query, Excel Tables, and, 0 VBA, 0 functions.  The first model was Power Query, Excel Tables, Pivots, Dynamic Array Functions, and VBA.

Can you imagine having the opportunity to rebuild an entire solution, from the ground up?  What if I told you, the first model was built over a -month period?  The second model was built in 45 hours.  The difference, one is basically all Power Query & Pivots.

 

 

Case Study:  How Power Query is Used in Business Finance, Waterfall Reports

Waterfall Reports based on DAFs or Power Query

In the first custom Excel solution we used several of the new Dynamic Array Functions for interactive analysis and reporting.  The Waterfall Report below was based on DAFs ( See Image Below ).  In the second project the Waterfall Report was 100% done in Power Query.  

DAF Waterfall Report

This is how Power Query is Used in Business Finance.

Case Study:  How Power Query is Used in Business Finance Post: Image of dynamic array function based Waterfall Report.

 

DAF Lost Accounts Waterfall Report

Case Study:  How Power Query is Used in Business Finance – DAFs Report

Case Study:  How Power Query is Used in Business Finance Post: Image of dynamic array function based Waterfall Report, showing "lost accounts".


Dynamic Array Functions built this Waterfall Report

 

Start Period Same Store Sales Power Query Waterfall Report

Case Study:  How Power Query is Used in Business Finance – Slicer Based Waterfall Report.

Case Study:  How Power Query is Used in Business Finance Post: Image of Excel Waterfall Report, built on Power Query, not functions.


100% Power Query based Waterfall Report. Slicers make it interactive.

 

 

Quarter over Quarter Same Store Sales DAF Report

 

 

Power Query is where the heavy lifting is done

Case Study:  How Power Query is Used in Business Finance Post: Image of the Power Query Editor.

 

 

 

Conclusion:  Case Study:  How Power Query is Used in Business Finance:

  • Power Query:  A Powerful Data Transformation Tool, integrated into Microsoft Excel.  Not surprising, Power  Query is like a mini database inside of Excel.  Excel’s Power Query ability is more advanced than Excel functions when it comes to transforming your data, that is what it does.
  • Power Query allows you to fully automate and to integrate your custom Excel solutions.
  • We have a team of Excel and Power Query experts to work with.  We offer programming, training and mentoring services.   We offer free consultations, low rates, and top-notch work
  • As such, at Excel and Access, LLC, our top Excel consultants leverage Power Query in our most advanced Excel solutions for our clients.

 

 

If you need help with Power Query, how it is used in business, we are here to help.

 

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