3 ms·

Excel has an entire ETL engine called PowerQuery tucked up under the hood. Originally part of SQL Server Analysis Services, it got strapped into Excel about a d

by cosmie 6y ago

Excel has an entire ETL engine called PowerQuery tucked up under the hood. Originally part of SQL Server Analysis Services, it got strapped into Excel about a decade ago, and more recently tons of other products[1]. The Web[2] connector is what you're after. It can consume a variety of formats, including csv, xml, and json. It supports HTTP Basic Auth pretty well, but if you want to put the data source behind oauth it can get tricky (it technically supports it but depends on how novel the implementation is). Credentials sit with the (Excel) client, so if the file gets shared with a new user it'll prompt them for authentication details when they first attempt to refresh it. It'd be pretty easy to set up a template/turnkey workbook you can just hand over to new clients with everything all set up and all they have to do is enter their credentials when prompted. Only caveat is Excel for Mac - it only gained Power Query about a year ago, and porting it over is a very involved[3] process including an entire .NET Core rewrite and factoring out Windows dependencies from legacy cruft. The Web connector is one of the many that haven't made it over to the Mac yet, so any clients using a Mac would still need an alternative. [1] https://docs.microsoft.com/en-us/power-query/power-query-what-is-power-query#where-to-use-power-query https://docs.microsoft.com/en-us/power-query/power-query-wha... [2] https://docs.microsoft.com/en-us/power-query/connectors/web https://docs.microsoft.com/en-us/power-query/connectors/web [3] https://devblogs.microsoft.com/dotnet/using-net-core-to-provide-power-query-for-excel-on-mac/ https://devblogs.microsoft.com/dotnet/using-net-core-to-prov...

amichal 6y ago
Thank you for this!. It's why i continue to read HN.
- cosmie 6y ago
  My pleasure! Feel free to reach out, you have any questions (email in profile). I'm not an expert on it, but I am a technical person currently working on the business side of the IT fence. And I frequently do (data-engineering heavy) consulting for clients who are similarly on the business side of IT. So none of the nifty ETL tools, data storage capabilities, or computing environments I have available when I'm on the IT side. Power Query is a godsend in that scenario. It's a well featured ETL engine with integrations for a variety of systems, services, and databases (including generic JDBC/ODBC support). And has the ability to use direct HTTP calls when that's more appropriate/useful. It also stores the data internally in a highly-compressed and optimized columnar store, independent of the "Excel data" on sheets. Which you can then either sync to a worksheet, or leave it in a state where the raw data isn't visible but can be accessed through a pivot table connected to it. So you can abuse it for far heavier work than you'd expect to be able to do in Excel. And it's already there, sitting on virtually every business person's computer everywhere. Completely sidesteps the security, IT, procurement, and legal hassles you have to jump through to get a proper system. Not to mention user training - you can architect things in a way where all users have to do is maybe tweak a cell or two, then hit the Refresh button that's in the Ribbon. So the complexity of "learning something new" is completely absorbed on your side, and you're free of pesky support questions and hassles since you isolated them from being the new stuff (also making it harder for "accidental/I didn't press anything!" changes from users). It's not perfect by any means and has a number of warts, usually falling short of more purpose-built solutions when those are options. But it's still pretty solid, and on balance has saved me far more frustration than it's caused. And without having to dip into the dreaded world of macros and VBA.

by cosmie 6y ago

amichal 6y ago

Thank you for this!. It's why i continue to read HN.

cosmie 6y ago
My pleasure! Feel free to reach out, you have any questions (email in profile). I'm not an expert on it, but I am a technical person currently working on the business side of the IT fence. And I frequently do (data-engineering heavy) consulting for clients who are similarly on the business side of IT. So none of the nifty ETL tools, data storage capabilities, or computing environments I have available when I'm on the IT side. Power Query is a godsend in that scenario. It's a well featured ETL engine with integrations for a variety of systems, services, and databases (including generic JDBC/ODBC support). And has the ability to use direct HTTP calls when that's more appropriate/useful. It also stores the data internally in a highly-compressed and optimized columnar store, independent of the "Excel data" on sheets. Which you can then either sync to a worksheet, or leave it in a state where the raw data isn't visible but can be accessed through a pivot table connected to it. So you can abuse it for far heavier work than you'd expect to be able to do in Excel. And it's already there, sitting on virtually every business person's computer everywhere. Completely sidesteps the security, IT, procurement, and legal hassles you have to jump through to get a proper system. Not to mention user training - you can architect things in a way where all users have to do is maybe tweak a cell or two, then hit the Refresh button that's in the Ribbon. So the complexity of "learning something new" is completely absorbed on your side, and you're free of pesky support questions and hassles since you isolated them from being the new stuff (also making it harder for "accidental/I didn't press anything!" changes from users). It's not perfect by any means and has a number of warts, usually falling short of more purpose-built solutions when those are options. But it's still pretty solid, and on balance has saved me far more frustration than it's caused. And without having to dip into the dreaded world of macros and VBA.