Forum Discussion
L70F
1 year agoHelper II
How to create a parameter library to use in different workbooks with PQ
Hi, I have a problem that I hope you can help me to solve. I am responsible for data migration from an old ERP to a new ERP system, so I have created several migration templates (workbooks) (>4...
- 1 year ago
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...
L70F
1 year agoHelper II
It is not allowed by my company to ignore privacy level. Is there another way to solve it without changing security levels?
Cristian_Angyal
1 year agoMost Valuable Professional
You could also create the Parameters Table inside SQL_DB and follow same logic as PwerQueryKees provided.