Chris Webb's BI Bloghttps://blog.crossjoin.co.uk
Microsoft Analysis Services, MDX, DAX, Power Pivot, Power Query and Power BIWed, 16 Aug 2017 18:09:32 +0000enhourly1http://wordpress.com/https://secure.gravatar.com/blavatar/14ea7107002d0f37386f8abd4c507070?s=96&d=https%3A%2F%2Fs2.wp.com%2Fi%2Fbuttonw-com.pngChris Webb's BI Bloghttps://blog.crossjoin.co.uk
wordpress/Cpjzhttps://feedburner.google.comObscure MDX Month: Current and CurrentOrdinalhttp://feedproxy.google.com/~r/wordpress/Cpjz/~3/_w2OIybx_FY/
https://blog.crossjoin.co.uk/2017/08/11/obscure-mdx-month-current-and-currentordinal/#respondFri, 11 Aug 2017 12:00:00 +0000http://cwebbbi.wordpress.com/?p=5246]]>When you are writing an MDX expression, everywhere you use a set you can give that set a name and then reference the name later on. This is known as creating an inline named set, something I have blogged about a few times (see here and here) over the years. When you are iterating over a set using a function like Generate() or Filter(), if you give that set a name you can then use the Current and CurrentOrdinal functions to find out more about the item in the set returned at the current iteration.

It returns a set of four tuples on rows: every combination of Gender and Marital Status:

If you pass the set on rows to the Filter() function and give it a name (for example MySet) you can then use the CurrentOrdinal function to find the 1-based ordinal of the current iteration. This query uses the CurrentOrdinal function to filter the set shown above so only the first and third items in the set are returned:

With an inline named set you can also use the Current function to return the tuple at the current iteration. Here’s another query that uses the Current function to remove the tuple (Female, Single) from the set:

I won’t pretend that these functions are massively useful, but fans of super-complex MDX will enjoy this vintage post where I used them.

]]>https://blog.crossjoin.co.uk/2017/08/11/obscure-mdx-month-current-and-currentordinal/feed/0cwebbbiimageimageimagehttps://blog.crossjoin.co.uk/2017/08/11/obscure-mdx-month-current-and-currentordinal/Obscure MDX Month: Recreating The Star Ratings Measure In MDX Using Excel Functionshttp://feedproxy.google.com/~r/wordpress/Cpjz/~3/HlJVWMVDu-Q/
https://blog.crossjoin.co.uk/2017/08/04/obscure-mdx-month-recreating-the-star-ratings-measure-in-mdx-using-excel-functions/#commentsFri, 04 Aug 2017 12:30:00 +0000http://cwebbbi.wordpress.com/?p=5238]]>I still love MDX, but I’m aware that I blog about it less and less – which is a shame, I know. Therefore I’ve decided that for the next four weeks I’m going to write about some obscure MDX topics that hopefully will make all you SSAS MD diehards out there feel less neglected… even if they don’t have much practical use.

Let’s start off with recreating my ever-popular DAX star-ratings measure in MDX. Well, not exactly pure MDX, but did you know that in MDX you can call some Excel functions (in the same way you can call some VBA functions)? It’s a really, really bad thing to do from a query performance point of view, but it does allow you to do some useful calculations that might otherwise be impossible. Here’s a query on the Adventure Works cube that uses the Excel Rept() and Unichar() functions (functions that do not exist in MDX proper) to recreate my start-ratings measure:

]]>https://blog.crossjoin.co.uk/2017/08/04/obscure-mdx-month-recreating-the-star-ratings-measure-in-mdx-using-excel-functions/feed/4cwebbbiimageimagehttps://blog.crossjoin.co.uk/2017/08/04/obscure-mdx-month-recreating-the-star-ratings-measure-in-mdx-using-excel-functions/Thoughts On Power Query/Common Data Service Integrationhttp://feedproxy.google.com/~r/wordpress/Cpjz/~3/vQw5MVkoKNE/
https://blog.crossjoin.co.uk/2017/07/28/thoughts-on-power-querycommon-data-service-integration/#commentsFri, 28 Jul 2017 15:21:11 +0000http://cwebbbi.wordpress.com/?p=5228]]>Yesterday there was a webinar on how Power Query is going to be used as the way to load data into the Microsoft Common Data Service. You can watch it online here (if you’re in a hurry, skip to 24 minutes in for the details on the Power Query integration):

I don’t have much to add to what’s in the webinar, but there are a few things that occurred to me:

This is Power Query in a browser. If they can build a web interface for Power Query for CDS, why not for Power BI? It would give us the full power of Power BI Desktop in the browser, on any platform (I know a few people have been asking for Power BI Desktop for Mac), with no tedious manual updating.

In the demo at around the 54 minute mark, Miguel shows a screen where there are two Database Load options:
The “Only load new or modified rows for existing entities” options is… incremental load! This makes me wonder whether Power BI users who want incremental load should be using using the CDS as a staging area (a super-simple data warehouse…?) and then connecting Power BI to it?

I’ll be honest, I’ve not done anything with the CDS so I can’t really say how useful this new functionality will actually be – and I’ve heard mixed reports about the CDS, if I’m honest. Certainly, as someone (I suspect Meagan), mentions in a question, the only way Power BI can connect to the CDS right now is via DirectQuery and not Import, which seems pretty crazy. Still… I’m very curious and will be paying close attention to how it develops. More Power Query in the world can only be a good thing!

]]>https://blog.crossjoin.co.uk/2017/07/28/thoughts-on-power-querycommon-data-service-integration/feed/2cwebbbiimageimagehttps://blog.crossjoin.co.uk/2017/07/28/thoughts-on-power-querycommon-data-service-integration/Creating Animated Reports In Power BI With The Drilldown Player Custom Visualhttp://feedproxy.google.com/~r/wordpress/Cpjz/~3/aYXTXeL-qzA/
https://blog.crossjoin.co.uk/2017/07/28/creating-animated-reports-in-power-bi-with-the-drilldown-player-custom-visual/#commentsFri, 28 Jul 2017 12:00:00 +0000http://cwebbbi.wordpress.com/?p=5219]]>Last week I had the chance to do something I have not done before: build a Power BI report to be displayed on a big screen hanging on a wall. To make up for the loss of user interactivity, I used the new Drilldown Player custom visual to cycle through different selections and display a new slice of data every few seconds; Devin Knight’s blog post here has a great summary of how to use it. However I wasn’t happy about the look of the Drilldown Player visual in this particular report: the play/stop/pause buttons aren’t much use if you can’t click on them and the visual doesn’t show all of the values that it is cycling through. As a result I hid the visual behind another one and came up with a different way of displaying the currently-displayed selection.

Here’s a simple example of what I did. Imagine you have two identical tables called Table1 and Table2 loaded into your dataset that contain a list of the 24 hours in a day:

With no relationship between these tables in the dataset, you can display the 24Hour column from one in a table in your report and then use the Drilldown Player to cycle through the values in the 24Hour column in the other. At this point, because there’s no relationship between the tables, the Drilldown Player visual has no effect on the table. Next create a measure called Displayed as follows:

…and add it to the table in the report. This measure uses my old favourite the Unichar() function to display an arrow against the row in the table that matches the currently selected hour in the Drilldown Player. The result is this:

This got me thinking about other fun stuff that I could do with this technique. After adding some more columns to my source data:

…I created the following measure:

Clock = UNICHAR(128335 + MAX('Table1'[Hour]))

This takes the hour selected by the Drilldown Player and displays the corresponding Unicode character for a clock face showing that hour. Here’s what the measure looks like when displayed in a card:

I also had a go at an animation showing the sun and moon rising and setting – I did this by displaying the Unicode characters as data labels in a scatter chart, then using colour to hide everything apart from the data labels – but by this stage I thought things were getting too silly…

Anyway, you can download the report with these animations in here, and view it online here. Have fun!

]]>https://blog.crossjoin.co.uk/2017/07/28/creating-animated-reports-in-power-bi-with-the-drilldown-player-custom-visual/feed/8cwebbbiimageCurrentSelectionimageAnimatedClockSunAndMoonhttps://blog.crossjoin.co.uk/2017/07/28/creating-animated-reports-in-power-bi-with-the-drilldown-player-custom-visual/New M Functionality And Behaviour In Power BI Custom Data Connectorshttp://feedproxy.google.com/~r/wordpress/Cpjz/~3/j76UKan91W4/
https://blog.crossjoin.co.uk/2017/07/21/new-m-functionality-and-behaviour-in-power-bi-custom-data-connectors/#commentsFri, 21 Jul 2017 15:45:00 +0000http://cwebbbi.wordpress.com/?p=5203]]>Over the past few weeks I’ve spent some time playing around with Power BI custom data connectors and while I don’t have anything to share publicly yet (other people are way ahead of me in this respect – see the work of Igor Cotruta, Miguel Escobar and Kasper de Jonge among others) I have learned some interesting things that are worth blogging about.

First of all, the data privacy rules around combining data from different data sources do not apply in custom data connector code. As the docs say here:

Data combination checks do not occur when accessing multiple data sources from within an extension. Since all data source calls made from within the extension inherit the same authorization context, it is assumed they are “safe” to combine. Your extension will always be treated as a single data source when it comes to data combination rules. Users would still receive the regular privacy prompts when combining your source with other M sources.

Those of you who have followed my recent series on this topic, or who have struggled with the Formula.Firewall error, will appreciate how much easier this makes combining data from different sources.

Secondly, you have a lot more flexibility when it comes to different types of authentication for web services. As I showed in my session on web services and M at the Data Insights Summit, there are a lot of limitations when it comes to working with web services in Power BI or Excel. Within a custom data connector, however, you can connect to web services that use OAuth for authentication, you can make POST requests to web services that require authentication and you can pass a web API key from the credentials store through an HTTP custom header and not just through a query parameter – none of which are possible in Power BI or Excel.

I’m sure there are a lot of other useful bits of functionality or behaviour that are only available in custom data connectors – I know I’ve only just begun to learn what’s possible. Even with what I’ve listed here, though, I get the feeling that there will be a lot of cases where you will have no choice but to build a custom data connector just to be able to access certain data sources, even if you only need to create a single report. There may also be cases where it’s preferable to build a custom data connector rather than embed lots of complex M code in a Power BI report or Excel workbook, perhaps to make code portability easier. It’s a bit of a pain to have to have Visual Studio and the SDK installed in order to do this, but building a custom data connector is fairly easy if you already know M and the development experience in Visual Studio (with intellisense!) is much better than in the Advanced Query Editor window.

]]>https://blog.crossjoin.co.uk/2017/07/21/new-m-functionality-and-behaviour-in-power-bi-custom-data-connectors/feed/3cwebbbihttps://blog.crossjoin.co.uk/2017/07/21/new-m-functionality-and-behaviour-in-power-bi-custom-data-connectors/Configuring Power BI Gateway Data Sources For Files And Foldershttp://feedproxy.google.com/~r/wordpress/Cpjz/~3/3rRSMNiurS0/
https://blog.crossjoin.co.uk/2017/07/14/configuring-power-bi-gateway-data-sources-for-files-and-folders/#commentsFri, 14 Jul 2017 19:45:18 +0000http://cwebbbi.wordpress.com/?p=5197]]>Recently I’ve been building a lot of Power BI reports from csv and Excel files, and to make sure that scheduled refresh works I have been setting up data sources in an On Premises Data Gateway (what used to be called the Enterprise Gateway). I had assumed that if I was connecting to file-based data sources in my Power BI dataset then, in the gateway, I would need to set up one data source for each file that I’m connecting to – which is a bit of a pain. In fact it turns out that you can set up a gateway data source for the folder that the files are in instead.

Let me give you an example. Imagine that you have three Excel files in a folder called C:\Sales Data:

Now imagine that you have three queries in Power BI that get data from these three files:

However, once the report has been published only one data source needs to be set up in the On Premises Data Gateway for it to refresh successfully, even though the report connects to three different files. Here’s a screenshot of the gateway data source I set up in the Power BI service:

Two things to point out:

The data source type is set to Folder

The full path property is set to the path of the folder that the files used by the report are in, ie C:\Sales Data

Setting up a single gateway data source for a folder is obviously a much better option than setting up multiple data sources for all the files in the folder. Did everyone else know this but me? I guess this is all related to the inheritance of data privacy settings that I blogged about here.

]]>https://blog.crossjoin.co.uk/2017/07/14/configuring-power-bi-gateway-data-sources-for-files-and-folders/feed/6cwebbbiimageimageimagehttps://blog.crossjoin.co.uk/2017/07/14/configuring-power-bi-gateway-data-sources-for-files-and-folders/Data Privacy Settings In Power BI/Power Query, Part 5: The Inheritance Of Data Privacy Settings And The None Data Privacy Levelhttp://feedproxy.google.com/~r/wordpress/Cpjz/~3/PgddXCyLhGQ/
https://blog.crossjoin.co.uk/2017/07/10/data-privacy-settings-in-power-bipower-query-part-5-the-inheritance-of-data-privacy-settings-and-the-none-data-privacy-level/#commentsMon, 10 Jul 2017 12:35:00 +0000http://cwebbbi.wordpress.com/?p=5187]]>Something I didn’t understand at all when I started writing this series was how the “None” data privacy level worked. Now, however, the ever- helpful Curt Hagenlocher of the Power Query dev team has explained it to me and in this post I’ll demonstrate how it behaves and show how data privacy levels can be inherited from other data sources.

Let’s go back to the original example I used in part 1 of this series where I showed how data from an Excel workbook can be combined with data from SQL Server, and how the data privacy settings on each data source determine whether query folding takes place or not (I suggest you read that post before continuing to get some background). Now, imagine that the Excel workbook is in a folder called C:\Data Privacy Demo, and a query called FilterDay is used to get data from it:

…and the query is run for the first time, then you will get prompted for credentials to access SQL Server and after that you’ll get prompted to set data privacy levels on both data sources used:

The dropdown boxes in the second column allow you to set the data privacy settings for each data source, but look at the data sources listed in the first column. There are two things to point out:

The data sources the two queries are accessing are the DimDate table in the Adventure Works DW database on localhost, and the file C:\Data Privacy Demo\FilterParameter.xlsx. However you’re not being prompted to set data privacy levels on those exact data sources, you’re being prompted to set data privacy levels on the localhost instance and the c:\ drive

The data source names are displayed in dropdown boxes, so there are other options to select here

Clicking each dropdown box is revealing:

For the SQL Server database you can set the data privacy level at two places: the localhost instance (the default), or the Adventure Works DW database on that instance. For the Excel workbook you get set the data privacy level at three places: the c:\ drive (the default), the folder c:\Data Privacy Demo that the Excel workbook is in, or the Excel workbook itself.

Let’s say you accept the defaults and set the data privacy settings to Public on localhost and the c:\ drive:

As you would expect after reading part 1 of this series, the query runs and query folding takes place:

Now, let’s say you copy the Excel file up to the root of the c:\ drive and rename it to filterparameter2.xlsx, then update the FilterDay query above to load data from this new Excel file instead:

At this point, when you click the Data Source Settings button and look at the permissions for the file c:\filterparameter2.xlsx you will see that the privacy level is set to None:

However, it behaves as if it has a data privacy level of Public: the second query that gets data from SQL Server runs successfully, query folding still takes place and you are not prompted to set a data privacy level for this data source. Why?

The “None” data privacy level means that no privacy level has been set for this exact data source. However, when this happens the engine checks to see if a data privacy level has been set for the folder that this file is in and then for all folders up to the root. In this case, since the data privacy level has been set to Public for the c:\ drive, all files in all folders on that drive that have a data privacy level set to None (like this one) will inherit the c:\ drive’s setting of Public:

The same goes for databases on a SQL Server instance: they can inherit the data privacy settings set for the instance. The same is also true for web services, where data privacy settings can be set for different parts of a URL; for example, here’s the list of options for a call to the https://data.gov.uk/api/3/action/package_search web service described in part 2 of this series:

The general rule is that the engine looks for permissions for the exact data source that it’s trying to access, and if none are set then it keeps looking for more general permissions until it runs out of places to look.

In my opinion, I don’t think the way the “None” privacy level and inheritance works is very clear right now – it makes sense now I’ve had it explained to me, but the UI does nothing to help you understand what’s going on. Luckily it sounds like the dev team are considering some changes to make it more transparent. I would like to see the fact that data privacy levels have been inherited for a data source, and where they have been inherited from, called out in the Edit Permissions dialog.

]]>https://blog.crossjoin.co.uk/2017/07/10/data-privacy-settings-in-power-bipower-query-part-5-the-inheritance-of-data-privacy-settings-and-the-none-data-privacy-level/feed/4cwebbbiimageimageimageimageimageimageimageimageimageimagehttps://blog.crossjoin.co.uk/2017/07/10/data-privacy-settings-in-power-bipower-query-part-5-the-inheritance-of-data-privacy-settings-and-the-none-data-privacy-level/Data Privacy Settings In Power BI/Power Query, Part 4: Disabling Data Privacy Checkshttp://feedproxy.google.com/~r/wordpress/Cpjz/~3/D0KMdIhJjKY/
https://blog.crossjoin.co.uk/2017/07/04/data-privacy-settings-in-power-bipower-query-part-4-disabling-data-privacy-checks/#commentsTue, 04 Jul 2017 14:04:01 +0000http://cwebbbi.wordpress.com/?p=5164]]>So far in this series, I have shown how changing the data privacy settings for a data source can affect the performance of queries and even prevent them from executing completely. What I haven’t mentioned yet is that you also have the option of disabling data privacy checks completely in Power BI Desktop and Excel. In this post I will show you how you can disable data privacy checks and discuss the pros and cons of doing so.

In Power BI Desktop you can change whether data privacy checks are applied when a query executes by going to File/Options And Settings and selecting Options:

The same settings can be found in Excel 2016 by going to the Data tab, clicking Get Data and then selecting Query Options.

In both cases this brings up the Options dialog.

There are two panes in the Options dialog with properties that are relevant to how data privacy checks are applied. First of all, in Global/Privacy, there are global properties that are relevant for every .pbix or Excel file that you open on your PC:

The three options here need a little bit of explanation:

Always combine data according to your Privacy Level settings for each source means that data privacy settings are always applied for every .pbix or Excel file you open, regardless of the properties (described below) that you have saved for individual files.

Combine data according to each file’s Privacy Level settings means that the properties set on individual .pbix or Excel files control how the data privacy checks are applied.

Then, in the Current File/Privacy pane, there are properties that are saved in and apply to the current .pbix or Excel file that you have open:

The radio buttons here are greyed out if you have options #1 or #3 selected in the previous pane; it’s only if you have selected option #2, Combine data according to each file’s Privacy Level settings, that these properties are taken into account. You may need to close and reopen the Options dialog if you have changed settings in the previous pane but the radio buttons here remain greyed out.

The two options here are:

Combine data according to your Privacy Level settings for each source, which means that the data privacy settings that you have set for each data source are used to control how queries that combine data from multiple data sources behave. This is the default setting.

To sum up, these two groups of properties allow you to choose whether data privacy settings are applied differently for different .pbix or Excel files, or whether, on your PC, they are always applied or always ignored.

For Power BI users it is important to remember that these settings only apply to Power BI Desktop. After a report has been published, if you are using the On-Premises Data Gateway, you also need to configure data privacy settings on the data sources used by your dataset in the Power BI portal. If you are using the On-Premises Data Gateway in Personal Mode (what used to be called the Personal Gateway) then you can configure it to ignore data privacy settings as described here. Unfortunately if you are not using Personal Mode (ie you are using what used to be called the Enterprise Gateway, and what is now just called the On-Premises Data Gateway) then at the time of writing there is no way to configure the gateway to ignore data privacy levels. You can vote here to get this changed. It’s also worth mentioning that right now you can’t combine data from online and on-premises data sources in a gateway either, although it sounds like this limitation will be addressed soon. To work around these limitations you have to import data into separate tables in the dataset and then use DAX calculated tables to combine the data instead – a nasty hack I know, but one that I’ve had to implement myself a few times.

It can be incredibly tempting to avoid the problems associated with data privacy checks by setting Power BI and Excel to ignore them completely. Doing this certainly avoids a lot of headaches and confusion with the Formula.Firewall error message and so on. It also ensures that your queries execute as fast as they can: this is not just because query folding happens whenever possible but because the act of applying the data privacy checks alone can hurt query performance. Recently I saw a case where the only data source used was an Excel workbook (so no query folding was possible) and turning off the data privacy checks made a massive difference to query performance.

However, I cannot recommend that you turn off data privacy checks for all your Excel workbooks and .pbix files by default. Firstly, if you are working with sensitive or highly-regulated data, leaving the data privacy checks in place at least forces you to consider the privacy implications of query folding on a case-by-case basis. On the other hand ignoring data privacy checks by default makes it more likely that you or one of your users will create a query that accidentally sends data to an external data source and breaches your organisation’s rules – or even the law – concerning how this data should be handled. Secondly, if you are a Power BI user and need to use the On-Premises Data Gateway, then you risk creating reports that work fine in Power BI Desktop when the data privacy checks are ignored but which cannot be refreshed after they have been published because the On-Premises Gateway still applies those checks.

]]>https://blog.crossjoin.co.uk/2017/07/04/data-privacy-settings-in-power-bipower-query-part-4-disabling-data-privacy-checks/feed/4cwebbbiimage_thumb[7]imageimage_thumb[6]imagehttps://blog.crossjoin.co.uk/2017/07/04/data-privacy-settings-in-power-bipower-query-part-4-disabling-data-privacy-checks/Power BI, DAX, Data Science & DevOps Training In London This Autumnhttp://feedproxy.google.com/~r/wordpress/Cpjz/~3/ExY0eFHE3wg/
https://blog.crossjoin.co.uk/2017/06/27/power-bi-dax-data-science-devops-training-in-london-this-autumn/#respondTue, 27 Jun 2017 10:34:07 +0000http://cwebbbi.wordpress.com/?p=5149]]>If you’re looking for classroom-based training on Microsoft BI, data science or SQL Server then check out the list of courses I have coming up at Technitrain this autumn:

Introduction to Power BI, taught by me, September 25th-27th – a three day course covering the basics of Power BI suitable for BI pros and business analysts with no previous experience of the product.

Database DevOps, taught by Alex Yates, September 25th-27th – a course for DBAs, developers or anyone who plays a role in writing, testing or deploying changes to SQL Server.

Mastering DAX, taught by Alberto Ferrari, November 27th-29th – a three day introductory course that will teach you how to write DAX calculations and queries in Power BI, Power Pivot and Analysis Services Tabular.

Optimising DAX, taught by Alberto Ferrari, November 30th-1st December – learn about performance tuning DAX from one of the leading experts in the field!

All of the courses are in central London.

]]>https://blog.crossjoin.co.uk/2017/06/27/power-bi-dax-data-science-devops-training-in-london-this-autumn/feed/0cwebbbihttps://blog.crossjoin.co.uk/2017/06/27/power-bi-dax-data-science-devops-training-in-london-this-autumn/Data Privacy Settings In Power BI/Power Query, Part 3: The Formula.Firewall Errorhttp://feedproxy.google.com/~r/wordpress/Cpjz/~3/DSPXHJaBk0s/
https://blog.crossjoin.co.uk/2017/06/26/data-privacy-settings-in-power-bipower-query-part-3-the-formula-firewall-error/#commentsMon, 26 Jun 2017 12:30:00 +0000http://cwebbbi.wordpress.com/?p=5146]]>In the first two parts of this series (see here and here) I showed how Power BI/Power Query/Excel Get & Transform’s data privacy settings can influence whether query folding takes place or even whether a query is able to run or not. In this post I’m going to talk about the situations where, whatever data privacy level you use, the query will not run at all and you get the infamous Formula.Firewall error.

I’ll admit I don’t understand this particular topic perfectly (I’m not sure anyone outside the Power Query dev team does) so what I will do is explain what I do know, demonstrate a few scenarios where the error occurs and show how to work around it.

Assume you have the two data sources described in my previous posts: an Excel workbook that contains just a single day name, and the DimDate table in SQL Server that can be filtered by the day name from Excel. Let’s also assume that both data sources have their data privacy levels set to Public. The following query, called FilterDay, loads the data from Excel and returns a text value containing the day name:

It filters the contents of the DimDate table and only returns the rows where the EnglishDayNameOfWeek column matches the day name returned by the FilterDay query. Notice that there are two steps in the query, Source (which runs a SQL query) and FilteredRows (which does the filtering). Here’s the output:

As you can see from the screenshot, the query runs. In fact it runs whatever data privacy settings you have set on both the data sources, although it’s worth pointing out that if you use your own SQL in an M query (as I do in this case) this stops query folding in all subsequent steps, as described here.

The important difference here is that there is now one step in this query instead of two: the query and the filtering take place in the same step. Even more importantly, regardless of the data privacy settings, the query fails with the error:

Formula.Firewall: Query ‘DimDate With Native Query Single Step Fails’ (step ‘Source’) references other queries or steps, so it may not directly access a data source. Please rebuild this data combination.

The problem here is that the Power Query engine is not allowed to access two different data sources originating from different queries in the same step – as far as I understand it this is because it makes it too hard for the engine to work out whether a step connects to a data source or not, and so which data privacy rules should be applied.

At this point you might think that it’s straightforward to break your logic up into separate steps, as in the first example above. However there are some situations where it’s not so easy to work around the problem. For example, consider the following query:

In this example I’m dynamically generating the SQL query that is being run and passing the name of the day to filter by into the WHERE clause. In the two previous examples the query that was run had no WHERE clause and the filtering on day name took place inside Power BI – in this case the filtering is happening inside the query, so in order to generate the WHERE clause I have to refer to the value that the FilterDay query returns in the same step. Therefore, this query also gives the same Formula.Firewall error seen above.

How can you work around this? Well, the following version of the query that attempts to reference FilterDay in a separate step doesn’t work either:

Luckily, it turns out that if you use the Value.NativeQuery() function to run your query instead you can avoid the error. As I showed here, you can use this function to pass parameters to SQL queries. If you generate the record containing the parameters for the query as a separate step (called ParamRecord here), like so:

There is another way to avoid the error. In all the examples above I have two queries: one to get data from Excel, one to get filtered data from SQL Server. If these two queries are combined into a single query, it doesn’t matter if data from different data sources is accessed in the same step. So, for example, unlike all of the queries above the following query does not reference any other queries; instead it gets the day name from the Excel workbook in the ExcelSource step and then runs the dynamic SQL query in the SQLSource step, and runs successfully:

Clearly the M engine doesn’t get confused about accessing data from different sources in the same step if those data sources are created in the same query.

Of course you can avoid the Formula.Firewall error and make query folding happen as often as possible by turning off data privacy checks completely in the Options dialog. This will be the subject of the next post in this series.