Unlike most of my articles, this one doesn’t solve a problem, it highlights one. If you use Microsoft Access as a data source to import data via Power Query, this article should make you aware of a limitation when importing so-called ‚multivalued fields‘.
What are multivalued fields in MS Access?
‚Multivalued fields‘ are a special feature of MS Access (SQL Server does not have this feature). These allow to store not only one value but up to 100 values in one field of a single record. Click here for examples and to learn how to create them.
What if you want to import those fields with Power Query?
I don’t want to drag this post out unnecessarily. As you can see from the title, there is no way to import ‚multivalued fields‘ from MS Access using Power Query. These fields are simply ignored during import.
Because I didn’t know if I was simply missing something here – e.g. an unknown parameter of the Access.Database() function – I contacted Curt Hagenlocher of the Power Query team. He confirmed that there is ‚no way to import values of this type‚ using Power Query. Thank your Curt, for this statement.
Conclusion
Since I still see it relatively often in the self-service area that MS Access is used as a tool for data entry and processing, my advice at this point is: If you develop the Access database yourself (i.e. you can design it according to your own ideas) and can already foresee that it will be accessed via Power Query, avoid the use of ‚multivalued fields‘. If you want to know how to replace ‚multivalued fields‘ with an adequate modeling, this article will show you how.
Since we are already in the middle of December: Have a great Christmastime and a happy new year,
Lars
Lars ist Berater, Entwickler und Trainer für Microsoft Power BI. Er ist zertifizierter Power BI-Experte und Microsoft Trainer. Für sein Engagement in der internationalen Community wurde Lars seit 2017 jährlich durch Microsoft der MVP-Award verliehen. Lies hier mehr…
sam meint
There are lots of other things you cant do if you use Access as a data source
a) You cant import a Union Query – you would need to build a separate SELECT * query on the Union query
b) you cant connect to a password protected DB with the Access Connector – you would have to go via the ODBC / OLEDB connector – But you cant do a scheduled refresh
The list goes on and on