Posts in Category ‘little Tipps&Tricks’

How to cancel your Power Query refreshes fast in Power BI and Excel

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:

Step-by-step-instruction

  1. Open the task manager (Ctrl + Alt + Del -> select Task Manager or rightclick the NavBar )
  2. Select (one of) the “Microsoft Mashup Evaluation Containers” with a high CPU-usage (below the main process!)

    Cancel refresh by ending a Container-task

  3. Right-click mouse and choose “End task”.
  4. 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

  5. Another scary message might appear as well – just click close:
  6. 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.

Enjoy & stay queryious 😉

Query folding on JoinKind.Inner gotcha for Power BI and Power Query

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.

Edit:

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:

Enjoy and stay queryious 😉

Bulk-extract Power Query M-code from multiple Excel files at once

Some time ago I published a function that extracts all M-code from Power BI (.pbix)-files. Today I publish the pendant to Bulk-extract Power Query M-code from multiple Excel-files at once. The code contains many elements from the before mentioned, so please refer to that article for reference.

How to use

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

Improve import of Excel sheets with empty rows and columns in Power Query and Power BI

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 😉

Background

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?

Reason

The reason for it can be cell formatting of empty cells. They often occur in old workbooks where cells have been deleted. These cells will be returned with a null-value during the import process with Power Query. See this blogpost for more details of potential pitfalls that come with it.

Solution

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.

Effects

You will benefit from:

  • simpler query logic
  • potentially huge improved import speed, due to the reduced file size

Enjoy and stay queryious 😉

Import data from multiple SharePoint lists at once in Power BI and Power Query

This is a quick walkthrough on how you can easily import multiple SharePoint lists at once, just like the import from folder method.

Start as usual

You start your import like this:

Pass the URL to the folder where your lists are located:

In the next step you would normally choose all the multiple SharePoint lists you want to import:

Read more