Forum Discussion

L70F's avatar
L70F
Helper II
1 year ago
Solved

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) (>40) with Excel Power Query.
I need to make my templates scalable not only for the one site in my company I made them for but also for other sites that are planned for a change of ERP.
I want to make managing source addresses connected to my queries more efficient. I use different sources, such as SQL, external Excel files from SharePoint, and tables in the same workbook.
 
Today, I created parameters in the Power Query that I use in different queries in the workbook. The same parameters are useful for the other workbooks too, and I have created all the needed parameters in each workbook.
 
When I am done migrating the first site and starting with the second site, I need to go to each workbook to change the parameters, which is very time-consuming.
 
I wish that I could have all parameters in one workbook, for example, and then load the needed parameters to each workbook with queries. So, I could change the parameter value only in one place, and it would affect them whenever they are used.
Example:
 
WB=workbook
P=parameter
Q=query

 

 

 

 
 
Unfortunately, I couldn't find a good way of doing that, and I need your support in this matter.
 
Thanks a lot
Best regards
Larissa
Need Help
  • PwerQueryKees's avatar
    PwerQueryKees
    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
    Custom2

    Producing this:

    And I refferred to the P1 parameter from another query like this:

    Hope this clarifies and helps...

9 Replies

  • PwerQueryKees's avatar
    PwerQueryKees
    Super User
    1 year ago

    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! 

    • PwerQueryKees's avatar
      PwerQueryKees
      Super User
      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
    Custom2

      Producing this:

      And I refferred to the P1 parameter from another query like this:

      Hope this clarifies and helps...

      • L70F's avatar
        L70F
        Helper II
        1 year ago

        Thank you. Your codes helped. I tried and experience the following problem:

        How can I get it works?