My Power BI is in the unfortunate habit of periodically crashing when trying to work with the Power Query editor.
When an action is taken in the editor, a small black 'command prompt' window appears on my screen, before all of Power BI crashes to desktop without the option of saving.
Luckily the 'retrieved files' function still works, but when a file is recovered in that way it requires a full refresh of all data and previews, which can take a long time.
Some notes:
- The error has appeared in several files and different reports.
- My PC is a desktop, 3.2 GHz , 64-bit Windows 10, 64 GB memory.
- The error seems to have appeared around the time I started using Power Query editor to accomplish more tasks. This was around 3 months ago. I have not reported it until now, as I have hoped that each new version of the desktop client would resolve the bug. None of them have helped.
My typical use is:
- The error typically occurs when I have been working intensively in the editor for a while (20 minutes of intensive work repeatedly changing queries and refreshing previews, or 60 minutes of infrequent use).
- One or more large tables merged and appended from several smaller tables, where the smaller tables have had 'enable load' un-marked. Sometimes this results in levels such as 'tbl_JanuaryWeek1' and 'tbl_JanuaryWeek2' appended into 'tbl_January', then 'tbl_January' and 'tbl_February' being appended into 'tbl_2019'. Only 'tbl_2019' is told to load once the query editor is applied and closed. This might proceed with 30 tables forming into 5 tables, then 1 large table.
- These tables are often pivoted to be 'tall' and 'thin'. The dataset are not usually extremely large; in their final form they range from 200,000 to 2 million rows, 20 columns, with a mixture of text and numbers.
- Data is usually (but not always) brought in from .xlsx files, where it is usually stored in excel tables (not imported as a whole sheet). The .xlsx files are almost always stored on sharepoint, in a shared 'documents' workgroup. The data source would typically list the address as 'https://abcde.sharepoint.com/sites/Analysis/Shared%20Documents/data/Results/mapping.xlsx' or similar.
- I make extensive use of the advanced query editor to copy, paste and edit text, but usually only replicating those functions available through buttons on the query editor ribbon, rather than advanced M.
Can anyone offer any advice? I thought that because I was using excel tables and not enabling the loading of tables into the data model, I was following good practice.
Thanks in advance.
