Here is Why I Personally use Power Query in all of my Excel 365 Solutions for Business

~ Because it is just so much easier this way ~

Power Query is why we changed our business model in 2025

~ Excel and Access , LLC should now be Excel and Power Query, LLC  ~

 

~ Power Query Completely Changes the Solution when used with Excel Tables in Excel 365 ~

 

But let’s backup 25 years. I have made my career as a database programmer, and an Excel programmer.  75% of my career was spent programming Microsoft databases, Access, SQL Server, and SSRS.  I am a college trained Database Programmer first and foremost.

I used Excel as all roads lead to Excel.   As such, I became an expert Excel developer and have made a career and a business of it ( Excel and Access, LLC since 2003 ).

I first programmed Microsoft Excel in 1986 on the Mac.  I have been at it since.

 

 

 

 

---

 

 

What I did for a living

Since 1999, every job I have had was 100% centered around developing custom solutions for businesses, using the Microsoft Business Applications.  If the job did not focus on development, I did not accept it.  I was focused.   

I had one thing in mind, to be an amazing programmer.

So focused: I worked full-time, and then before work, at lunch, after work, and weekends, I built my own company.  In 2010 I went out on my own. I have never looked back.  Success.

 

I spent 10-years as working as a Microsoft Database Developer

I spent 10-years working for others, before I launched my own firm.  I got my CHOPS in that time.  I learned a lot, constantly took classes, learned new methods, learned best practices, I invested in myself.  It paid off.

 

~ Here are a few examples of what I did for a living ~

 

Custom Solution Development Microsoft Access, SQL Server, Excel – Toshiba

I had the opportunity to build a new solution, from scratch, for Toshiba North America.  The solution to be built would be a shared application, both accounting and finance departments would use this solution, to run their operations. Used daily, critical for month-end.

Both departments ran off of this single database; this was a serious solution, took almost a year to program.

• A shared database, up to 10 users, built on SQL Server, Access, and Excel, with VBA.

 

They are still using it today, I built it in 2009.

 

SQL Server Reporting Servies (SSRS) Development – Resources Global Professionals

I spent over six years working as a consultant for Resources Global Professionals, building custom solutions in the Microsoft applications for their clients.  That is all I did, over and over and over for very large businesses.

I spent a year working internally at Resources, in Finance, at the corporate office, programming SQL Server Reporting Services.   I spent 8-10 hours a day typing SQL.  Not fun, did not like it.  But I got very good with Relational Databases and T-SQL Code.

 

 

AS400, Access, Excel Development – Tenet Healthcare, Finance

I took their current daily flash report that took 2-3 hours to manually update, and I 100% automated it, and I never ran it again.  It actually ran and distributed the files while I was still in bed sleeping.

• At midnight I ran a series of scheduled AS400 database queries.
• At 3am Access ran a series of macros.
• at 4 am Excel updated, the Dashboard was emailed and saved to the server.
• Automation comes down to design.

 

 

SQL Server, Access, Excel Development- Kaiser, Finance

Kaiser was SQL Server based, into Access, and then into Excel.   These were big solutions.

Once again, automation was the focus, and that is what I did daily, take their manual systems, and automate them, or rebuild them.

Knowing T-SQL was very important, being a DBA was very important.   Excel programmers lack this.

 

 

Access & SQL Server Development – Honda, North American Finance

I was hired by Honda to consolidate hundreds of Access databases into a custom SQL Server database.  I built the SQL Server database, I asked for four computers, and I wrote the code to extract the data from all of these inhouse Access databases, for their pending ERP system.

It took almost a year, with four computers on my desk, to make the deadline.  My SQL Server and DBA skills were critical for this job.

 

 

Excel and Access Application Development – El Pollo Loco, Finance

I took the job as an Associate Financial Analyst, as low as you can start, they doubted my computer skills.  Three months later I received a promotion based on my development skills.

When I started there, they gave me a 7 Lotus file solution, that took 5-hours to update.    I then went on to create an Excel Front-End to an Access Back-End solution that ran in minutes.

This was my first job, and it set the tone for everything that followed.

 

 

 

A Clear Focus – Microsoft Application Development, Skills Expansion

Every “traditional” job I have had was centered around Microsoft application development.  I only took jobs where the majority of my efforts would be in the development of custom solutions.

This is what I do; this is what we do at Excel and Access LLC

 

 

 

---

 

So why Excel 365 w/ Power Query for Relational Solutions?

I know database programming, and when I see Power Query, I see a Relational Database. 

If your need is small to medium, Excel 365 w/ Power Query can be an optimal solution for your organization.

• Microsoft Access databases cap-out at 2.09 gigs, that is not large either.

 

~ Size Matters in Excel, keep it under 10mb ~

 

“Relational Data Solutions”

1. SQL Server Programming
2. Microsoft Access Programming
3. Excel Programming

• All of the above use Tables, Primary Keys, Joins, Relationships, Data Types, and Queries.   They all provide the same output.

 

Power Query uses the same exact Joins as Microsoft Access, both based on Tables or Queries. 

 

---

 

Microsoft Excel is not a Database – TRUE

 

Microsoft Access is not a True Database – Some Say

Microsoft Power Query is not a Relational Database – Some Say

So many people say that, and they say Power Query cannot be used to create a relational solution in Excel 365, when based on Tables, Relationships, Primary Keys, Joins, M-Code, and VBA.  THEY ARE WRONG.

Access is a true Relational Database, with a focus on the user Front-end, a user-friendly experience.  When you use the Design Window in Access to create a Query, it is actually recording that in T-SQL.  When I worked in Access, I wrote my Queries in SQL Mode, not Design Mode.  Much faster to type the T-SQL.

• Unknown Access Format:  Then there is the other version Access databases that many Access developers have never seen, really, Access developers have not seen it.
  • Access.ADP files. This is a True SQL Database; Access just has the VBA, Forms and Reports, that is it.

 

Power Query works the same as an Access database, just smaller, 10mb versus 2 gigs.

 

 

If you understand Joins and Queries on Queries, then perhaps it is time you take a look at Power Query.  If you do, it might change how you program.  It changed the services my firm now offers.

 

 

---

 

Database Programming Flashback

Access at a Higher Level  .ADP

I know this ages me, but when I programmed Access databases, I often used the Access. ADP versus the Acces.MDB method.  I used SQL Server as the database, not Access, Access just had the code and such.  Not like an Access Front-End to a SQL Server Back-End, this is entirely different.

When I programmed in Access I did not do the Design Window, I worked in the SQL Window; I typed the SQL myself, just as I did when I worked in SQL Server Reporting Services, using Access, SQL Server and Visual Studios.  Wow, that takes me back, been 15 years or so.

 

 

 

I know database programming

 

I know Excel programming

 

I know Power Query programming

 

Not as a Trainer, but as a Tenured Programmer – HUGE Difference, Night & Day.

I have built hundreds of solutions from scratch – I know Best Practices

 

 

 

---

 

The First Time I saw Power Query

I stopped programming in 2010, as I was running the business, I have an international team of Excel, Access, SQL Server and Azure programmers and trainers doing that, many of them Microsoft Excel MVPs.  So I stopped programming.

In 2021 I saw Power Query.  I forget where I saw it, but it got my attention.

My MVPs were not interested in learning it, but leads were coming in on its usage.  So I took a look, and I was instantly hooked, the database programmer in me instantly saw the true potential – it uses relationships and joins.

So I decided to learn Power Query, even though I was not actively programming.

 

Then a DAF Sidetrack:  Then came along the Dynamic Array Functions, XLOOKUP, FILTER, and more to learn ……. I learned to use them, and I was using them, yet I found that PQ did the same thing, just easier.  So I stopped using them, as the center of the solution, and more as quick reporting, Power Query is just quicker and easier.

 

I was hooked on making Power Query and Excel Tables the center of every solution I program. Based on all of the hands-on experience I had gained as a database programmer.

 

So I Became a Power Query Programmer

Power Query development became the center of my focus, how far can I take Power Query, in an optimal solution for business.  How much can I do with design.

 

Fully Automated, Fully Integrated, Interactive, Intuitive, Dynamic Solutions for any Organization, based on Power Query and Excel Tables

 

So in 2021 when an existing Excel client asked for help with a Power Query solution, but the MVP he worked with did not know it, I stepped in to help, and I have been doing that since.

In 2025 we reorganized our company, centered around Microsoft Excel 365 w/ Power Query for Business.

• I am still at it today, getting better and better, developing new techniques, learning M-Code.   With Power Query there is always more to learn.

 

 

---

 

When it comes to the use of Power Query in Excel, Excel and Access LLC is all in

We now have an international team of professional Excel developers that build custom solutions for business, based on Power Query.  Add LAMBDA, Pivots, Macros, concurrent users, and you have a powerful solution for business.

I know several Microsoft Excel MVPs that build these same types of solutions, using Excel Tables with Power Query, to create relational solutions in Excel 365.  More and more people are starting to do this.

 

 

---

 

 

Why the Pushback in using Power Query this way?

 

Excel programmers and database programmers do not think the same, not at all

The applications are so different, it is often difficult for an Excel programmer to understand the benefits of Power Query.   Usually it is the use of Joins that losses them.  And it should, it is completely different than using Index/Match.

• One thinks in terms of Bi-State, one in Tri-State.  ( True, False Null ).
• One Normalizes their data.
• One understands Joins and Relationships.
  • It is a different way of thinking, we get it; we are here to help.

 

Hence the challenge, when you are trained to think one way, yet use the application another way.  Excel VLOOKUP or Left-Outer-Join?

The Excel VLOOKUP, the Access DLOOKUP , Power Query Joins, Jet-SQL Joins, T-SQL Joins, they do the same exact thing.

But doing joins in Excel freaks people out, much like the LAMDA Function.

 

 

Insane:   Remember, 95% of Excel users do not use Excel Tables.  Most people have never heard of an Excel Table.   Excel Tables are the absolute starting point in any Excel application, yet most do not know what they are.  We are here to mentor.

 

So for many Excel users, Power Query is not a viable option;

you do now know what you do not know.

 

We offer one-on-one mentoring services in Excel with Power Query.  We can teach you all you need to know to build powerful custom solutions in Excel 365.

It will absolutely change the way you program Excel.

 

 

---

 

Excel programmers and Excel developers do not think the same, not at all

Box versus no box.  Programmers do what the tools are supposed to do; developers hijack the tools and make them their own.  Example Using Power Query Tables as Data Edit and Entry Forms, not standard, yet powerful.

One is rules based, one is tools based; you can do many things with a standard screw driver, like open a bucket of paint!

 

A programmer will turn a screw with a screwdriver, a developer will do all sorts of things with this TOOL, but may never turn a screw with it

 

“Bending the Rules”, Matrix reference

 

Power Query is an Amazing Tool in an Excel Programmers’ Hands

Power Query is an even more amazing to in a database developers’ hands

One has vision and sees the potential and methods to make it possible – affordable database replacement solutions in Excel 365

 

---

 

I LOVE Power Query

I cannot image programming Microsoft Excel without Power Query

It could be done, using Excel Tables, the DAFs and VBA, but I would not want to do that, that is too much work, takes too long, has onsheet formulas, etc.

Power Query makes everything easier, much much easier

 

---

 

Pushing Power Query at Excel and Access, LLC

At my firm, we are constantly pushing to see how much we can leverage Power Query, while keeping the solution at optimal performance.  Power Query helps to optimize Excel 365 solutions.  It all comes down to design.

1. Before our focus on Power Query, we were focused on using Excel Tables, Pivots, and Dynamic Array Functions.
   1. Power Query changed how we think and how we program, there is no going back.
2. With our focus on Power Query, we are now focused on using Excel Tables, Power Query, Pivots, and Dynamic Array Functions.
   1. But we minimize the use of the onsheet DAFs.  Doing as much as possible in PQ.
3. If the user does not need to manually type data, no user is needed in a fully automated Excel 365 solution.
   1. The goal is to automate the workbook.
4. Power Query makes it so much easier.
   1. Power Query reduces the use of VBA/Macros
   2. Power Query reduces the use of onsheet Excel functions such as the XLOOKUP.
   3. Power Query reduces the need to have a user.

 

 

 

 

Misc Notes:

• Excel files should be smaller than 10mb in size.
• When you use Tables, you simplify the calculations being used.
• Power Query can handle the calculations we do for our business clients.
• The version of Excel matters when using Power Query.

 

 

---

 

Our current focus is on building database replacement solutions in Excel 365, leveraging Excel Tables and Power Query

~ Add M-Code and VBA for even more Power ~

We are pushing the limits, using Power Query Tables as Data Entry Forms, instead of using UserForms.    We can easily Create, Edit, Delete any record in the data tables quite easily, using Power Query Forms.

We know others that are also doing this, several leading MVPs, but not very many of us.

We are pushing the boundaries to see what is possible.

 

Benefits of using Power Query in a relational solution:

1. Rapid development
   1. Power Query Development is much faster than say using the DAFs or Legacy functions.
2. Affordable
   1. A fraction of the cost of a new Access database.
3. Excel based
   1. You already have the software, billions of people know how to use Excel.
   2. Most software packages send the data to Excel.
   3. It is easy to find Excel help when you need it.
4. Easy to edit, expand, etc.
   1. Unless you protect the file, you can revise it as needed, as your business changes.  Usually for free.
5. Easy to automate, often no user is needed.
   1. Unless you need to manually type, you do not need a user to run the application.

 

 

---

 

Conclusion:  You could program Excel this way, if …

 

If your data is in Tables, if you have added Primary Keys to those Tables, if you are using Power Query in Excel 365, you can use Joins between your Tables, then you can base your entire solution on Queries versus formulas.

The results are Refreshable; you load results not functions.  You can load the results to the worksheet versus onsheet functions that can be accidentally deleted or changed.

You can reduce or even eliminate the need to use macros.  Macros are used to manipulate data as is Power Query.

You can greatly reduce the number of cells in your workbook with functions in them.  Functions that can be deleted, that need to be updated, protected, etc.

You can greatly reduce the effort of the user.  Unless you need to manually type data, then there is no need for a user at all.

 

Give it a try:   To use this approach, all of your data is in Excel Tables, your output is in Power Query Tables or Pivot Tables, when needed you use DAFS such as PIVOTBY to create a report, based on the Power Query Table output.  Goal, to minimize the use of functions, macros, users.

 

 

---

 

 

 

Power Query is very easy to learn and very easy to use – we are here to help

 

One-on-one mentoring in Microsoft Excel 365 Development

Mentoring Dynamic Excel Development is one of the advanced services our company offers.  Here we take an intermediate or advanced Excel programmer, and we take them up to the expert level.  What you learn here will put you above others who program Excel.

877-392-3539