Unable to use parameter in query2 which uses query1 to get parameter values

[ShaliniUpputell]

There is an issue in power BI report. I have parameter1 which displays values froom query1. Query2 is using this parameter1 as &parameter1& in where condition. I am getting below error. 

Could you please advise 

[MAwwad]

The error message you're receiving indicates that the syntax in your query is incorrect. Specifically, the issue seems to be with how you're using the parameter in the WHERE clause of query2.

Here are a few things you can try:

1. Double-check the syntax of your query to ensure that the parameter reference is correct. It should be in the format: @Parameter1.

2. Make sure that the data type of the parameter matches the data type of the column you're comparing it to in the WHERE clause.

3. Verify that the parameter is being populated correctly by running query1 separately and checking the values returned.

4. Try using the "Advanced Editor" in Power Query to manually edit the M code for query2 and ensure that the parameter reference is correctly formatted.

5. If none of these solutions work, try breaking down the query into smaller steps to isolate the issue. You may be able to identify the problem by testing each part of the query separately.

Hopefully, one of these solutions will help you resolve the issue and use your parameter successfully in query2.

[Uspace87]

@ShaliniUpputell 

he Formula.Firewall function in Power Query is used to control data privacy by preventing certain types of operations that can potentially expose sensitive data. It is designed to block any data access from an M code step to external data sources or to other queries or steps that are not authorized to access the data.

When Formula.Firewall is used in a step, it checks whether the step references any external data sources, other queries, or steps that are not authorized to access the data. If such a reference is found, the function blocks the operation and returns an error message that indicates the data source or reference that caused the problem.

For example, if you have a query that reads data from a SQL Server database and performs some transformations, and you add a new step that references a different query or step that does not have permission to access the SQL Server database, Formula.Firewall will block the operation and return an error message indicating the unauthorized reference.

To resolve the issue, you need to add permission to the unauthorized query or step to access the data source or other query. You can do this by setting the privacy level of the data source or query to the appropriate level.

Overall, the Formula.Firewall function is a powerful tool for ensuring data privacy and preventing unauthorized data access. It helps you to maintain control over data access and protect sensitive data from exposure.

In Power Query, data privacy levels are used to control access to data sources based on their sensitivity. When you connect to a data source, Power Query automatically assigns a privacy level to the data source based on the type of data it contains and the source of the data.

The privacy level is a numeric value that indicates the sensitivity of the data, with higher values indicating more sensitive data. Power Query uses the privacy level to prevent unauthorized access to the data source by blocking any operation that requires a higher privacy level than the one assigned to the data source.

To set the privacy level of a data source or query to the appropriate level, you can follow these steps:

1. Open the Power Query Editor: To set the privacy level of a data source or query, you need to open the Power Query Editor in Excel or Power BI.

2. Select the data source or query: In the Power Query Editor, select the data source or query that you want to set the privacy level for.

3. Open the Data Source Settings: In the Query Settings pane, click on the gear icon next to the data source or query to open the Data Source Settings dialog box.

4. Set the Privacy Level: In the Data Source Settings dialog box, click on the Privacy Level button to set the privacy level for the data source or query. You can choose from the following options:

• None: No privacy levels are applied, and all data sources and queries can access each other.
• Public: This is the lowest privacy level, and data from public sources can be shared with other data sources or queries with a public or higher privacy level.
• Organizational: This privacy level is for data that is sensitive but can be shared within the organization. Data from organizational sources can be shared with other organizational or higher privacy level data sources or queries.
• Private: This is the highest privacy level, and data from private sources can only be accessed by the same query or other queries with a private privacy level.

1. Save and Close: After setting the privacy level, click on OK to save the changes and close the Data Source Settings dialog box.

By setting the appropriate privacy level for your data sources and queries, you can control access to sensitive data and ensure that only authorized queries and data sources can access it.