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 (7) 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

Leave a Reply