Chapter 21 · 21.2
Excel files with Power Query or Power Pivot: what works in Euro-Office and what doesn't
Many businesses have Excel reports that fill themselves: a sales overview that pulls data from the till system, a timesheet report that reads a folder of exports every month, or a pivot table based on a large data model. In Excel, this is called Power Query (getting and transforming data) and Power Pivot (the data model, with DAX formulas). If you put a file like this in your WebOké Nextcloud, the question is: does it also work in Euro-Office?
The honest answer
No. Euro-Office has no Power Query, no Power Pivot, no data model and no DAX. You can open such a file and look at the figures: you see the values as they were last retrieved in Excel. You can't refresh them.
What happens to the queries if you edit the file in Euro-Office? We checked this in the demo with an xlsx containing a data connection and a query table, like the ones Power Query creates: after a change and saving in Euro-Office, the connection and the query table were still in the file. But a real report from Excel can contain much more, such as a Power Pivot data model, and there are reports elsewhere of parts of it being lost after saving. In any case, Euro-Office can't display, check or refresh these parts. So treat a file like this as an Excel file: viewing is fine, but make your edits in a copy or in Excel.
How to recognise such a file
- In Excel, there is a list of queries under Data, or Excel asks when opening whether you want to refresh the data.
- There are worksheets with a table that fills "by itself", and nobody types in it.
- Pivot tables are based on a "data model" instead of a range in the file.
- Ask the person who made it. If you're not sure, treat the file as one with queries.
How to work: Excel and Euro-Office side by side
Example: Bloemisterij Het Zonnetje has a monthly report that the bookkeeper updates in Excel. The owner wants to view the figures in the browser.
- View only. Opening and reading works fine. Don't change anything: Euro-Office saves every change automatically (see Saving: how automatic saving works).
- If you want to change something or add calculations, make a copy first: open the file, click File and choose Save Copy As, or copy the file in your Nextcloud. Work in the copy. The original stays for Excel.
- Share the original as read-only with colleagues who don't use Excel (see Sharing a document with a colleague), so nobody edits it in Euro-Office by accident.
- Updating is done by the creator in Excel on their own computer, using the Nextcloud desktop app (see Working with the Nextcloud desktop app (synchronisation)).
Switching without Power Query
If you want to be able to update the report entirely in Euro-Office, build it differently:
- Get Data: on the Data tab you'll find the Get Data button. Use it to import a TXT or CSV file from your computer or from a web address, or an XML file, into your worksheet. There is no query editor with clean-up steps like in Power Query.
- Export the data as CSV from your till, accounting or webshop system and paste it into a fixed worksheet (see Importing and exporting CSV files and Opening a CSV file with semicolons and strange characters correctly).
- Do the calculations with regular formulas such as SUMIFS, or with a pivot table based on that worksheet (see Pivot tables).
- Data from another spreadsheet in your Nextcloud can be pulled in with a link (see Linking cells to another spreadsheet in your Nextcloud).
- The same clean-up steps every time (removing columns, splitting text) can be automated with a macro: View tab, Macros button (see Een rij automatisch naar een archiefblad verplaatsen als de status Klaar is and Een VBA-macro zelf omzetten naar JavaScript: de vertaaltabel).
Pitfalls
- One click is enough. Automatic saving means that even a small change, such as widening a column, saves the file again. If something goes wrong, restore the previous version from the version history (see Version History: restoring an older version).
- Excel and Euro-Office at the same time: if the bookkeeper works in Excel via the desktop app while you open the file in the browser, you end up with two versions. Agree on who works in the file and when (see Saving or syncing isn't working).
- Macros: if the file also contains VBA macros (xlsm), read An Excel file with VBA macros: what now? as well.
