Test Filters in M Code

Power Query M Code Showcase
1

Hello all, I just wanted to share a little trick that I use when building out solutions from large datasets.

Problem:
I am working with sales data, coming from SQL databases, and I have little to no technical assistance with the structure of the databases. Occasionally, I have to dig through my code to make sure that it’s doing what it is supposed to be doing with a sales document.

Solution:
a TESTING filter in my M Code

As I’m building out my dataflow (or this can be done in the dataset if you prefer), I add a parameter called “TESTING”, and set this to TRUE/FALSE.

I also add a manual-entry table called “TestDocs”, and the second step of this table is to convert it to a list.

Then in each relevant query of my solution, I add this line of code (updated to handle the column name if needed), immediately after my Navigation step - this can be done later in the code if you choose, but I find right after the Navigation to be appropriate.
it is important to know here that my M code is always updated to change the Navigation step of the SQL Table name to “getTable”

TestStep = if TESTING = true then
Table.SelectRows( getTable, each List.Contains( TestDocs, [Document No]))
else getTable,
// make sure TESTING is set to FALSE before publishing

My data is now filtering or not, based on the setting of the TESTING paramter.
make sure to include a warning note to yourself or other users before doing this.

Bonus: this filtering and use of the parameter will actually let you maintain query folding if that is something you are worried about.

Because I set this up in all of the tables that should have this, I can quickly filter my dataset down to one or more appropriate rows, to quickly debug or use as a proof that the data is working as intended.

2 Likes
2

replying to mark as solved - but also to note:

in a recent solution, I needed to be able to debug Shipment Headers, Invoice Headers, and Credit Memos - my TestDocs table can contain any or all of these document types, and my tables will filter to only the relevant ones (if there are no Invoices in the TestDocs - my invoice table Header table will return a blank).

I could also do this with more than one Test parameter and Docs table, but then you must check multiple Test parameters before publishing.

1 Like
3

Hi @Heather,

For small sets and/ or folding is not required, you could also write:

let
    TestStep = Table.SelectRows( getTable, each 
      if TESTING = true 
      then List.Contains(TestDocs, [Document No]) 
      else true
    )
    // make sure TESTING is set to FALSE before publishing
in  TestStep

.
Here’s a technique you can explore for “more complex filters” Note that this requires values not to be shared between columns but be unique to one specific column:

And how to incorporate a filter validation step:

Just contributing to the filter fun… :wink:
Cheers!

3 Likes