One thing I’m missing in Excel is the ability to define an auto-updating table that is basically an SQL-like view joining/filtering/projecting/grouping data from some other tables in the same Excel file. Or is there actually a way to do this? I understand that Power Query allows to do something like that with an external data source. Being able to have the result of a spillover formula automatically form a table (or a named range) would also be helpful.
PowerQuery can also use ranges inside the same worksheet as data sources. You can define either cell ranges or named tables as data sources and then use them in PowerQuery just like you would use any external data source.
This is a standard use case for ETL (extract load transform) tools such as Easy Data Transform at the budget end (which i wrote) or Alteryx at the corporate end.
I want the expressiveness of SQL queries within a single Excel file, so that data entry and derived views are within the same tool and same file. Also, EDT doesn‘t look like it can do joins?
