Tip for Parameter Tables in Power Query and Power BI

Parameter tables in Excel are normally set up like this:

Bild1

In order to retrieve the currency for example, you could write this:

= Parameters[Value]{2}

This returns the content of the “Value”-column of the query named “Parameter” that sits in the 3rd row (! Power Query starts to count at zero): EUR. Which is not bad, but also not ideal, because you have to count the number of rows. Actually, over time this method could cause problems: So if you decide to add a parameter in the second row for example, all parameters referenced by numbers after that would need to be adjusted.

So this way is easier and more robust: After you’ve passed your parameter table to Power Query, transpose it and promote the headers.

Bild2

This will always return the “Currency”-Parameter, no matter where it’s located within the Parameter table and it is also easier to write.

= BetterParameters[Currency]{0}

Your column (sitting in [ ]) is the name of the parameter and the line is always the first (Power Query language: {0}).

BetterParameters1.xlsx

 

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