How to create a parameter library to use in different workbooks with PQ

[L70F]

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

[PwerQueryKees]

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 · Accepted solution]

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]

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

How can I get it works? 

[PwerQueryKees]

Try this:

[L70F]

It is not allowed by my company to ignore privacy level. Is there another way to solve it without changing security levels?

[PwerQueryKees]

Or try what CoPilot gave me:

The Formula.Firewall error in Power Query occurs when there's a mismatch in the privacy levels of the data sources you're trying to combine1. To resolve this, you can set the privacy level of your Excel workbook to match the SQL Server2.

Here's how you can do it:

1. Open your Excel workbook3.

2. Go to the Data tab2.

3. Select Get Data > Query Options2.

4. In the Query Options dialog box, go to the Global section2.

5. Click on Privacy2.

6. Set the Privacy Level to match the SQL Server2. The options are:

   • Ignore privacy levels (not recommended for sensitive data)

   • Private

   • Organizational

   • Public4

To determine the correct privacy level for your SQL Server data, you'll need to check the sensitivity of the data2. If it contains highly sensitive or confidential information, you should set the privacy level to Private2. If it's less sensitive, you might choose Organizational or Public2.

[PwerQueryKees]

Can you set the privacy level of the Excel to match the SQL privacy level?

I have never even tried it.... So I may be sending you on a goose chase...

[Cristian_Angyal]

You could also create the Parameters Table inside SQL_DB and follow same logic as @PwerQueryKees provided.

[Cristian_Angyal]

I would do it the same