Power BI Custom Data Connectors

Amid all the other announcements at Microsoft Build today came one I’ve been waiting for a long time: we can now build our own custom data connectors for Power BI. Here’s the announcement on the Power BI blog:

https://powerbi.microsoft.com/en-us/blog/data-connectors-developer-preview/

…and here are the docs:

https://github.com/Microsoft/DataConnectors

Although it might not sound that exciting, I think this is one of the biggest things that’s happened to Power BI in a long time. Microsoft’s most successful products are always the ones that are platforms for its partners to build their own solutions on top of, and Power BI is no exception. Custom visuals have made an important contribution to the success of Power BI as a whole, allowing third parties to build visuals that Microsoft either didn’t have the resources to build itself or didn’t realise people wanted. Similarly, custom data connectors will only increase the already impressive number of data sources that Power BI can connect to. My guess is that a lot of SaaS and data providers will want to build their own custom data connectors, and that the number of custom data connectors available is going to explode. Why go to all the trouble of trying to build BI and reporting features into your product when it’s quicker and easier to send the data to Power BI and your users get a much better experience as a result?

This doesn’t just affect Power BI though. Since the new Get & Transform functionality for loading data into Excel (see here) is based on the same Power Query technology that is used by Power BI, these custom data connectors will at some point work with Excel too. Although it will be a while before most Excel users upgrade to a version that supports custom data connectors, when that happens the sheer size of the Excel market means that there will be even more demand for custom connectors and this will in turn strengthen the Power BI ecosystem. Don’t forget that Analysis Services Tabular 2017 also now uses Power Query for loading data, so this will benefit corporate BI users too.

From a purely technical point of view today’s announcement included some welcome new capabilities for M coders like me. These include Visual Studio integration (intellisense for M!) and the ability to connect to web services and use OAuth for authentication, something that wasn’t really possible before. It’s also interesting that you can create custom data connectors that enable DirectQuery connections from Power BI, and that you aren’t limited to importing data.

I can’t wait to start using all these features and I’m sure they will be the subject of many future blog posts. Also, if you’re looking to hire someone with a good understanding of M to build a custom data connector, I’m your guy. You can find my contact details at http://www.crossjoin.co.uk/

Using Text.BetweenDelimiters() To Extract URLs From A Web Page In Power BI/Power Query M

The Add Column By Example functionality that appeared in the April 2017 release of Power BI Desktop is, I have to say, very cool: I’ve used it quite a bit in the last few weeks and it really does work well. One thing I noticed while using it is that three new functions have been added to the M language to support this feature:

  • Text.BetweenDelimiters() which takes a text value and extracts the part that appears between two given delimiter values
  • Text.BeforeDelimiter() which takes a text value and extracts the part that appears before a given delimiter value
  • Text.AfterDelimiter() which takes a text value and extracts the part that appears after a given delimiter value

The functions themselves are quite straightforward and the online documentation has some good examples of how to use them. To save you the click here’s an extra example – the expression:

Text.BetweenDelimiters("Hello *world!??", "*", "!")

…returns the text “world”:

image

As it happens last week I received an email from a reader who wanted to know if it was possible to extract all the links from the href attributes in the source of a web page using M, and I realised that Text.BetweenDelimiters() would be very useful for doing this. I wrote the following M function to demonstrate:

(SourceURL as text, AttributeDelimiter as text) =>
let
	//Get HTML source
    Source = Text.FromBinary(Web.Contents(SourceURL)),
	//Function to find each link
    GetLink = (Counter as number) =>
                    let
                        CurrentLink = 
		Text.BetweenDelimiters(
			Source, 
			"href=" & AttributeDelimiter, 
			AttributeDelimiter,
			Counter
		)
                    in
                        if CurrentLink="" 
		then 
			{} 
		else 
			List.Combine({
				{CurrentLink}, 
				@GetLink(Counter+1)}
			),
	//Call function
    Output = GetLink(0)
in
    Output

A few things to note:

  • I’m using a combination of Text.FromBinary() and Web.Contents() to get the HTML source for the web page whose links we’re extracting
  • Since HTML allows the use of single and double quotes for attributes, I’ve added a parameter to my function called AttributeDelimiter to allow either to be passed in
  • Text.BetweenDelimiters only extracts one piece of text at a time, but you can specify which occurrence of the start delimiter it uses. I therefore used recursion to extract the contents of every href attribute in the HTML: I declare a function called GetLink, and from within that function I can make a recursive call by putting an @ before the function name as in line 22 above. It would probably be better to use List.Generate() instead of recursion here though.

Assuming the query that returns this function is called GetAllLinks

image

…then it can be called in a new query like so:

GetAllLinks(
	"https://msdn.microsoft.com/en-us/library/mt798303.aspx", 
	""""
)

One other thing to point out is how, in order to pass a double quote character to the function as text, since text has itself to be enclosed in double quotes I need to use four double quotes: “”””

The output of this query is a list containing all of the links from the href attributes on the page that are enclosed in double quotes:

image

I guess this could be taken even further to create a function that crawls a series of web pages and returns the links in all of them, then uses the Force Directed Graph custom visual or better still NodeXL in Excel to show which pages link to each other. I’ll leave that to someone else to do though…!

You can download a pbix file with all of the examples in this post here.

Daylight Saving Time And Time Zones In M

Quite a few people (Ken Puls, Reza Rad and today Chris Koester) have blogged about how the M date/time zone functions work in Power Query/Power BI, and the related problem of turning a UTC time into the actual time in any given location taking Daylight Saving Time changes into account. I don’t have much to add to what they have written, but I did learn something new about this subject from a customer last week: it is possible to do the conversion in pure M if the time zone you’re converting to is your PC’s own local time zone.

Here’s an example. In 2017 the clocks in the UK went forward at 1am on Sunday March 26th. Given the following table with two times in it, one of which is 1pm on Saturday March 25th and one of which is 1pm on Sunday March 26th:

image

…if you assume that these times are both UTC, here’s a query that returns the correct equivalent local time for me (because my PC is set to UK time) for both of them:

let
    //Read data from Excel table
    Source = 
	Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    //Change the type of the column to datetime
    ChangeType1 = 
	Table.TransformColumnTypes(
		Source,
		{{"Time", type datetime}}),
    //Turn this into a UTC time
    ConvertToUTC = 
	Table.AddColumn(
		ChangeType1, 
		"UTC", 
		each DateTime.AddZone([Time],0)),
    //Change the data type to datetimezone
    ChangeType2 = 
	Table.TransformColumnTypes(
		ConvertToUTC,
		{{"UTC", type datetimezone}}),
    //Convert to local PC time
    ConvertToLocal = 
	Table.AddColumn(
		ChangeType2, 
		"Local", 
		each DateTimeZone.ToLocal([UTC])),
    //Change type to datetimezone
    ChangeType3 = 
	Table.TransformColumnTypes(
		ConvertToLocal ,
		{{"Local", type datetimezone}})
in
    ChangeType3

image

Here’s a brief explanation of what the query does:

  • First it reads the times from the Excel table and sets the Time column to be datetime data type
  • It then creates a new column called UTC and then takes the values in the Time column and converts them to datetimezone values, using the DateTime.AddZone() function to add a time zone offset of 0 hours, making them UTC times
  • Finally it creates a column called Local and converts the UTC times to my PC’s local time zone using the DateTimeZone.ToLocal() function

As you can see from the output table, the DateTimeZone.ToLocal() function has correctly handled the UK Daylight Saving Time change by converting the UTC time 13:00 on March 25th 2017 to 13:00 UK time, and converting the UTC time 13:00 on March 26th 2017 to 14:00 UK time.

Frustratingly there doesn’t seem to be a way to convert a UTC time to the correct local time in any given time zone in M – the DateTimeZone.SwitchZone() function just adds/removes hours without taking Daylight Saving Time into account, so it’s not really very useful. Apart from all the blog posts linked to at the start of this post, when I posted this question on the Power Query forum I got a very helpful answer from Marcel Beug with a completely different approach to solving the problem here, although it’s still not a straightforward one. If anyone from Microsoft is reading, it would be great to have a new M function to handle this requirement!

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…