Quick Tip: Working With Multiple Power BI Subscriptions/Accounts

As a consultant I frequently work with customers who want to add me to their Power BI tenant so I can publish reports, monitor data refresh and so on. However, this means I end up with many different Microsoft “Work or School” accounts, one for each customer, and signing in and out of each one can be a real pain. I use Chrome as my main browser so instead I create one profile in Chrome for each customer – it makes it very easy to switch between Power BI accounts without having to sign in and out.

For more details, see the Google documentation here:

https://support.google.com/chrome/answer/2364824

…and here’s the blog post by James Callaghan on using this feature with multiple Office 365 subscriptions that I got the idea from:

https://jcallaghan.com/2014/06/login-multiple-office-365-azure-accounts/

It looks like you can do something similar with other browsers too.

Thoughts On Visio And How It Could Integrate With Power BI

The recent release of Visio Online (which means most people with an Office 365 subscription can now see Visio diagrams in the browser) and updates for Visio Pro for Office 365 (the desktop version of Visio, needed for creating diagrams, available on subscription but annoyingly not included in any of the Office 365 Enterprise plans) made me wonder if there’s anything new for BI pros in the latest version of Visio. This is a subject I come back to every few years – the last time was in back in 2013 – but I’m still a bit disappointed by some aspects of Visio when it comes to working with data even though there’s a massive amount of potential there.

To recap, there are actually two features of Visio that are interesting for BI purposes. First, you can import data into Visio and then link rows of data to shapes in your diagram (see here for a basic intro; this presentation has a lot more detail and is a must-read) to create data-linked diagrams; if your data source is an Excel workbook stored in SharePoint Online/OneDrive for Business or a SharePoint Online list then you can even refresh the data in Visio Online in the browser. While it doesn’t do exactly the same thing, the fact that the Synoptic Panel custom visual for Power BI is so popular shows that people really like the idea of seeing their data visualised in diagram form. If you know what you’re doing, you can do some really amazing stuff with Visio data-linked diagrams: not just change shape colours but also their formatting, geometry, size and more.

Secondly there are Pivot Diagrams, the Visio equivalent of Excel’s PivotTables. This blog post by David Parker is a really good guide to what they can do; like PivotTables they can even be linked direct to SSAS. The enduring popularity of the old Proclarity Decomposition Tree and the fact that so many people want something similar to be added to Power BI shows the appeal that this way of analysing data has for BI pros and Visio Pivot Diagrams are much more powerful than any custom visual the Power BI team could hope to develop.

So where does Visio fall short and what could be done about it? Here are some thoughts:

  • The data import options are limited, both in terms of where you can get data from and what you can do with it after that. These are all problems that Excel has solved in recent years with Power Pivot and Power Query, so why not borrow what Excel have implemented? Power Query/Get & Transform functionality for data loading and transformation, and a Visio Data Model (like the Excel Data Model/Power Pivot) would solve all these problems at a stroke and mean that anyone who had experience with Excel or Power BI would be able to move over to using Visio for BI easily.
  • Publishing to Office 365 is all well and good, but publishing to Power BI would also be useful. This might involve:
    • The ability to publish Visio diagrams to Power BI in the same way we can publish Excel workbooks to Power BI today.
    • A Visio equivalent to “Analyze in Excel” so you could create Pivot Diagrams connected to Power BI datasets. This should be straightforward to implement because Pivot Diagrams already work with SSAS, so all you would need to do is fire the same MDX at Power BI.
    • Once published, the ability to pin Visio diagrams to a Power BI dashboard.
    • The ability to schedule data refresh in the same way you can today with Power BI datasets and Excel workbooks.
  • The separate and relatively pricey ($13USD per month) subscription needed for Visio on the desktop suggests that Microsoft now thinks of it as a niche product, not something that even a top-of-the-range Office 365 E5 subscriber gets by default. I guess they’re allowed to make that assumption but it will be very hard for a BI pro to justify that expense for a product that isn’t essential for their job. Adding Visio into the top Office 365 SKUs would expose it to a much wider audience including BI pros. Incidentally the same argument can be made about Visio’s newly re-acquired ability to create diagrams from relational databases: it looks nice, but how many DBAs will be able to justify the cost of a subscription just for it?

In summary, while I know that I’m guilty of looking at Visio from a BI pro point of view and that data visualisation isn’t necessarily Visio’s primary purpose, I do think both Visio and Power BI could both benefit a lot from closer integration just as Excel and Power BI have done in recent years. With a bit of investment from both teams we could have another killer feature added to Power BI, one that none of Microsoft’s competitors could begin to match.

Power Query/Get & Transform: The Future Of Data Loading In Excel

Last week, Dominic Petri shared a link on Twitter that contained some important news from Microsoft: as of the March release of the Office 365 click-to-run version of Excel 2016, the old methods of loading data into Excel will be replaced by the newer “Get & Transform” functionality that is better known as Power Query. All the details are here:

https://support.office.com/en-us/article/Unified-Get-Transform-ad78befd-eb1c-4ea7-a55d-79d1d67cf9b3?ui=en-US&rs=en-US&ad=US&fromAR=1

Up to now Excel 2016 users have had to deal with a rather confusing compromise whereby the older data loading functionality existed side-by-side with the new “Get & Transform” functionality on the Data tab of the ribbon. From the March update onwards though the Data tab will look like this:

Get & Transform options on the Data tab

There are also several other cool new features being introduced, such as the ability to export Power Query queries to .odc files so they can be shared between workbooks, and new UI for managing connections and queries. You’ll still be able to get the old data loading functionality back via the Options dialog if you want.

If you’re a regular reader of this blog you’ll know that I’m a massive fan of Power Query, so naturally I’m really happy to see this happen. I believe it’s a big step forward for Excel in terms of its BI and reporting capabilities and the fact that this functionality is almost identical to the data loading functionality in Power BI means that Microsoft’s overall BI story becomes even more compelling: the skills you learn in Excel are transferable to Power BI and vice versa. On the other hand I can guess this is going to cause confusion for some users – similar to what happened when the ribbon first appeared in Office 2007 – but I don’t think this can be avoided. Ultimately the benefits will outweigh any disruption caused as users have to learn the new way of doing things.

Exporting Your Queries’ M Code From Power Query And Power BI Using Copy/Paste

Two years ago I blogged about a method to export all the M code for all of your queries in Power Query using the Send A Frown button – useful if you need the code for documentation purposes. This trick doesn’t work with Power BI Desktop, unfortunately, but the good news is that there’s a better way to do this now in Power Query and Power BI Desktop using copy/paste. It’s pretty simple really: when you copy a query from the Power Query or Power BI Desktop Query Editor you can not only paste the query to another Query Editor (pasting from Power Query to Power BI and vice versa works too) but you can also paste the query to a text editor like Notepad and get the M code for the query. What’s more, you can also select more than one query in the Query Editor and when you paste you get all the code for all of the selected queries:

image

Remember that because the properties of each step in a query become comments in your M code, they get copied too.

Thanks to VossF for telling me about this on this thread.

The List.* M Functions And The equationCriteria Argument

Last week a reader left a comment on my blog asking what the third argument of the List.Contains() M function (somewhat cryptically called equationCriteria) does. I didn’t know, so I did some research and found out that lots of the List.* functions have the same argument. The documentation for List.Distinct() has a few examples but no real explanation of how they work. It also says:

For more information about equationCriteria, see Parameter Values.

…but there’s no link or indication where the documentation on ‘Parameter Values’ can be found. A bit more digging led me to the bottom of this page:

Equation criteria

Equation criteria for list values can be specified as either a

  • A function value that is either

    • A key selector that determines the value in the list to apply the equality criteria, or

    • A comparer function that is used to specify the kind of comparison to apply. Built in comparer functions can be specified, see section for Comparer functions.

  • A list value which has

    • Exactly two items

    • The first element is the key selector as specified above

    • The second element is a comparer as specified above.

Still not exactly helpful, is it? After a bit of time testing, though, I think I’ve worked out how what’s possible with the equationCriteria argument and this blog post will, I hope, help any future M coders who are struggling with the same question. Let’s see some examples…

The basics

First of all, the basics. The following expression using List.Contains() returns TRUE because the text value “apples” appears in the list {“apples”, “oranges”, “pears”}:

List.Contains({"apples", "oranges", "pears"}, "apples")

image

The following returns FALSE because the text value “grapes” does not appear in the list {“apples”, “oranges”, “pears”}:

List.Contains({"apples", "oranges", "pears"}, "grapes")

image

However there are lots of different ways that text values can be compared and the equationCriteria argument allows you to specify which rules to follow.

Case sensitivity and culture

If you’ve written any M code you’ll know that it is case sensitive. As a result, the following returns FALSE:

List.Contains({"apples", "oranges", "pears"}, "Apples")

What happens if you want to do a case-insensitive comparison though? This is where the Comparer functions come in. The Comparer.FromCulture() function returns a function that compares two values according to the rules of a given culture or locale and optionally ignore case, and can be used in the equationCriteria argument. The following example returns TRUE:

List.Contains(
	{"apples", "oranges", "pears"}, 
	"Apples", 
	Comparer.FromCulture("en-GB", true)
)

In this case Comparer.FromCulture("en-GB", true) returns a function that compares two values for the English – Great Britain culture (for a full list of culture values, see the Language Tag column of the table on this page); the second, optional argument here makes the function ignore case when making the comparison. The function that Comparer.FromCulture() returns is then used by List.Contains() to make the comparison.

Rather than specify a culture you can also use the Culture.Current function to return the current system culture. For me, Culture.Current returns the value “en-GB” because I live in Great Britain and have my PC configured to use a British English locale:

image

The following example shows how Culture.Current can be used with Comparer.FromCulture and also returns TRUE, at least for me:

List.Contains(
	{"apples", "oranges", "pears"}, 
	"Apples", 
	Comparer.FromCulture(
		Culture.Current, 
		true
	)
)

If you’re curious to see an example where different cultures produce different results here’s one I stole from this article on string comparisons and sorting in .NET. Apparently in English the character æ is treated the same as the combination of the two characters ae but this is not the case in Danish. As a result the following returns TRUE:

List.Contains(
	{"aepples", "oranges", "pears"}, 
	"æpples", 
	Comparer.FromCulture(
		"en-GB", 
		true
	)
)

Whereas this returns FALSE:

List.Contains(
	{"aepples", "oranges", "pears"}, 
	"æpples", 
	Comparer.FromCulture(
		"da-DK", 
		true
	)
)

Ordinal comparisons

If you don’t want all the uncertainty of cultures and case sensitivity you can just make an ordinal comparison, which will compare two strings by finding the unicode character value for each character in each string and compare those values. To do this you can use the Comparer.Ordinal() function. The following returns FALSE:

List.Contains(
	{"apples", "oranges", "pears"}, 
	"Apples", 
	Comparer.Ordinal
)

…because “a” is not the same unicode character as “A”, and so “apples” and “Apples” are not treated as equal.

Custom comparer functions

As the documentation hints you can also write your own function to do the comparison. A comparer function is just – as far as I can see – a function that has two arguments and returns a logical value. Here’s an example of a custom function that takes two text values, x and y, and returns true if the first three characters of x are the same as y:

(x as text, y as text)=>Text.Start(x,3)=y

It can be used with List.Contains() as in the following example, which returns TRUE:

List.Contains(
	{"apples", "oranges", "pears"}, 
	"app",
        (x as text, y as text)=>Text.Start(x,3)=y
)

What must be happening here is that the function is called three times, every value in the list {“apples”, “oranges”,”pears”} is being passed to the x argument and for each call “app” is passed to y; because the first three characters of “apples” are “app” the function returns true in this case, so List.Contains() returns true.

Key selectors

If you’re working with a list of records you might only want to do the comparison on one field in the record, and this is what key selectors allow you to do. The following example, which returns TRUE:

List.Contains(
	{[Fruit="apples", Colour="Red"], 
         [Fruit="oranges", Colour="Orange"],
         [Fruit="pears", Colour="Green"]}, 
	[Fruit="apples", Colour="Russet"],
        each [Fruit]  
)

…does so because it only compares the Fruit field in each record, and the Fruit fields in [Fruit=”apples”, Colour=”Red”] and [Fruit=”apples”, Colour=”Russet”] are indeed the same. However the following example returns FALSE:

List.Contains(
	{[Fruit="apples", Colour="Red"], 
         [Fruit="oranges", Colour="Orange"],
         [Fruit="pears", Colour="Green"]}, 
	[Fruit="apples", Colour="Russet"],
        each [Colour]  
)

…because the Colour “Russet” does not appear anywhere in the Colour field of any of the records in the first parameter.

Combining key selectors and comparison functions

Finally, as the documentation suggests, you can combine the above methods of comparison by passing a list containing two items to equationCriteria: the first item in the list must be a key selector, the second must be a comparer function. For example, the following returns TRUE:

List.Contains(
	{[Fruit="apples", Colour="Red"], 
         [Fruit="oranges", Colour="Orange"],
         [Fruit="pears", Colour="Green"]}, 
	[Fruit="Apples", Colour="Russet"],
        {each [Fruit], Comparer.FromCulture("en-GB", true)} 
)

…because it only looks at the Fruit field of each record, and it does a case-insensitive comparison using the en-GB culture, so “apples” and “Apples” are equal.

Record.AddField(), Functions And The Delayed Option In M

Today I was looking at the Record.AddField() M function and saw it had a mysterious – and badly documented – optional fourth argument called delayed. Of course I had to work out what it did, and having done so I thought I’d blog about it in case someone found it useful (and however obscure the topic, there’s always someone out there who finds this stuff useful).

Imagine you have an M function called ReturnANumber that has no arguments at all and returns the number 999:

() as number => 999

image

Now imagine that you want to return this function in a record (which is exactly what #shared does). The following expression:

[MyFunction = ReturnANumber]

…returns a record with one field whose value is of type function:

image

But what if you wanted the field to contain the number that the function returns, not a reference to the function itself? The delayed option of Record.AddField() allows you to do this: if you set it to true, you get the value the function returns.

Record.AddField([], "MyFunction", ReturnANumber, true)

image

Setting delayed to false does the same as the first example above:

Record.AddField([], "MyFunction", ReturnANumber, false)

image

Another way to get the same result as setting delayed to true is to use Function.Invoke():

[MyFunction = Function.Invoke(ReturnANumber,{})]

image

Now I need to think of a real-world use for this…

Web.Contents(), Caching And The ExcludedFromCacheKey Option In Power BI And Power Query

When you’re using the Web.Contents() M function to call a web service from Power Query or Power BI, you don’t necessarily get one HTTP request each time you call the function: some caching takes place, so that if you make the same request multiple times your query won’t waste time asking for the same data over and over. In this post I’m going to share the results of some tests I made to show how caching works with Web.Contents() and what factors influence it.

For my tests I built a simple web service in Microsoft Flow, similar to the one I blogged about here, that accepts a HTTP POST request and calls a stored procedure in an Azure SQL Database. The stored procedure then updates a table in the database and this in turn allows me to count the number of times the web service is called. Finally, the web service returns the value 0 if the stored procedure has executed successfully.

This web service can then be called from either Power Query or Power BI using the Web.Contents() function, something like this (because the URL for the web service is very long I stored it in a parameter called WebServiceURL):

let
    Source = Web.Contents(
	WebServiceURL,
	[Content=Text.ToBinary("Hello")]
	),
    #"Imported JSON" = Json.Document(Source,1252)
in
    #"Imported JSON"

The output of the query when run in Power Query and loaded to an Excel table is this:

image

The first important thing to point out is that the above query, when refreshed in the latest versions of Power Query (I’m running Excel 2016 build 7571.2109) and Power BI (build 2.41.4581.361- November 2016 release), results in a single call to the web service. It might seem like I’m stating the obvious but in the past I’ve seen plenty of cases where a data source has been queried multiple times by Power Query/Power BI even if I was only expecting it to be queried once.

Now, let’s look at a query that calls this web service several times. Here’s the query above converted to a function called fnCallWebService:

() => 
let
    Source = Web.Contents(
	WebServiceURL,
	[Content=Text.ToBinary("Hello")]),
    #"Imported JSON" = Json.Document(Source,1252)
in
    #"Imported JSON"

Here’s a query that calls this function once for each row of the following table:

image

let
    Source = Excel.CurrentWorkbook(){[Name="MyTable"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(
	Source,
	{{"Row", Int64.Type}}),
    #"Invoked Custom Function" = Table.AddColumn(
	#"Changed Type", 
	"fnCallWebService", 
	each fnCallWebService())
in
    #"Invoked Custom Function"

In the query above I used the Invoke Custom Function button to call the function for each row in the source table and put the value returned by the function in a new column. The output is this:

image

Even though the function is called four times, once for each row in the original table, that does not mean that the web service is called four times – it isn’t, it’s only called once. In this case Power BI/Power Query knows that each of the four calls to the function is making an identical request to the web service and so it only goes to the web service once, and thereafter uses a cached result the other three times.

One way to stop this caching from taking place is to add an HTTP header to the request to the web service and pass a different value to that header for each call. Here’s another version of my function, now called fnCallWebServiceWithHeaders, which this time takes a number as a parameter and then passes that number to the web service via a header called MyHeader:

(RowNum as number) => let
    Source = Web.Contents(
	WebServiceURL,
	[Content=Text.ToBinary("Hello"), 
	Headers=[MyHeader=Text.From(RowNum)]]),
    #"Imported JSON" = Json.Document(Source,1252)
in
    #"Imported JSON"

Now if I call this function for each row of the table, and for each call pass the value in the [Row] column through to the function like so:

image

let
    Source = Excel.CurrentWorkbook(){[Name="MyTable"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(
	Source,
	{{"Row", Int64.Type}}),
    #"Invoked Custom Function" = Table.AddColumn(
	#"Changed Type", 
	"fnCallWebServiceWithHeaders", 
	each fnCallWebServiceWithHeaders([Row]))
in
    #"Invoked Custom Function"

…the web service gets hit four times. The presence of a different value for the MyHeader header in each request is enough to prevent any caching from taking place.

It is possible, however, to get Power BI/Power Query to ignore one or more headers when working out whether caching should take place using the ExcludedFromCacheKey option in Web.Contents(). Here’s one more version of my function, now called fnCallWebServiceWithHeadersExlCache, which uses this option:

(RowNum as number) => 
let
    Source = Web.Contents(
	WebServiceURL,
	[Content=Text.ToBinary("Hello"), 
	Headers=[MyHeader=Text.From(RowNum)], 
	ExcludedFromCacheKey={"MyHeader"}]),
    #"Imported JSON" = Json.Document(Source,1252)
in
    #"Imported JSON"

The ExcludedFromCacheKey option takes a list of text values which represent the names of headers that are to be ignored when considering which requests can be cached. In the example above my list contains just the one header, MyHeader, and when this is used in a query like so:

let
    Source = Excel.CurrentWorkbook(){[Name="MyTable"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(
	Source,
	{{"Row", Int64.Type}}),
    #"Invoked Custom Function" = Table.AddColumn(
	#"Changed Type", 
	"fnCallWebServiceWithHeaders", 
	each fnCallWebServiceWithHeadersExlCache([Row]))
in
    #"Invoked Custom Function"

…even though the function is called four times, once for each row in the table, and even though each time Web.Contents() is called it is with a different value passed through to the MyHeader header, only one request is made to the web service and the three subsequent requests are answered from cache again.

In summary, if you’re calling a web service multiple times in a query and especially if you’re using the Headers option in Web.Contents(), this is important stuff to understand because caching can make a big difference to the performance of your queries.