Parameter tables in Excel are normally set up like this:
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.
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 🙂
Thank you Imke. Elegant solution, as usual. 🙂
Thank you 🙂
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
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 🙂 )
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 🙂
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 🙂
Pretty cool, thanks.
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.