4 ms·

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 you

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.

cosmie 6y ago
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. [1] https://bengribaudo.com/blog/2017/11/17/4107/power-query-m-primer-part1-introduction-simple-expressions-let https://bengribaudo.com/blog/2017/11/17/4107/power-query-m-p... [2] https://docs.microsoft.com/en-us/powerquery-m/power-query-m-function-reference https://docs.microsoft.com/en-us/powerquery-m/power-query-m-... [3] https://docs.microsoft.com/en-us/power-query/connectors/ https://docs.microsoft.com/en-us/power-query/connectors/

cosmie 6y ago

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. [1] https://bengribaudo.com/blog/2017/11/17/4107/power-query-m-primer-part1-introduction-simple-expressions-let https://bengribaudo.com/blog/2017/11/17/4107/power-query-m-p... [2] https://docs.microsoft.com/en-us/powerquery-m/power-query-m-function-reference https://docs.microsoft.com/en-us/powerquery-m/power-query-m-... [3] https://docs.microsoft.com/en-us/power-query/connectors/ https://docs.microsoft.com/en-us/power-query/connectors/