Forum Discussion
How to create a parameter library to use in different workbooks with PQ
I took it a step further and give you an example.
I created this little table in Excel
I created the following query in aanother workbook
let // Connect to the parameter workbook. Source = Excel.Workbook(File.Contents("C:\.....\How to create a parameter library to use in different workbooks with PQ.xlsx"), null, true), // Access the ParameterTable ParameterTable_Table = Source{[Item="ParameterTable",Kind="Table"]}[Data], // Transpose it: Rows become columns and vice versa #"Transposed Table" = Table.Transpose(ParameterTable_Table), // First row is the name of the parameters: Turn it into column names #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table", [PromoteAllScalars=true]), // Turn the table in a list of records (with only 1 element) Custom1 = Table.ToRecords(#"Promoted Headers"), // Access the first (and// only) element Custom2 = Custom1{0} in // Return the record Custom2Producing this:
And I refferred to the P1 parameter from another query like this:
Hope this clarifies and helps...
Very doable.
- Create a workbook to hold you parameters
- Set up an excel table, name the table something understandable
- I think 2 columns: ParameterName and ParameterValue
- Save the workbook in a location accessible to all your other workbooks
- Open 1 of your other workbooks
- Create a query with Data|Get Data|From Excel File
- Follow the steps to find your file and table and close with " Transform"
- Now you have a query with all the paremeters
- Copy (Ctrl-C) the query
- Open each of your other workbooks and paste the query (Ctrl-V)
- Go through all the queries in all your workbooks to refer to the new query whenever you need a paremeter.
You may have additional questions to apply these steps to your unique situation. Feel free to ask, but please include actual screenshots and/or the M-Code of the query you trouble with....
Have fun!
I took it a step further and give you an example.
I created this little table in Excel
I created the following query in aanother workbook
let // Connect to the parameter workbook. Source = Excel.Workbook(File.Contents("C:\.....\How to create a parameter library to use in different workbooks with PQ.xlsx"), null, true), // Access the ParameterTable ParameterTable_Table = Source{[Item="ParameterTable",Kind="Table"]}[Data], // Transpose it: Rows become columns and vice versa #"Transposed Table" = Table.Transpose(ParameterTable_Table), // First row is the name of the parameters: Turn it into column names #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table", [PromoteAllScalars=true]), // Turn the table in a list of records (with only 1 element) Custom1 = Table.ToRecords(#"Promoted Headers"), // Access the first (and// only) element Custom2 = Custom1{0} in // Return the record Custom2Producing this:
And I refferred to the P1 parameter from another query like this:
Hope this clarifies and helps...
Thank you. Your codes helped. I tried and experience the following problem:
How can I get it works?
Try this:
I would do it the same
