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) (>4...
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...

L70F's avatar
L70F
Helper II
1 year ago

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

  • Cristian_Angyal's avatar
    Cristian_Angyal
    Most Valuable Professional
    1 year ago

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

  • PwerQueryKees's avatar
    PwerQueryKees
    Super User
    1 year ago

    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...

  • PwerQueryKees's avatar
    PwerQueryKees
    Super User
    1 year ago

    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.