In my quest to check out every last bit of obscure Power Query functionality, this week I looked into the ContextInfo option on the Sql.Database and Sql.Databases M functions. This option allows you to set CONTEXT_INFO in SQL Server (see here for a good article explaining what this is) and here’s an example of how to use it:
Source = Sql.Database(
Query = "SELECT * FROM DIMDATE",
ContextInfo = Text.ToBinary(
This Power Query query runs a simple SQL SELECT statement against the SQL Server Adventure Works DW 2017 database. Note that since you need to pass a binary value to the ContextInfo option, in this example I had to use the Text.ToBinary function to convert my text to binary.
Here’s what happens in SQL Server when this Power Query query is run:
Here’s a simple example of how to retrieve this data on the SQL Server side:
SELECT session_id, login_time, program_name, context_info
I’ll leave it to the SQL Server experts to decide what this can be used for and no doubt to complain that it would be more useful to support SESSION_CONTEXT too – although I’ve heard that might already be used for something, so I need to do more research here…
Back in September I posted about a few new Power BI-related books I was given to review for free; that post led to me getting sent another freebie book, “Extending Power BI with Python and R”, by Luca Zavarella (buy it from Amazon UK here). I found this book particularly interesting because I know very little about Python or R, but I also know that this is a really hot topic for many people and I was curious to know what problems using these languages in Power BI might solve.
I can’t comment on the quality of the Python and R advice (although I’m pretty sure Luca knows what he’s writing about), but from the point of view of a Power BI developer the book does a good job of explaining how using them allows you to do things that are difficult or impossible otherwise. There are chapters on regular expressions, calling APIs, using machine learning models and advanced visualisations. I haven’t seen any other books, videos or blog posts that cover these topics in such detail, so if you have some Python or R skills and want to make use of them in Power BI this book seems to be a good bet.
Over the past few years I’ve blogged and presented extensively on the subject of Power Query’s data privacy settings (see here for a post with links to all this content). I thought I knew everything there was to know… but of course I didn’t, and I’ve recently learned about an issue that can cause mysterious errors.
As always it’s easiest to show an example of how it can occur. Here’s a table of airport names taken from the TripPin public OData feed:
Note how the Location column contains nested values of data type Record, and note that I have not expanded this column.
Here’s another query with sales data for these airports coming from Excel:
Now let’s say we want to join these two queries together using a Merge operation in the Power Query Editor. When you do this, because you are combining data from two different sources and because OData supports query folding, you will be prompted to set data privacy settings on these sources (unless you have already done so at some point in the past) because a Merge could result in data being sent from Excel to the OData source.
If you set the data privacy levels to Private on each source, like so:
…you are telling Power Query that it should never send data from these sources to any other source. As a result, Power Query has to load the data from both sources, buffer that data in memory, and do the Merge inside its own engine.
When you do the Merge everything looks normal at first:
But when you expand the Airports column you’ll see that the nested values in the Location column have been turned into the text “[Record]” and as a result can no longer be expanded.
This is because Power Query has had to buffer the values in the Airports query but it is unable to buffer nested values (I wrote about this here).
There are two ways to fix this. First of all, you can change the data privacy settings or turn them off completely. I don’t recommend turning them off completely because this is only possible in Power BI Desktop and not in the Power BI Service, and even changing the data privacy settings can lead to some unexpected issues later on. For example, if you set the data privacy levels for both sources to Public like so:
…then no buffering is needed (because data can now be sent from one source to another) and the nested values in the Location field can be expanded:
…and of course you do so:
However, people always forget that you have to set your data privacy levels again after publishing your dataset to the Power BI Service. And if you or someone else subsequently sets the data privacy levels back to Private you’ll get the following error in the output of the query:
The error message here is:
“Expression.Error: We cannot convert the value “[Record]” to type Record.”
Depending on the data type of the nested field you might get:
“Expression.Error: We cannot convert the value “[Table]” to type Table.”
“Expression.Error: We cannot convert the value “[List]” to type List.”
The second way to fix the problem is easier and probably safer: you just need to expand the Location column before the Merge operation instead of after it. That way there are no nested fields present when the Merge takes place so all the values can be buffered. Here’s what the Airports table looks like after the Location column has been expanded, before the Merge:
…and here’s the output of the Merge even when the data privacy levels for both sources are set to Private:
In the last few months the following issue has been escalated up to the Power BI CAT team several times: customers have deployed reports into production and then found that users are able to see data they should not be allowed to see by using the “Show data point as a table” feature. The question is: is this a security hole? It isn’t, and in this blog post I’ll explain why and how you should think about security as something that happens on the dataset and not in the report.
Here’s a simple example of the problem. Say you have a dataset with the following table in it:
It contains sales data but the text in the Comments field is sensitive and should not be visible to everyone. If you have a report with a matrix visual in it, put Employee on columns and drag the Revenue field into values and sum it up (ie create an implicit measure rather than defining an explicit measure) like so:
Ooops! Of course it’s bad when an end user sees something they shouldn’t but this isn’t Power BI’s fault. As a Power BI developer it’s important to understand that visibility and security are not the same thing and that data security is something that is defined on a dataset and not in a report. You need to use features such as row-level security and object-level security to stop users seeing data they should not be allowed to see – or you should not import that data into your dataset in the first place. You can stop the “Show data point as table” option from appearing by changing the visual you use in your report or by using an explicit measure (ie one defined using a DAX expression), but that’s still not secure and there’s no guarantee that users would not be able to see the same data some other way.
In our example, with object-level security set up to deny access to the Comments field you can be sure that users will not be able to see that data unless they have permission. When viewing the report via a role with OLS defined then the Comments field will not appear when you use “Show data point as a table”: