If you’re working with large data or complex queries that take a long time refresh, cancelling one of those refreshes can even take longer time, especially, if the query has run for quite some time already.
Luckily, there is an easy trick to cancel refresh without loosing the work you’ve done already:
Open the task manager (Ctrl + Alt + Del -> select Task Manager or rightclick the NavBar )
Select (one of) the “Microsoft Mashup Evaluation Containers” with a high CPU-usage (below the main process!)
Cancel refresh by ending a Container-task
Right-click mouse and choose “End task”.
A pop-up-message like this will appear – just click cancel (and don’t click “Report this issue” !!):
Cancel refresh, but don’t report an issue
Another scary message might appear as well – just click close:
There might also be a message that other refreshes are currently running and if you want to cancel them as well – choose cancel.
That’s all: All the changes you’ve made in the query editor and forgot to save before refreshing will be kept and you can continue your work from here.
If you query databases who support query folding, you’re probably very aware of every step you take and check if folding happens with every new step like so:
“View Native Query” shows folding query that’s send back to the server (if not greyed out)
Folding will (usually) happen as long as “View Native Query” isn’t greyed out.
So when doing an inner join on tables whithin the same database, I was a bit surprised to see this greyed out actually. As according to the literature, it should fold.
But guess what? After I expanded a column from it, the folding was back again:
Expanding column(s) brings back folding
Also I vaguely remember having seen queries fold, where it was actually greyed out. So I would always recommend to check the actual query in SQL Server Profiler before shouting at your screen.
You should use this technique as well when you actually don’t need any of the expanded columns, but just use the inner join to filter out records from your “left” table. Thanks to Ed Hansberry for pointing this out:
Yup. I found that out after trial and error when I was doing inner joins just to limit data in my first table and deleting the joined table with no expansion. Now I expand and then delete the expanded column. Folding continues!
The function below has just one parameter where you either fill in a full filename (incl. path) of an Excel file, or a folder path where multiple files reside. The function will automatically detect the right modus and spit out the M-code. Read more
When you import Excel sheets who have empty leading or trailing columns and rows (showing null-values), you can substantially improve the complexity and speed of your import process with a simple trick:
Remove the reasons for the empty trailing rows and columns 😉
Usually, when you import data from an Excel sheet, Power Query will automatically detect the used range in a sheet and will just return those rows and columns who have content in it. So how can it come that in some cases, additional rows or columns are returned who have nothing but empty values in them?
The “Inquire” Excel Add-On lets you clean any excess cell formatting. After you’ve executed this command, Power Query will not import any of those leading or trailing empty rows or columns any more. Often this will reduce the file size of the Excel files dramatically as well.
You will benefit from:
simpler query logic
potentially huge improved import speed, due to the reduced file size