5 ms·

Excel has similar capabilities (since Excel 2010) in the form of PowerQuery[1]. It can pull in 3rd party data sources (including custom HTTP calls if you want

by cosmie 6y ago

Excel has similar capabilities (since Excel 2010) in the form of PowerQuery[1]. It can pull in 3rd party data sources (including custom HTTP calls if you want to reach out to an API), has data types, can both process and store substantially larger data volumes (data is cached/stored separately from the spreadsheet data in a compressed, columnar format and processed by a separate data processing engine), and every step of the data processing pipeline is shown and easily referenced/reviewed (including the ability to leave comments in the code). That said, PowerQuery is a graft onto Excel, originating in the SQL Server world. So "Excel" skills aren't very helpful/transferable; it leverages Microsoft's M language[2] instead of Excel functions, and the usage paradigm is far more natural for someone with a programming background than an Excel background. But as someone else mentioned in another comment, Alteryx is anything but cheap. PowerQuery is already widely distributed and available to anyone with Excel installed[3]. So for anyone looking for this type of operational discipline around data, it's a handy tool to pick up. Bonus points that PowerQuery/M-code is portable between a variety of Microsoft tools (SQL Server, PowerBI, Excel, and a variety of Azure's data services). [1] https://support.microsoft.com/en-us/office/power-query-overview-and-learning-ed614c81-4b00-4291-bd3a-55d80767f81d https://support.microsoft.com/en-us/office/power-query-overv... [2] https://docs.microsoft.com/en-us/powerquery-m/ https://docs.microsoft.com/en-us/powerquery-m/ [3] Traditionally only available for Windows, but the base tech has recently been added to Excel for Mac and is being brought up to parity.

hermitcrab 6y ago
I tried doing some joins in Power Query and was surprised how slow it is.
- cosmie 6y ago
  Join performance is a bit of a hairy topic. If you're pulling data from an external source that supports query folding[1] such as a database, it'll try to push those joins to the original source. If you're pulling in data from a source that doesn't (or the join occurs after query folding is no longer possible), there are a host of performance considerations/optimizations. By default PowerQuery knows nothing about your source data and makes no assumptions. If you're joining data using the GUI, the generated code will likely use the NestedJoin[2] function. A single "step" in the processing will have its ram capped at 256MB[3], so depending on the size of the data you're joining and absent of any data processing steps that would give it guarantees about your data, you may or may not be paging massive amounts of data to disk as it loops through the entire dataset you're joining for each row that is being joined. That said, PowerQuery has 6-7 join algorithms available, and a variety of techniques that can be used to optimize the processing. For example, if you add a primary key to the table (via Table.AddKey or Table.Distinct or Table.Group), it can short-circuit the data processing the moment it finds a match. This[4] article series is really helpful for optimizing joins in particular and the link-outs and tidbits scattered within the series are fantastic for getting a better understanding of PowerQuery's inner workings and performance considerations. [1] https://docs.microsoft.com/en-us/power-query/power-query-folding https://docs.microsoft.com/en-us/power-query/power-query-fol... [2] https://docs.microsoft.com/en-us/powerquery-m/table-nestedjoin https://docs.microsoft.com/en-us/powerquery-m/table-nestedjo... [3] Mentioned halfway down this article: https://blog.crossjoin.co.uk/2019/04/21/power-bi-dataflow-container-size/ https://blog.crossjoin.co.uk/2019/04/21/power-bi-dataflow-co... [4] https://blog.crossjoin.co.uk/2020/05/31/optimising-the-performance-of-power-query-merges-in-power-bi-part-1/ https://blog.crossjoin.co.uk/2020/05/31/optimising-the-perfo...
blendo 6y ago
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#?
- contextfree 6y ago
  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.
- LegitShady 6y ago
  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.
- cosmie 6y ago
  > 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. [1] https://docs.microsoft.com/en-us/office/dev/add-ins/reference/overview/excel-add-ins-reference-overview https://docs.microsoft.com/en-us/office/dev/add-ins/referenc... [2] https://docs.microsoft.com/en-us/powerquery-m/excel-currentworkbook https://docs.microsoft.com/en-us/powerquery-m/excel-currentw... [3] https://docs.microsoft.com/en-us/powerquery-m/accessing-data-functions https://docs.microsoft.com/en-us/powerquery-m/accessing-data... [4] 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...
benhurmarcel 6y ago
> PowerQuery is already widely distributed and available to anyone with Excel installed Looks like you need to install it separately as a module, it's not included with base Excel. So every user needs to download it, and have admin rights to install it. That makes it a non-starter in many of the corporate use-cases.

by cosmie 6y ago

hermitcrab 6y ago

I tried doing some joins in Power Query and was surprised how slow it is.

cosmie 6y ago
Join performance is a bit of a hairy topic. If you're pulling data from an external source that supports query folding[1] such as a database, it'll try to push those joins to the original source. If you're pulling in data from a source that doesn't (or the join occurs after query folding is no longer possible), there are a host of performance considerations/optimizations. By default PowerQuery knows nothing about your source data and makes no assumptions. If you're joining data using the GUI, the generated code will likely use the NestedJoin[2] function. A single "step" in the processing will have its ram capped at 256MB[3], so depending on the size of the data you're joining and absent of any data processing steps that would give it guarantees about your data, you may or may not be paging massive amounts of data to disk as it loops through the entire dataset you're joining for each row that is being joined. That said, PowerQuery has 6-7 join algorithms available, and a variety of techniques that can be used to optimize the processing. For example, if you add a primary key to the table (via Table.AddKey or Table.Distinct or Table.Group), it can short-circuit the data processing the moment it finds a match. This[4] article series is really helpful for optimizing joins in particular and the link-outs and tidbits scattered within the series are fantastic for getting a better understanding of PowerQuery's inner workings and performance considerations. [1] https://docs.microsoft.com/en-us/power-query/power-query-folding https://docs.microsoft.com/en-us/power-query/power-query-fol... [2] https://docs.microsoft.com/en-us/powerquery-m/table-nestedjoin https://docs.microsoft.com/en-us/powerquery-m/table-nestedjo... [3] Mentioned halfway down this article: https://blog.crossjoin.co.uk/2019/04/21/power-bi-dataflow-container-size/ https://blog.crossjoin.co.uk/2019/04/21/power-bi-dataflow-co... [4] https://blog.crossjoin.co.uk/2020/05/31/optimising-the-performance-of-power-query-merges-in-power-bi-part-1/ https://blog.crossjoin.co.uk/2020/05/31/optimising-the-perfo...

blendo 6y ago

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#?

contextfree 6y ago
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.
LegitShady 6y ago
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.
cosmie 6y ago
> 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. [1] https://docs.microsoft.com/en-us/office/dev/add-ins/reference/overview/excel-add-ins-reference-overview https://docs.microsoft.com/en-us/office/dev/add-ins/referenc... [2] https://docs.microsoft.com/en-us/powerquery-m/excel-currentworkbook https://docs.microsoft.com/en-us/powerquery-m/excel-currentw... [3] https://docs.microsoft.com/en-us/powerquery-m/accessing-data-functions https://docs.microsoft.com/en-us/powerquery-m/accessing-data... [4] 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...

benhurmarcel 6y ago

> PowerQuery is already widely distributed and available to anyone with Excel installed Looks like you need to install it separately as a module, it's not included with base Excel. So every user needs to download it, and have admin rights to install it. That makes it a non-starter in many of the corporate use-cases.