From the M-code link:
“A core capability of Power Query is to filter and combine, that is, to mash-up data from one or more of a rich collection of supported data sources. Any such data mashup is expressed using the Power Query M Formula Language. It's a functional, case sensitive language similar to F#.”
Does M-code serve as a replacement for (the abysmal) VBA? And how similar is it to F#?
> Does M-code serve as a replacement for (the abysmal) VBA?
Sort of depends. M (and PowerQuery in general) is fundamentally about data processing. If you were only dropping into VBA for ETL purposes, then chances are you can use PowerQuery/M instead. With the advantage that it exists in a standard xlsx file, rather than the macro-enabled xlsm files that are more heavily restricted in many environments. It's not intended for more general purpose tasks, nor can it do stuff like interact with the Excel object model directly, so you'd still need to use VBA (or if targeting Excel 2016 and above, Javascript[1]) for that.
PowerQuery/M is basically a sidecar app within Excel, and doesn't have access to nor is aware of the Excel object model. Excel.CurrentWorkbook[2] is just a convenience function available when used within Excel, and it treats data from the current workbook the same as if it was pulling from an external workbook or csv file or web API or any other source. To the extent that even when solely accessing data from within the same workbook and not calling out externally at all, it prompts users with an "Enable External Connections" warning to use. You can then either have the output of that sync to a Table object within Excel (and easily/generally available for use), or made available from within the data model (only available via the object model or pivot tables).
That said, the data access functions[3] are fairly expansive, and you can get pretty creative in how you (ab)use them. For example, Web.Action is one I've abused often: pinging a web service to track usage, triggering external jobs, and exporting/syncing data to an external service. Although those last two uses require a bit of complexity in architecting, to account for cases when it gets manually refreshed/triggered in quick succession or with duplicate data.
> And how similar is it to F#?
I'm not familiar enough with F# to be able to answer that. But if you are, you can probably find out pretty quickly via this[4] article series. PowerQuery/M is used across a variety of Microsoft products, and most resources around it are akin to product-specific "how-tos". Ben's primer series is the only source I've come across that approaches it from a software engineering perspective.
Thanks for this great explainer. I discovered PowerQuery almost be accident (trying to just do a simple join).
The capabilities were nice (and according to your posts more extensive than I know!) but the interface was so beyond counterintuitive, no discoverability, so unlike any of the variety of environments I've encountered in 10 years of programming, and the performance was painfully slow (compared to an RDBMS).
I have hopes for MS moving Excel to a less unique interface (I heard smooth scrolling is coming!) and I will definitely be reading your links.
Yea, discoverability is a pain. Although importing data from a CSV file has migrated from their obtuse "Text Import Wizard" to PowerQuery in the most recent versions of Excel, so more people will be stumbling on it.
And also yes, the Power Query Editor interface sucks. It's intended to be intuitive for Excel users, which inversely makes it a bit counterintuitive for someone coming from a dev background. The GUI functionality is also pretty limiting and only exposes a fraction of the overall capabilities. That said, there's an "Advanced Editor" button in the Ribbon that'll open up a modal that's far more familiar for a dev; shows you all of the code/Steps for the query all at once and has IntelliSense for code completion. Still crude as far as a dev environment goes, but far more productive than flailing around in the GUI the whole time.
Since you're coming from a dev background, I'd highly encourage that M primer[1] I referenced. It's a crash course on everything that's relevant to a developer (type system, error handling, patterns, antipatterns, custom functions, execution logic, etc). Between that and glancing over the standard library[2], you can avoid using the GUI entirely except for when it's convenient (such as scaffolding out some tedious stuff to then clean up in the Advanced Editor).
> and the performance was painfully slow (compared to an RDBMS).
Also of note is that Excel is only one of the data sources PowerQuery supports. There are a ton of others[3], including many RDBMSs natively supported and generic ODBC support if you have the appropriate driver installed.
It's not really comparable to VBA, as it's not used to automate/script the Excel object model. There is a JavaScript scripting model in recent releases which at least overlaps in use cases with VBA but I'm not sure if you can use it to do everything that VBA can.
M code is the query language mostly used for getting the data into power query and transforming it/cleaning it. The replacement for VBA for power pivot (as well as analysis services and power bi) is DAX (Data Analysis expressions). I've never used F# before.
Does M-code serve as a replacement for (the abysmal) VBA? And how similar is it to F#?