Article 19 What is better to use: ‘Power Query Data Model’ or ‘Microsoft Access’?

Summary

Data Models in Excel are used when there are two or more relatable models. By using a Data Model, one attempts to emulate what could otherwise be achievable using a relational database.

Here Microsoft Access can be considered a proxy for other relational databases.

This article considers the status of Data Models in Excel, and tends to conclude that for complex systems it would be more appropriate to link Excel with Microsoft Access.? This is because there are still many problems with the Excel Data Model; albeit it is hoped these problems will be fixed in due course.

Historic background

For some 35 years I have generally been comfortable with the integration of Microsoft Access and Excel.? I am still comfortable with this approach.

Indeed I was more competent in Access than Excel for many years – and only really started to consider Excel as a useful system (for complex systems) around 2009, and then only because the Company I then worked for advocated Excel (and largely ignorant of how to use Access).

I still consider Excel to be an unfinished product (with respect to complex systems) lacking obvious functionality.

Over time I started to give courses in Excel – primarily to engineers and scientists – and I had to learn more about Excel Pivot Tables.?

Pivot Tables are impressive and were incorporated in Excel in 1993; but I could not figure out what the advantages of Power Pivot were compared to Access integration.

One of the key issues I faced in trying to identify advantages/disadvantages were that many advocates of Excel didn’t compare Power Pivot with Access.? Instead, the Excel advocates appeared to largely be one-stop-shop advocates; rather than consider integration.? For example, consider the Microsoft (2012) reference.

I have only identified a few people in LinkedIn who see the advantages of integration.

I couldn’t identify when Data Models were made available in Excel – but 2013 appears to be an appropriate year. (Microsoft 2012).

In Excel, Data Models are effectively the same as Access Relationships Diagrams (a key functionality of Access from 1992).

Problems with the Excel Data Model

Now before I start to highlight the problems, it needs to be emphasised that if one looks at many of the available YouTube videos, they all share a common theme.? They tend to be advocates of the Data Model and lack critical insight.? Having said that, I find many of the available videos useful.? I have not carried out a detailed review of videos (as there are so many) nor have I reviewed available online courses.? It is not the purpose of this document to criticise specific instructors.

Now in trying to identify some of the problems we also need to note that Data Models is a functionality of Power Query. Power Query is also used in Power BI.

Here I highlight 4 identified problems – and to be fair I will also highlight some problems of Access.? I am also focusing on using the Data Model applied to available Excel Tables in a workbook.? Please bear in mind that although I am here giving opinions. I am more than keen to change my position if I am corrected.

Problem 1 Input to the Data Model

The Data Model focuses on using Excel structured Tables (XL Tables).? In contrast, when integrating data from Excel to Access, Access focuses on Named Ranges.

Problem 2? Power Query is largely dependent on Power Pivot

Whilst Power Query is used to generate a Data Model the resultant queries do not generate a Table in normalised format.? In contrast this is easily achieved using Access.? Similarly one can link tables in Excel using XLOOKUP.

Problem 3 Power Query does not allow Table names to change.

This problem I found bizarre.? If one creates a Data Model using available Table names, and then change the names of the tables, then the Data Model uses the original names.

Problem 4 Unwanted Tables are use in Power Pivot.

Figure 1 shows the Data Model based on two tables.

Figure 1 is a simple Data Model Diagram.

There is a third table (TableDetail_1) in Figure 2 which I made sure I removed from the Data Model.

I then select ‘Power Pivot’ and the unwanted table appears.

There appears to be reasons for this.? Excel attempts to help the unsuspecting user by identifying tables that Excel thinks should be added to the Data Model.? However, the reality is that this process causes confusion.

Compatibility problems between Excel and Access

There appears to be incompatible problems between Excel and Access.? For example, Access does not allow the Excel Data Model to be integrated into Excel, nor does Excel try to recognise the relationships diagram in Access.

Comments on VBA

In the early years, I tended to import Access data into Excel using VBA.? I found this relatively straightforward. Similarly exporting data from Excel to Access (either from Named Ranges or XL Tables) is also relatively straightforward.? So with the limitations of Power Query and getting data from Excel, VBA still appears to be a useful option.

Conclusions

Power Query, Data Models and Power Pivot in Excel provide a powerful and useful combination. However for complex systems, I would tend to encourage Access or other RDBMS.

Reference

Microsoft 2012.? Introduction to the Data Model and Relationships in Excel 2013 | Microsoft 365 Blog