Conditions in FirstN, LastN and other xN-functions in M, PowerBI and Power Query

Today I discovered that we can use conditions in many of the N-selecting functions where one/I would normally expect just a number-expression for the N:

Table.RemoveFirstN( table as table, optional countOrCondition as any)

So apart from being able to select a certain number of rows to be removed, we can pass a condition (as function). This condition will iteratively be checked for every row in the table (from top or bottom) and as long as every (next) step returns true, the resulting range will be removed. So as soon as one row breaks the condition, the process will stop.

I find that totally awesome, as we can now remove all top-rows who have an empty field in Column3 like this for example:

Table.RemoveFirstN(<MyTable>, each each (_[Column3] = null or _[Column3] = “”))

Yes, this will remove the first sequence of consecutive nulls in the table. So all other rows with nulls in the table coming later after a non-null value has “broken in”, will remain.

This is the list of function, where you can use this M-agic:

Enjoy & stay queryious 🙂

How to auto-parametrize your Power Query queries

This is about a technique that I’m going to use in my upcoming articles on ICT reconciliation. But as it is useful in other areas as well, it’s getting its own post here:

Say we want to pass the year and month as well as the type of accounts as filters/parameters into our reconciliation query. If our file is stored here:

C:\Users\Imke\Desktop\2015\08\\ICTRec201508_Transactions.xlsx,

we have everything we need. And when the next month comes, we even don’t have to adjust our query, as it will automatically take 09 as the months parameter, providing we store it in the correct folder.

Using: CELL(“filename”) will extract this information into the cell in Excel. Check this cell and pass it to Power Query (as table).

Image1

Now we just have to extract the relevant parts, using an ultracool ninja-trick I just picked up in the TechNet Forum:

Read more

Use Timeline or Slicers to filter your Power Query import

A question in the Mr. Excel forum this morning reminded me that the technique I’ve blogged about here could also be used to do simpler things like using a timeline slicer to filter your Power Query imports. So here’s a quick shot on this:

  1. Import your calendar table into the data model (load only)
  2. Create a pivot on it with just one field: Date
  3. Put your timeline on it
  4. Return the resulting filtered pivot back to PQ by using an offset-function in a named range (no way to push this pivot directly back into PQ)
  5. Import the table to be filtered in the next step and merge in modus: JoinKind.Inner. This will only return the rows that have a match on both sides, thereby act as the filter we want. As this will allow query folding to happen (speed up your queries if accessing a SQL relational DB), do this as your first step before doing any other transformations on your source data.

Have a look at the file:

TimelineFilterPQ.xlsx

 

You will also find some exercises on filters on multiple columns in there if that is of interest.

Enjoy & stay queryious 🙂