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:

Image2a

Yeah, there is a function that just lets you pass in every delimiter-sign you want to use… just love Power Query.

Now we rename the columns that contain our parameters to ease further use:

Image4

So the query (name it “SplitAsYouLike”) would look like this:

let
Source = Excel.CurrentWorkbook(){[Name="Tabelle1"]}[Content],
Split = Table.SplitColumn(Source, "Spalte1", Splitter.SplitTextByAnyDelimiter({"\", "[", "_", "]", "."})),
SplitAsYouLike = Table.RenameColumns(Split,{{"Spalte1.5", "Year"}, {"Spalte1.6", "Month"}, {"Spalte1.9", "TypeOfAccounts"}})
in
SplitAsYouLike

Now we can grab our filter values whereever we want by adressing them like this:

Image6

Secondary usecases for the cell reference:

  • Grab the local filepaths when sharing workbooks : Maintain the first x columns then and re-merge them.
  • and some more that might be posted here, so stay tuned 🙂

Enjoy & stay queryious 🙂

 

Comments (5) Write a comment

  1. Hi Imke,

    A very useful example of using SplitTextByAnyDelimiter. From a practical standpoint for the average Excel user, in this particular instance it might be more straightforward to perform the parameter extraction in Excel (given that “C:\Users\Imke\Desktop\” is fixed)

    For example, to get the year parameter:

    =MID(Path,SEARCH(“\”, Path, 22)+1, 4)

    and the month parameter:

    =MID(Path,SEARCH(“\”, Path, 22+1)+1, 2)

    where “Path” is the name of cell containing the path. Not as exciting as SplitTextByAnyDelimiter though 🙂

    Reply

    • Hi Colin,
      thanks! Yes, M is still a challenge for many Excel users – so your example is much more direct.
      Stay queryious anyway 🙂

      Reply

  2. Actually, since the name length is variable (e.g “Imke”), we cannot use the previous formulas. Instead, we can use more flexible formulas that are based on finding the nth position of a character.

    The year formula is then:

    =MID(Path,SEARCH(“$”, SUBSTITUTE(Path, “\”, “$”, 4))+1, 4)

    and the month formula is:

    =MID(Path,SEARCH(“$”, SUBSTITUTE(Path, “\”, “$”, 5))+1, 2)

    Reply

  3. How easy would it be to split a column based on a list of words in a parameter file, which could be updated to split more columns as required?

    Reply

    • This shouldn’t be a problem at all. Just replace:
      {“\”, “[“, “_”, “]”, “.”}
      by the reference to the column of your parameter table (this will be interpreted as list).

      Reply

Leave a Reply