Loading multivalued fields from Microsoft Access – not possible with Power Query

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