3 ms·
Power Query, aka Get & Transform (built into Excel 2016), is probably the best new feature since Excel Tables were introduced in v2007. It lets you pull data fr
by chrispsn 9y ago
Power Query, aka Get & Transform (built into Excel 2016), is probably the best new feature since Excel Tables were introduced in v2007. It lets you pull data from local, network and web sources - including databases - with a relatively easy UI. The queries can also be refreshed.
My main complaint about spreadsheets (or at least Excel) is: you can't generate data of arbitrary length via a formula and display it on the grid without manually managing the cells it takes up. An example where this would be handy is an array formula for unique items.
It seems that maintaining location-based referencing as a feature (referring to data based on cell location, eg '=A1') makes this difficult to implement.
Power Query allows this via List.Generate and the M language, but you need to break the spreadsheet auto-calculation paradigm to use it.
This is why Mesh exists: https://github.com/chrispsn/mesh https://github.com/chrispsn/mesh