3 ms·
> If I then want to share a dataset with a colleague and email it to them, the easiest friction-free way is to attach an .xlsx file. Sending them an email with
by Closi 5y ago
> If I then want to share a dataset with a colleague and email it to them, the easiest friction-free way is to attach an .xlsx file. Sending them an email with attachments of SQlite .db file + executable app for Windows/macOS is much more cumbersome. The alternative of sending them a link to a cloud-based "database-as-worksheet" SaaS platform just creates another set of problems. An all-in-one db+gui tool like MS Access also isn't really an option since it doesn't have the same powerful GUI data manipulation as Excel.
The alternative is to load the sqlite DB into Excel via PowerQuery and then share the file, which will maintain type safety of all columns through the excel data model.
This provides much better GUI data manipulation too, as you can define relationships between the data in the model e.t.c.
The problem is I would guess less than 1% of Excel users actually understand this functionality, but it is absolutely core to doing proper analysis in excel (not saying you don't use it - you probably do - but lots of users don't!).
- hn_version_0023 5y ago> The problem is I would guess less than 1% of Excel users actually understand this functionality Do you have any recommendations for getting started with this?
- hn_version_0023 5y agoTo answer my own question: looks like all you need is Excel 2016 or better. https://powerquery.microsoft.com/en-us/excel/ https://powerquery.microsoft.com/en-us/excel/
- Closi 5y agoAbsolutely! I would add though that you really need to follow a tutorial/book to make everything make sense in it. I read a book by Rob Collie - but any well reviewed book on DAX should do it. The book “Supercharge Excel - When you learn DAX…” looks like it covers similar information but more up to date. The analysis pattern is usually importing data and cleaning it in PowerQuery, then building relationships between tables in the data model view, and then analysing it via a ‘PowerPivot’ Table. PowerPivot then has new functionalities compared to a regular pivot table (eg measures) that allow for automatically calculating columns based on context and some additional capabilities you don’t get in excel.
- hn_version_0023 5y agoThank you kindly; I’ll look into your suggestions!
- cupofpython 5y agoI do this all the time. Excel is a fantastic data GUI for ad-hoc reporting. My typical quick turn-around process is: type SQL in text editor, test sql in database, create a view, connect to the view from excel, use native excel features to display whats needed. Usually I create a summary page as well which uses sum-ifs and such on the query result for the high level detail rather than go through the SQL process for it separately