Leveraging Excel Data Model

Leveraging Excel’s Data Model

~ Excel Tables, Power Query, Power Pivot, and the Data Model ~

Proper Excel solutions start with Excel Tables. Advanced solutions leverage Power Query to transform the data into something useful. You could stop there, and use LAMBDA or a series of Dynamic Array Functions, onsheet, to get the results you want, and you could even use a standard Pivot Table, Pivot Charts, and Slicers. Or you could take the solution to the next level, use the Data Model to deal with BIG Data needs.

BIG Data Excel Solutions leverage the Data Model and Power Pivot

~ If you have millions of rows, file sizes in the gigs, then you may want to consider using Excel’s Data Model and Power Pivot, based on Tables and Power Query ~

Take the solution to the next level when you are dealing with BIG Data and load the Power Query Data Tables and the PQ Dimension Tables into Excel’s Data Model. You can then leverage Power Pivot, allowing tens of millions of rows.

*With the Data Model we are still using Excel Tables and Power Query, but we are using those to stage the data for the data model in Excel 365, versus doing Joins in Power Query.

BIG Data Expert

The Excel Data Model in Excel 365 is relational in that allows you to work with extremely large datasets based on relationships between the tables and queries.

The Data Model leverages Excel Tables, Power Query, and Power Pivot.

Excel 365 Data Model, here showing Power Pivot and DAX.

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

Contact us for a Free Consultation Today.

BIG Data Needs a Data Model

If you have BIG Data, millions of rows, consider using Excel’s Data Model. Millions of records are no longer an issue. Else, SQL Server would be the next application to look at.

Else, how would you do it?

When should we consider using the Data Model

~ Large File Size & Performance ~

Size, the data model works with very large files, with tens of millions of rows. Excel cannot handle that on its own.

LARGE input files.

Here one of the input files is literally 11 gigs, an 11-gig csv file. Microsoft Access databases in comparison max out at 2.09 gigs.

Here the other files are pretty large as well.

Massive Excel files

Look at the size of these Excel files. 400,000 MB! That is way to large. Way too much for Excel.

Use a database like SQL Server, or use the Data Model in Excel 365.

Excel Files using Power Query and massive source files.

Experts Tip: So if you have BIG data, you may want to take a look at the data model in Excel 365. Leveraging Power Query with Power Pivot, that is how BIG Data is done. SQL Server or such are also options. But if you can do it in the data model, that is a lot easier and cheaper.

The Data Model Solution – Excel Tables, Power Query, Power Pivot

The Excel Data Model takes your current Power Query based solution, and expands on what is already there. You use many of the same Tables and Queries in the Data Model that you are already using in Power Query. Here we make those Tables available to Power Pivot. There we join the Tables, and the result is a super powerful Power Pivot Table.

DAX is hard, for sure, but did you not at one point say the same things about VBA and Power Query? If you want easy, use AI. If you have BIG Data, use DAX.

Excel Tables
Power Query
Data Model
Power Pivot
Dynamic Array Functions
Code

* For most, the Data Model should be used sparingly, as needed, for BIG Data or complex needs. It is worth learning. If your repeat client suddenly has gigs of data, and if he wants to work in Excel, how will you help him? The best way, if you have to stay within Excel, is to use the Data Model.

I put off learning the Data Model, until my client asked for it, then I went over to EOTG to learn all about it. Then Chandeep. The resources are out there, it does not take long to learn this. Well, DAX will take some time, but ………

I definitely did not want to learn DAX, I am focused on learning M-Code right now.

Anyhow, Power Pivot is powerful, and worth looking at, when the need is there.

The Demo Solution

I spent a few hours, created four Excel files, and I created a super simple to understand budget based demo, to show how you would set this up. It is meant to be simple, so that it is easy to follow.

The demo is all about the components of the solution, and how the data flows, from External data files, into Power Query, and then into Power Pivot.

The Periods Table in Excel 365

Here we load an Excel Table into Power Query. We will expand the number of columns in PQ, not onsheet using Excel functions.

We could do this Table Transformation directly in Power Query, which is usually preferred, but we did want to load the Fiscal Year and the Business Days, both which are entered by the user.

So we upload the base Table into Power Query, and we do the rest of the column transformations there, based on the Date attribute.

~ Power Query looks at the User Filter Checkboxes.

~ The User Filter column allows the user to specify which periods are loaded into PQ.

~ The Budget or Actual column attribute is a function. This can be used in Headers, by functions, etc. (This too can be done directly in Power Query, but here, we wanted the user to see it).

A Periods Table is very useful in an automated solution. You can load that into the Data Model as a Dates Table.

Power Query brings in the current period, which is used as a Filter, or used in Joins.

Power Query Transforms the Table

Power Query will create Period related columns, simply based on a date attribute. No need to do this in Excel anymore.

Extracting columns from the Date field is quick. Month Name, Month Number, Start Date, End Date, Number of Days, Quarter, Week Number, etc. Very easy to do in Power Query.

Load the Periods Table into the Data Model as a Dates Table. Apply Slicers on this Table’s attributes in Power Pivot.

External Excel Data Tables in Excel 365

This is where a proper Excel solution begins. Excel starts with Excel Tables.

If you are using Ranges that look like Tables, convert them into an Excel Table.

Here we load 3 Excel Tables into Power Query, where we combine them into one Table. Now we can reference one Table and not three. And this approach works well in the Data Model as well.

Data loaded to: The data Tables are not loaded to a sheet; they are loaded as a Connection Only. The Table is loaded into the Data Model. Power Pivot will reference that Table.

Up to this point, nothing is loaded to a sheet.

The three external data sources, three Excel workbooks, with a single Excel Table in each. Each workbook has the same structure.

For this demo, we created three external Excel data files, using Excel Tables.

The External Data Sources – Demo Files

Actual Data in an Excel Table. Loaded into Power Query as a connection only.

Budget Data in an Excel Table. Loaded into Power Query as a connection only.

Forecast Data in an Excel Table. Loaded into Power Query as a connection only.

The three Tables/Queries are Appended to one Data Table in Power Query, for the Data Model to use, to create the Power Pivot Tables.

Power Query in Excel 365

Power Query is now a standard in advanced Excel solutions for business. And for good reason, PQ greatly simplifies Excel programming, and it greatly reduces user effort, vba, and functions.

Right after Excel Tables comes Power Query, long before you touch a function or Pivot Table.

Excel Tables are referenced via Power Query.
Pivots and Functions reference Power Query Tables.

Here we have four Tables/Queries with Financial Data. We append three of those into one Table.

Power Query and External Data, the Perfect Match

Power Query makes this easy.

Like data goes into one vertical data Table ( Budget, Actual, Forecast )

Not everyone would do this, and this might even shock a few people, but it is actually a smart way to go. Simplifies formulas, simplified queries, simplifies Pivot Tables.

Here we appended the Actual, Budget and Forecast data into one Table- it simplifies things

The three Tables have the same structure, same data types. If they are combined into one table, then it is easier to reference that data.

The Tables in Power Query

Here you can see the data flow, from external data files to the data model.

In Power Query you create the same Joins that you would use in SQL Server or Access.

Here are the Tables in Power Query.

The Data Model in Excel 365

Currently building this out as I learn DAX.

Currently viewing EOTG course on Power Pivot and DAX.

The Tables in the diagram are based on Power Query, they have been added to the Data Model.

Power Pivot is like a Pivot Table on steroids. One of the three components in Power BI.

The Joins in the Data Model are One to Many.

DAX is not fun.

More to come on the Data Model as we build out this solution, as I learn DAX.

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

Contact us for a Free Consultation Today.

Elizabeth Hand

16:38 15 Jul 21

The team and Excel and Access are very efficient, friendly and professional. I'm confident that when I send a project to them, it will be done quicker than I expected it and done perfectly.Thanks!

BC Synapse

23:32 02 Feb 21

I arranged for two 1.5 hour sessions on Microsoft Excel Tips and Tricks sessions for my organization in January 2021. I am 100% satisfied with the service. Chris and Maria were very responsive to my initial inquiries and helping me with setting up the virtual sessions. This company provide consulting services on various applications and I liked how I was able to customize my sessions by selecting the tips and tricks to be shared- it was 100% customizable.Our trainer, Bob Umlas, was superb in the delivery of both sessions. He provided an Excel workbook and shared his screen to show all the tips and tricks on how to use Excel more efficiently. The sessions were also recorded so that staff could rewatch the lessons again at a later date. Overall I was very please with the company's services and would work with this company again should future opportunities arise.

Christine Williams

20:52 01 Feb 21

These guys are the excel experts! They take our complicated needs and turn them into easy tools that we can use and run on the daily basis. Quick turnaround as well, the latest project we needed help with they completed and had back to me in 24-hours!

John Gebbia

22:45 18 Dec 20

Excel and Access was a great pick for our excel training needs. The training class was very easy to follow and Bob our instructor was very knowledgeable. Would definitely recommend and will be going back to them for future training needs.

Kelly WILKEN

20:01 03 Dec 20

Jacob was fantastic to work with. I was tasked with developing an easy way to track our Corporate Training across all departments and positions based on company requirements and hire date. I knew there had to be a way to do it in Excel with formulas, but it was way above my skill set. I contacted Excel and Access, and they listened to my needs and paired me with Jacob. He was very responsive and was quickly able to come up with precisely what I needed. He was great at making the necessary tweaks, including creating a “GENERATE” button in bright red so that I couldn’t miss it. I can now run all of my reports with all of my different conditions with the click of that big beautiful red GENERATE button. I will definitely reach out to Excel and Access and Jacob for my future needs.

Steven Kurtz

20:12 22 Aug 20

I'm not Pepsi, Amazon or any of their big clients...just a guy with a small, boutique private psychology practice 3,000 miles away in NY. Every time I need these guys they are there...got my project started ASAP, turn around time was amazing and best of all, any and all tweaks were handled ASAP until we got all the bugs out. Admittedly I was afraid we'd settle on a price and I might not be able to make edits. But no worries - each and every edit, debug done with a virtual smile and ASAP. Well worth the money and they won't disappoint. A pleasure to write a positive review for them!

Jerelene Tan

00:18 24 Feb 18

I have engaged Jacob to help me with an Aging Report. He quickly provided me with a solution which I didn't think of. I would highly recommend his service to anyone who needs excel help.

Demetrios Lambrou

03:30 17 Oct 17

I have been assisted once again, by Paul this time, quick and effectively, even though it has been a weekend. So many thanks!!!! Such a great support to Excel users and Excel programmers alike!

Mitch Goins

15:06 01 Aug 17

I had an Excel problem that I could not crack without extreme effort on my part. I contacted Excel and Access via email. I received a prompt response then described my problem and indicated that I really wanted to remunerate them for their (Christopher and Jacob) time and effort. They honed in on my problem description with only 2 very helpful emails that showed me how to solve my problem. I was very impressed and grateful. There was never a mention of any charges. They are consummate professionals. I can see why they are Microsoft MVP's.

Michael Bell

12:22 09 Jun 17

Chris and Paul were amazing. We hit them with a project last minute and they dropped everything and came through within hours. The work was done professionally and exactly as we needed and at a very reasonable price. We were very pleased.

Bret Linford

20:16 30 May 17

Excel and Access, LLC were super responsive. They really went above and beyond my expectations. They were able to customize directly to my needs. The automation they delivered is very fast and easy to use. I'd use them again in a second. AND they're nice people, too. ?

Chris DeSantis

19:10 07 May 17

Our experience with Excel and Access. LLC was excellent. Chris and his team of very skilled professionals were able to help us out immediately. After just a brief discussion of the project we hired them for, they completed the work and exceeded our expectations in just hours. I have used Chris and his team on numerous projects over the span of 10 years. I am consistently impressed with how quick the payback is, and how well his team understands my requirements and delivers a product that exceeds my expectations. His team is always available anytime of day or night to help or answer questions. We highly recommend Excel and Access, LLC for anyone searching for highly effective and easy to use productivity tools.

Jacqueline

13:10 21 Mar 17

I had the pleasure of working with Excel & Access on a very intricate Excel project involving a massive spreadsheet that housed years’ worth of data. After having two other firms take on the project and then bail, I was panicked and desperate to get the project completed in what was now a very tight time frame. I worked with Christopher and Jacob and couldn’t be more delighted with the level of customer service I received. Jacob is extremely knowledgeable, and also incredibly responsive. My e-mails were answered immediately and the project was completed quickly, ahead of my deadline, thanks to his working nights and even over the weekend to get it done. I felt as if I were the only client he was working with at the time! Jacob made a difficult, tedious assignment look like child’s play. Excel & Access are fabulous to work with and are now my go-to for any Excel need, large or small. Good customer service is difficult to come by, but they nail it! If you are looking for help with an Excel project, I recommend Excel & Access without reservation. You won’t be disappointed.

Leveraging Excel’s Data Model

BIG Data Excel Solutions leverage the Data Model and Power Pivot

Contact us for a Free Consultation Today.

BIG Data Needs a Data Model