Tool: xlDAX - an add-in for modern Excel
Before modern Excel I developed a .NET add-in called HAMMER to do many of the things that are now standard within Excel. And before that again, I used a mainly pure VBA add-in called microETL to do the same thing. When I say mainly VBA, it also used a number of C dlls (SQLite and Python) to add extra needed functionality, resulting in a lot of extra files accompanying the core VBA code.
HAMMER solved this explosion of files by wrapping the same functionality in a single-file, no-install, xll, but it was still a dll (an xll is a specialist form of a windows dll for loading by Excel), and in many corporate sites dlls of any nature are not welcome.
The post 2010 beefing up of Excel's data mangling capabilities, with Power Pivot, Power Query and MS Access driver, offered me a way out of my own dll hell. I did this by essentially re-engineering the microETL code base, to replace SQLite with a combination of Power Pivot, MS Access and Excel Tables, and replacing the need for a data scripting language with Power Query, the result is xlDAX.Read more...