Parameterising Queries

Question

Hi,

I've been experimenting with parameterising queries with values returned by other queries, but have run into the following error:

Formula.Firewall: Query 'Query1' references other queries and so may not directly access a data source. Please rebuild this data combination.

The scenario I was trying to implement was to use a query to read a value from an Excel table, and then take that value and pass it into another query against a web service. For example, there's a Google web service that returns the distance between two addresses and I wanted to be able to take the origin and destination addresses from cells in the worksheet rather than hard-code them.

Is this actually possible? If so, could you give me a clue how to do this?

Thanks,

Chris

---

Check out my MS BI blog I also do SSAS, PowerPivot, MDX and DAX consultancy and run public SQL Server and BI training courses in the UK

Answer 1

Hi Chris,

This scenario is definitely possible with Data Explorer. However, there is some risk for users to leak information to external sources when doing this, and so we try to prevent this from being done "by default". The reason is that a user could inadvertently send data from a private source (such as their Excel workbook) to a public source (such as Google Web Service in your example).

You can disable this level of protection by clicking the "Fast Combine" button in the Data Explorer ribbon tab.

More information about Fast Combine and Privacy Levels can be found in our Help contents: http://office.microsoft.com/en-us/excel-help/privacy-levels-HA104009800.aspx

Thanks,

M.

Answer 2

Hi Miguel,

When and where would we prompted with the Select Privacy Level For Data Sources dialog box as discussed in the "To configure privacy level settings" section here: http://office.microsoft.com/en-us/excel-help/privacy-levels-HA104009800.aspx#_How_to_configure

I've never been prompted for this (yet).

thanks
Jamie

---

ObjectStorageHelper<T> – A WinRT utility for Windows 8 | http://sqlblog.com/blogs/jamie_thomson/ | @jamiet | About me

Answer 3

Perfect, that's working now. Thanks again!

Chris

---

Check out my MS BI blog I also do SSAS, PowerPivot, MDX and DAX consultancy and run public SQL Server and BI training courses in the UK

Answer 4

Hi Jamie,

The Privacy Levels information becomes important when trying to combine queries with data from different sources (via Merge/Append or even Custom Column that references another query). Only at this point, we prompt the user about the Privacy Levels for each source.

In many cases, we don't even need to ask the user (so you might have not seen this prompt yet) since we are able to infer what the Privacy Level is (for instance, importing data from a Internet HTTP page with Anonymous Credentials is considered "Public", whereas data from your current Excel workbook is considered "Private").

Also, the flow of data from a less restrictive privacy level (i.e. Public -> Organizational -> Private) is generally allowed, while the other direction (i.e. Private -> Public, such as in Chris' example) needs explicit user "approval", by disabling this protection and enabling "Fast Combine".

As with every other product feature, we have tried to optimize for the non-advanced user to do things easily and also protect them from cases like the above. For the more advanced user scenarios, they may need to go a bit off the happy path to make the scenario work, such as enabling this non-default mode.

Hope that helps,

M.

Answer 5

Great, Chris. I'm glad it worked! : )

Answer 6

You can avoid having to enable fast combine by always doing your combining of two Queries in a new Query. For example, if you want to augment MyTable with data from MyLookup, create a new query and make the Source formula = Table.AddColumn(MyTable, each MyLookup([foo], ...)).

Answer 7

I would like to do something almost similar, but I can't tell if it is possible...

I want to use data in a column (each cell) / previous OData call to as a parameter in a call to another OData service.

The PDF of the expression language isn't clear to me as to whether this would work.

If I call an OData service in one sheet, and get a column of ID's, and name the query "MyOData1".

Then I want to call the second OData service and use that column of ID's in the second call, but augment the table "MyOData1" with data from this second call, could I do it?

Like (this is totally wrong):

formula = Table.AddColumn(MyOData1, (OData.Feed("URI to OData Feed 2"), each InvokedQuantityOrdered = QuantityOrdered([MyOData1.ID],"start time","end time"))

- or something like that? That's obviously wrong.

Basically I want to only get items from the second OData feed that match the first OData ID's and put them in one column. I see I could do it another way, but the second service only returns 1 value each call.

I can ask for them to expose a different service, but so far, that is how it works.