Miguel Escobar Published March 12, 2019

Data Privacy and the Formula Firewall in Power BI / Power Query

Power BIPower Query

IMPORTANT: You can read the official documentation that Microsoft has on this topic from the following link (url).

Have you ever received any Errors similar to these?

image

Formula.Firewall: Query ‘Merge1’ (step ‘Source’) is accessing data sources that have privacy levels which cannot be used together. Please rebuild this data combination.

image

Formula.Firewall: Query ‘Query1’ (step ‘Added Custom’) references other queries or steps, so it may not directly access a data source. Please rebuild this data combination.

This is the blog post where I’ll cover this specific topic to answer 3 questions:

  • WHY does it even exist
  • What triggers this error
  • How to better spot the cause of the error

Before we start…let’s define one concept / feature:

What is Data Privacy in Power Query / Power BI?

Whenever you connect to any data source, you need to define 2 things for that connection:

  • Authentication Method – it can be implicit (when you connect to a file in your computer) or explicit (a new authentication window will appear for the options available)

image

  • Privacy Level – this is usually an optional window, but you can select from None (default state), Public, Organizational or Private. You can see this by going to ‘Data Source Settings’ –> ‘Edit Permissions’

image

The part that we want to focus on right now is the Privacy Levels and here is a simple table that will break down the options that we have available:

[table style=”solid”]

SettingDescription
PrivateA Private data source is completely isolated from other data sources. Contains sensitive or confidential information.
OrganizationalAn Organizational data source is isolated from all Public data sources, but it is visible to other Organizational data sources
PublicA Public data source gives everyone visibility to the data contained in the data source

[/table]

Here’s a brief summary that Ehren from the Power Query team sent me to better understand this:

  • Public data can be sent to Public/Organizational/Private sources.
  • Organizational data can be sent to Organizational/Private sources (but not to Public sources).
  • Private data can’t be sent anywhere (even to other Private sources).

Why does this Formula.Firewall error even exist?

firewall

Data Privacy levels act as a firewall or “gate”. It’s a protection mechanism for your Data Privacy.

The Firewall exists to prevent data from one source from being unintentionally sent to another source.

In an ideal world, you’d mark all of your data sources correctly so that this system can protect you from sending crucial and sensitive information to a data source that should not know about it.

Data Leakage is a really bad thing and we want to avoid it, hence why we have this “defense” system in place which is the Data Privacy or the Formula Firewall system.

How can my data from one data source end up in a completely different data source??? That, my friends, it’s because of something called Query Folding.

In short, query folding is the process where the M code gets translated into the native query language of a data source.

At times you can even check if Query Folding is happening by right clicking on a step of your query and selecting the option for “Native Query”:

image

This is done for performance purposes and to push as much work as possible to the data source instead of loading ALL of the data to your PC and working locally because of it.

Please note that even if the View Native Query button is greyed out, Query folding might still be happening. For example, if you connect to an OData source, you won’t be able to see the “View Native Query”, but most of the actions will be “folded”.

Query folding and Lazy evaluation will be a topic that I’ll cover in a future blog post. For now, Ehren’s explanation on how Query Folding impacts the Formula Firewall is perfect enough:

As part of folding, PQ sometimes may determine that the most efficient way to execute a given mashup is to take data from one source and pass it to another. For example, if you’re joining a small CSV file to a huge SQL table, you probably don’t want PQ to read the CSV file, read the entire SQL table, and then join them together on your local computer. You probably want PQ to inline the CSV data into a SQL statement and ask the SQL database to perform the join.

This is the culprit of it all and the main reason why there’s no “one size fits all” approach.

It’s because query folding might behave differently depending on your query steps and you don’t want to get rid of query folding because you want your queries to take advantage of it whereas possible.

Ehren from the Data Integrations team (the guys behind Power Query and the modern Get Data Experience) started a new discussion that goes in depth as to what happens inside the firewall process. If you’re an advanced user interested in learning the ins and outs of what goes behind the scenes with the formula firewall, then this is a discussion and article for you. Check it out here.

The post here, that you’re reading right now, is more of a summary so you can better understand what this Formula Firewall and Data Privacy Levels are.

What’s the benefit of the Data Privacy Levels?

Before we jump right into the errors, this feature (the Formula Firewall) doesn’t get that much credit because is not as exposed as other features.

Why would I want to go out of my way to set the privacy level of each of my sources?

Let me give you an example on the benefits of the Formula Firewall (without any errors):

  • We have 1 CSV file that is stored in SharePoint Online – this file contains a value that we would like to use as a Parameter. It’s in the header of the one of the sheets and it basically tells us what Territory that file belongs to and what data we need to get from the database. This is how that query looks like:

image

  • We have a SQL Server database with MILLIONS of records – we don’t want to load the millions of records, but rather just the data that we’re interested in using that parameter from the CSV file. This is how that query looks like:

image

THIS is where query folding is extremely important. You DO NOT want to load all of the data and only then filter it.

You only want to fetch the data already filtered from the database and that’s why query folding exists.

Here’s where we’re at. 2 Queries, but they don’t talk to each other yet:

image

The next thing that we need to do is simply do the filter on the Territory column of the “Sales SalesOrderHeader” query which comes from the database:

image

we use a placeholder like “Panama” and the modify the code to use the name of the query that has the argument that we want to use.

The result of that will look like this:

image

our final diagram view will look like this:

image

Let’s do some testing to see what’s going on

The first thing that we need to set up now is the Data Privacy Levels of each data source.

image

To do that, make sure that you’re in the Power Query Editor window and:

  1. Go to the Home Menu
  2. Click the Data Source Settings button
  3. Once the Data Source Settings window comes up, select the data source that you want to edit
  4. Click on Edit Permissions
  5. On the Edit Permissions window, select the privacy level.

In this case, I’m setting up both data sources to have an Organizational Privacy Level.

When I go back to the Sales SalesOrderHeader query, I can right click on the last step and see that the ‘View Native Query’ option is enabled and when I click on it I see this:

image

Check the text highlighted in yellow! This is essentially the proof from Power Query that we are not downloading the millions of records that this database has, but we’re only getting the data for France which is the value from the ‘SalesTerritory’ query that comes from a CSV. This is how Query Folding works between multiple data sources.

Now, what happens if we set up the database to have a private privacy level?

image

well, what happens is that the ‘View Native Query’ option will be greyed out and what’s going on behind the scenes is that query folding is being performed only based on the transformations that you’re doing for the steps prior to the one that involves the value from the CSV / SharePoint Query.

Query folding will only happen for what goes within that specific Data Source (SQL Server database) and when it’s time to mashup that data with another data source, query folding will stop.

You’re fundamentally working with a local copy of your database and only then filtering the data using the value in the CSV from SharePoint.

At the same time, this is a major benefit if you were trying to prevent sensitive data to be sent to this database instead of just a simple parameter.

What triggers the Formula.Firewall error?

bomb

In most cases, it’s the fact that a single query has 2 or more data sources in it that are trying to work together, but they either have incompatible privacy levels OR undefined privacy levels.

In a more technical sense, Query Folding is the culprit of it all, because it tries to mash up different data sources for better performance, but, in order to better understand the situations and how to resolve it, we need to know what’s causing the issue. What is triggering this Formula.Firewall.

Error on ‘Query is accessing data sources that have privacy levels which cannot be used together’

SNAGHTML85484c

Formula.Firewall: Query ‘Merge1’ (step ‘Source’) is accessing data sources that have privacy levels which cannot be used together. Please rebuild this data combination.

The error message is directly referring to the table previously shown in this post.

In short, what’s happening is that within the same query, you’re trying to access multiple data sources and they’ve been set up with incompatible privacy levels, meaning that you need to head over to the Data Source Settings window and set up your ALL your data sources in that query to the same privacy level; either ALL Public or ALL Organizational.

Once this is done you’ll notice that the issue will be completely gone.

Error on ‘Query references other queries or steps, so it may not directly access a data source’

This is probably the most common one, especially for the ones that use a Table from their current workbook inside of Power Query for Excel.

The legend himself (my friend) Ken Puls has written about this before here.

In his blog post, Ken explains how you can lay down your queries through stages such as:

image

  1. Import your Data and do most of the steps that could result in query folding in those “Staging” Queries
  2. Reference your staging queries and do the final transformations / shaping and load those to either Excel or your Data Model

That approach seems to work quite nicely but, why do we get that error?

Some people still experience the error even after reading that post and, like anyone else, they’d like to know why they keep hitting that error.

The answer: your query is what I’d like to call a “Chained” query.

What is a Chained query? well, this is term that I completely made up, but it’s my way of calling a query that has references that are “out of the scope” of the current query.

By “out of the scope”, I mean that you have multiple data sources in 1 query and one of these data sources has not been properly defined / determined / evaluated.

When does this usually happen? The common scenario where I’ve seen this happening is with queries that try to connect to either a web service or a REST API and where you need to evaluate one step (or query) and then use the result of that one for the next and you end up in this cycle / iteration / pagination. If you’re getting this error, please tell me more about your scenario in the comments section

What options do I have to fix this? You have 3 options:

  1. Ignore Privacy Levels – You’ve probably read this one before, but you can just tick the box to ignore the privacy levels. This will work locally, but not on the Power BI Web Service.
  2. Create a Power BI Custom Connector – this is BY FAR the best option as you can make sure that your queries can run in an optimal way and work seamlessly in the web service as well. Not to mention that you also have some features that are unique to custom connectors like being able to read the response headers of your calls and using setting your OAuth 2.0 flow. Sadly, this is only available for Power BI, but if you’re only going to work within Excel then the first option should be sufficient for your case
  3. Embedding or masking your Data Sources inside Functions – this method will make your data sources not visible to the Data Privacy Levels at first, but you can tune your queries to define the data source at first and then apply a function.

In regards to that last option (or method #3)

Back in the day (2015-2016), you would’ve to pull a MacGyver move in order to “add a new data source” inside of Power Query.

For example, if your SaaS had a REST API, but it didn’t have the connector in Power BI, you could use the Web.Contents function to grab the data using GET requests.

Of course, you’d need to know M code and read the API documentation to make it happen, but things would rapidly get complicated depending on things like the Authentication method, pagination and throttling.

Then after ALL your hard work creating those queries to get your data, you’d publish your report to the Power BI Service just to find out that your queries can’t be refreshed on the service:

image

This happened to me many times and while there are some workarounds, they just make me feel like I’m in the middle of a minefieldand I’m terrible at Minesweeper.

image

There are quite a bit of technicalities on why you would and should create a Custom Connector instead of trying to write the queries directly with Web.Contents and they’re mostly related to the Privacy Levels and the Formula Firewall.

If you’re ever considering connecting to a source that is not listed in the ‘Get Data’ window, I highly recommend that you create a Custom Connector for it and if you need help creating one for your company, then you can always contact me.

Power BIPower Query
Subscribe
Notify of
guest
26 Comentario
Oldest
Newest Most Voted
Inline Feedbacks
View all comments
hongjun

Suffer from the second error. Exactly the same scenario as described “try to connect to either a web service or a REST API and where you need to evaluate one step (or query) and then use the result of that one for the next and you end up in this cycle / iteration / pagination. ”
I need one column’s value of previous query whose source is gotten by REST request as parameter to do REST request again as source of the later query.

why customer connector can detour the pain? And would you please share how to create that customer connector?

George Payne

The documentation says “Data Connectors are currently only supported in Power BI Desktop”. So, there is no way of setting up a working “chained query” rest api that will refresh in the (online) power bi service? (I’m using instructure Canvas’ rest api, fwiw)

Minefields are ok, if there are prescribed ways to work around the mines. I have been unable to get my api calls (which reference other api calls) to refresh in the service, despite separating the web.contents calls into separate functions. Works fine locally, if I turn off privacy levels.

George H Payne

Thank you! I will look into this.

Just so I’m sure I understand: I have to install an on-prem data gatway to connect from one cloud service (power bi service) to another cloud service (my rest api service, in this case, Instructure Canvas)?

My grail is no more hardware (or VMs), but you do what you gotta do..thanks!

Navaid Mansoor

Hi Miguel , I am a newbie and pardon my ignorance.
Seems like chained query error.
My Query is connected to csv files in a folder which are then filtered on filename and then transformed to get the desired data table.
I am trying to filter this table through parameter. using a function as detailed in https://www.howtoexcel.org/power-query/how-to-parameterize-your-power-query/
The function when introduced as last step gives the error rebuild data error. tried your technique doesn’t work. My knowledge is copy/ paste limited.
Master query (without filter function)
let
Source = Folder.Files(“C:\Users\NAVAID\OneDrive – Etihad Airways\In Work\WIP”),
#”Filtered Hidden Files1″ = Table.SelectRows(Source, each [Attributes]?[Hidden]? true),
#”Filtered Rows3″ = Table.SelectRows(#”Filtered Hidden Files1″, each Text.Contains([Name], “ADAT”)),
#”Invoke Custom Function1″ = Table.AddColumn(#”Filtered Rows3″, “Transform File”, each #”Transform File”([Content])),
#”Renamed Columns1″ = Table.RenameColumns(#”Invoke Custom Function1″, {“Name”, “Source.Name”}),
#”Removed Other Columns1″ = Table.SelectColumns(#”Renamed Columns1″, {“Source.Name”, “Transform File”}),
#”Expanded Transform File” = Table.ExpandTableColumn(#”Removed Other Columns1″, “Transform File”, {“Column1”, “Column2”, “Column3”, “Column4”, “Column5”, “Column6”, “Column7”, “Column8”, “Column9”, “Column10”, “Column11”, “Column12”, “Column13”, “Column14”, “Column15”, “Column16”, “Column17”, “Column18”, “Column19”, “Column20”, “Column21”, “Column22”}, {“Transform File.Column1”, “Transform File.Column2”, “Transform File.Column3”, “Transform File.Column4”, “Transform File.Column5”, “Transform File.Column6”, “Transform File.Column7”, “Transform File.Column8”, “Transform File.Column9”, “Transform File.Column10”, “Transform File.Column11”, “Transform File.Column12”, “Transform File.Column13”, “Transform File.Column14”, “Transform File.Column15”, “Transform File.Column16”, “Transform File.Column17”, “Transform File.Column18”, “Transform File.Column19”, “Transform File.Column20”, “Transform File.Column21”, “Transform File.Column22″}),
#”Removed Top Rows” = Table.Skip(#”Expanded Transform File”,4),
#”Removed Columns1″ = Table.RemoveColumns(#”Removed Top Rows”,{“Source.Name”}),
#”Promoted Headers” = Table.PromoteHeaders(#”Removed Columns1″, [PromoteAllScalars=true]),
#”Filtered Rows” = Table.SelectRows(#”Promoted Headers”, each ([Defect Type] = “Aircraft Technical Log”)),
#”Removed Columns” = Table.RemoveColumns(#”Filtered Rows”,{“Log Series”, “NR #”, “Defect Type”, “Due Date”, “Counter”, “UOM”, “CAT”, “NR Material”, “Remarks”, “Visit/Date”, “NR Operation”, “Column17”, “Column18”, “Column19”, “Column20”, “Column21”, “Column22″}),
#”Renamed Columns” = Table.RenameColumns(#”Removed Columns”,{{“Log. No.”, “ATL”}, {“A/C Reg”, “REG”}, {“Date Raised”, “Date_Raised”}, {“ATA Code”, “ATA”}, {“Description of Defect”, “Description”}}),
#”Changed Type2″ = Table.TransformColumnTypes(#”Renamed Columns”,{{“ATL”, type text}, {“ATA”, type text}, {“Date_Raised”, type date}}),
#”Filtered Rows1″ = Table.SelectRows(#”Changed Type2″, each [Date_Raised] > #date(2020, 1, 31)),
#”Invoked Custom Function” = Table.AddColumn(#”Filtered Rows1″, “KWSearch”, each KWFunction([Description])),
#”Replaced Value” = Table.ReplaceValue(#”Invoked Custom Function”,null,”00″,Replacer.ReplaceValue,{“ATA”}),
#”Uppercased Text” = Table.TransformColumns(#”Replaced Value”,{{“Description”, Text.Upper, type text}}),
#”Added Custom” = Table.AddColumn(#”Uppercased Text”, “Status”, each if Text.StartsWith([ATA], “5”) or Text.Contains([Description],”SCRATCH”) or Text.Contains([Description],”DENT”) or Text.Contains([Description],”NICK”) or Text.Contains([Description],”GOUGE”) or Text.Contains([Description],”CRACK”) or Text.Contains([Description],”STRIKE”) or Text.Contains([Description],”CARGO”) then “P1-Not Started” else “P2-Not Started”),
#”Removed Duplicates” = Table.Distinct(#”Added Custom”, {“ATL”, “REG”}),
#”Filtered Rows2″ = Table.SelectRows(#”Removed Duplicates”, each ([KWSearch] = null) and ([Description] “NIL” and [Description] “NIL ” and [Description] “NIL DEFECT” and [Description] “NIL DEFECT ” and [Description] “NIL DEFECTS” and [Description] “NIL DEFECTS ” and [Description] “NIL DERFECTS” and [Description] “NIL FURTHER” and [Description] “NLI DEFECT” and [Description] “VOID” and [Description] “VOID ” and [Description] “VOIDED”)),
#”Removed Columns2″ = Table.RemoveColumns(#”Filtered Rows2″,{“KWSearch”})
in
#”Removed Columns2″

Function
let PRaisedDate = (TableName,RowNumber) =>
let
Source = Excel.CurrentWorkbook() {[Name= TableName]}[Content],
value = Source {RowNumber} [Value]
in
value

in PRaisedDate

Applying filter as follows to Master Query above as last step throws rebuild data error
#”Filtered Rows4″ = Table.SelectRows(#”Removed Columns2″, each [Date_Raised] >= #date(Date.Day(fgeDate(“PTable”,0)), Date.Month(fgeDate(“PTable”,0)), Date.Year(fgeDate(“PTable”,0))))

Can you help with this?

Simon Jones

Hi Miguel, I came across your post while searching for a solution to the data source error you describe. In my case I have a data source from the MS Common Data Service which I’m then applying a function on using Cognitive Services Text Analytics to extract keywords. I have tried every combination of privacy levels in the Power BI Desktop before publishing to the Web Service. Here’s my error:

{“error”:{“code”:”ModelRefresh_ShortMessage_ProcessingError”,”pbi.error”:{“code”:”ModelRefresh_ShortMessage_ProcessingError”,”parameters”:{},”details”:[{“code”:”Message”,”detail”:{“type”:1,”value”:”[Unable to combine data] Section1/cr634_MyLog/AutoRemovedColumns1 is accessing data sources that have privacy levels which cannot be used together. Please rebuild this data combination.”}}],”exceptionCulprit”:1}}} Table: cr634_MyLog

Btw and I’m not sure whether this is relevant, but I used a Proxy Azure Function App to route the requests to the Cognitive Service endpoint since Power BI wasn’t playing nice with the api key being passed in a header.

Thanks for any advice you may have.

Simon

Simon Jones

Thanks for your swift reply Miguel. I posted my issue on the Power BI forum as you suggested.

https://community.powerbi.com/t5/Service/AutoRemovedColumns1-is-accessing-data-sources-that-have-privacy/m-p/1091978#M96127

I don’t think the AI API itself is to blame but I could be wrong. I tried moving the Common Data Service data load to a separate query and that resulted in a different error:

{“error”:{“code”:”ModelRefresh_ShortMessage_ProcessingError”,”pbi.error”:{“code”:”ModelRefresh_ShortMessage_ProcessingError”,”parameters”:{},”details”:[{“code”:”Message”,”detail”:{“type”:1,”value”:”[Unable to combine data] Section1/Query1/AutoRemovedColumns1 references other queries or steps, so it may not directly access a data source. Please rebuild this data combination.”}}],”exceptionCulprit”:1}}} Table: Query1.

Marcos Gatti

Hi Miguel, I’m having a similar error. I’m calling a text cognitive service (keyPhrases) through a custom function, here is the code:

let
Origen = (text) => let

jsontext = Text.FromBinary(Json.FromValue(Text.Start(Text.Trim(text), 10000))),
jsonbody = “{ documents: [ { language: “”es””, id: “”0″”, text: ” & jsontext & ” } ] }”,
bytesbody = Text.ToBinary(jsonbody),
headers = [#”Ocp-Apim-Subscription-Key” = APIKey],
bytesresp = Web.Contents(EndPoint, [Headers=headers, Content=bytesbody]),
jsonresp = Json.Document(bytesresp)
in jsonresp

in
Origen

It’s work fine in PBI Desktop, but when I deploy it to PBI Service I got this error in the data source credential:

Web.Contents failed to get contents from ‘https://tapbi01.cognitiveservices.azure.com/text/analytics/v2.1/keyPhrases’ (404): Resource Not Found

Swapnil

Hi Miguel,

I have a question which is related to privacy. I have a password protected excel file in share point. I am trying to access this file in power bi to build a dashboard, but I keep getting an error which says – “File has corrupted data”. Is there any special method to access this file.

Please help.

Regards,
Swapnil

Dennis

Hi Miguel!
Interesting article!
I get this Firewall error when calling a Function within a step in a Query:
In the Query I first dynamically collect a bunche of URLs to some Excelfiles to load: Follow-Up_Report_URLs
Then for each URL call to function MR_ProcessFile.

Query step:
……
= Table.AddColumn(Source, “Content”, each MR_ProcessFile([#”Follow-Up_Report_URL”]))
…..

Function MR_ProcessFile:

= (MR_FilePath as text) => let
Source = Excel.Workbook(Web.Contents(MR_FilePath), null, true),
#”Monthly Report_Sheet” = Source{[Item=”Monthly Report”,Kind=”Sheet”]}[Data],

#”Changed Type4″ = Table.TransformColumnTypes(#”Monthly Report_Sheet”,{{“WeekMax”, type number}})
in
#”Changed Type4″

________________________

It works fine if I choose option 1…Ignore privacy settings…
I also put all sources to Public.

But… how should one do this as a Proffesional? Any ideas?

Steve

Hi Miguel,

We’re using dropbox so I had to make the power query’s filepath dynamic so that other people can also refresh the query.
I have used the following: I wrote the excel formula LEFT(CELL(“filename”,A1),SEARCH(“[“,CELL(“filename”,A1))-1) in a cell in the master file and named that cell with name manager as Folderpath.
Then I replaced the hardcoded filepath in the power query with this

let
Path = Excel.CurrentWorkbook(){[Name=”Folderpath”]}[Content]{0}[Column1],
Source = Excel.Workbook(File.Contents(Path & “thisisthenameofthefile.xlsx”))

Now I can refresh the query so it works for me but my colleagues run into the Firewall error.

Any idea on what to do?

Thank you

Steve

Dave Dalton

Absolutely stumped. I am facing Formula.Firewall for a specific set of queries and can’t find the workaround but it must exist! Your blog post here is absolutely the best description of what I am facing – a “chained” query. Yep, that’s my issue. But what is my solution?!

Up to now I have been manually refreshing this report in desktop, and could ignore the privacy level issues, but now it’s ready to go on the service and needs to be automatically refreshed multiple times a day.

My file has three main sources:

Web API connection to our CRM platform (pipedrive)

Web connection to a sharepoint file

Web API connection to OpenExchangeRates

Here is the heart of the problem; the OpenExchangeRates API has a monthly limit of 1000 calls, and if I were to call the past 2 years worth of rates every day I would blow past that in 2 days. I have put the historical exchange rates into a sharepoint excel table, and update that every month.

From this table, I can get the max date and create a list of dates between the max date and today. I then invoke the custom function on that column of dates to return all exchange rates for the list of dates more current than the historical table.

The historical table is stored in sharepoint, which I use my organizational log-in to access with OAuth and the API is an anonomous web connection. I am getting the error:

Formula.Firewall: Query ‘Currency Conversion Dates’ (step ‘Invoked Custom Function’) references other queries or steps, so it may not directly access a data source. Please rebuild this data combination

Example of the functions I use to call the API’s with the invoke custom function feature. It works great if the source of my dates isn’t from a mismatched privacy data source. This gets the USD to CAD currency conversion for any given date:

let
Source = (Date as text, optional results as list)=>
let
Source = Json.Document(Web.Contents(
“https://openexchangerates.org/api”,
[
RelativePath=”historical/” & Date & “.json”,
Query=
[
app_id=”xxxxxxxxxxxxxxxxxx”,
symbols=”CAD”
]
]
)),
rates = Source[rates]
in
rates
in
Source

Do I turn the historical currency converstion into sharepoint list that I can set up some custom flow / power automate to update daily? Do I store this historical data hardcoded directly in the Power BI file itself? Do I store the historical information in something like a google sheet?

Any help or insight is greatly appreciated!

tldr; need a solution to import the historic and current currency conversion from USD to CAD on a daily level that doesn’t throw a privacy firewall error.

Alexis

Hi,

Very instructive article !

As I’m working on a tool which is a simple Excel file, I’m interested into your 3rd solution  (Embedding or masking your Data Sources inside Functions) to avoid each user to set his privacy settings for the query to work.

My main query is a very simple API request :

Source =
Json.Document(
Web.Contents(
“https://APIadress/”,
[
RelativePath=query2
]
)
)

query2 stands for another query getting the RelativePath from an Excel cell the user can fill. If I ignore privacy levels, it works like a charm. Is there a workaround to avoid this ?

Thank you

Alexis

I managed to mask my secondary query just after “RelativePath=”, and it’s finally working !
It’s an Excel-based solution each user must have access to on his own computer, so the setting “ignore the privacy levels” was not ideal. It is linked to the user and not the file, so each deployment of a new version would have resulted in a new manipulation for the user.
Thanks again

Leo

First of all, thanks for the post, so many info together!

Im suffering with a different, but related error. Im using a excel as a parameter to a oracle SQL query, like in example. All my data sources are organizational level of privacy.

In powerBI Desktop app, everything works well, but after i published the report, when i use the web refresh, i get the error: “Query references other queries or steps, so it may not directly access a data source”…

I tried everything, but not solved my problem.

Have you ever faced that?