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 🙂

Comments (8) Write a comment

  1. Hi Imke,
    I’m trying to wrap the management of my parameter tables in functions. But I hit a snag. In this pseudo code, I’m trying to specify a column name via a variable. Kindly comment. Many thanks.
    let
    ParmTable = GetOAParameters( ), // Loads and transposes the table per Imke
    ParmName = “DAYS_OF_HISTORY”,
    ParmValueInvalid = ParmTable[ ParmName ] {0}, //<–Is this simply not allowed or am I missing something ?
    ParmValueValid = ParmTable[ DAYS_OF_HISTORY ] {0}

    in
    ParmValueValid

    Reply

    • Hi Nin,
      All good, just that you cannot reference a variable within square brackets. So you have to write it like this instead:
      Record.Field(ParmTable, ParmName){0}

      Actually, meanwhile I’m not using this table, but instead pushed it one step further and transformed it into a record. Then the syntax is even simpler.
      In your case you’d do that by selecting the first row of your ParmTable: GetOAParameters(){0}
      Then you call parameter just like this: ParmTable[“YourParameterName”] in the hardcoded version or with variables like this: Record.Field(ParmTable, ParmName)
      Yes, it’s almost the same like above, just without the {0} (fingerbreakers 🙂 )

      Reply

  2. Hi Imke,
    Haha. While I was away I coded the following:

    /* Outer scope */
    let
    ParmTable = GetOAParameters( ),
    ParmValue = GetOAParmValue( ParmTable as table, “DAYS_OF_HISTORY” ) as any
    in
    ParmValue

    /*
    let GetOAParameters = () as nullable table =>
    let
    Source = Excel.Workbook(File.Contents(“C:\Users\Nin Sute\Documents\OA\OAParameters.xlsx”), null, true),
    ParmTable = Source{[Item=”OptionsAwareParameters”,Kind=”Table”]}[Data]
    in
    ParmTable
    in
    GetOAParameters

    let GetOAParmValue = ( ParmTable as table, ParmName as text ) as nullable any =>
    let
    LocalParmTable = Table.SelectRows( ParmTable, each [ Name ] = ParmName ) as table,
    ParmValue = LocalParmTable[ Value ] { 0 }
    in
    ParmValue
    in
    GetOAParmValue
    */

    Seems very similar. Let me know your thoughts 🙂

    Reply

    • I’m not a programmer and as a seasoned Excel-user feel pretty comfortable with nested functions. So my preference is to make the code as short as possible (knowing that this might make programmers cry 🙂 ).
      It’s great that there are so many different ways in M to achieve the same result, so there is room for so many different styles as well 🙂

      Reply

  3. Thanks Imke. I’ve used parameter tables occasionally, probably not as often as I should have though, and when I do I always have to go hunting for how to use them as it never seems to stick for me. In particular I’ve always been mystified as to why a function was needed. For what exactly? Maybe I’ve misunderstood, again, but I think what you suggest here dispenses with a function. Doesn’t it? It does! What I’ve wanted to do many times but was thrown into coding functions which always seemed a diversion to me, is massively simplified and direct with this. You just call the table, heading and there your parameter is the only item in that column. I think I might even remember this.

    Reply

Leave a Reply