The ‘Get Data’ User Experience in Power BI / Power Query

  • Facebook
  • Twitter
  • LinkedIn

One of the services that we provide here at Powered Solutions is the creation of Power BI Custom Connectors (see here). For the past few months or so I’ve been getting more and more questions related to what type of ‘Get Data’ experience we can have inside of Power BI / Power Query.

More often than not, these questions come from people with previous experience in VBA, Tableau, and other BI tools in the market.

I’ve talked about Parameters and Functions in Power BI / Power Query before (Part 1 | Part 2 | Part 3 | Part 4), and one of the elements that I didn’t fully cover is how Parameters look like and how the end-user can interact with them. In short, what is the ‘Get Data’ User Experience like in Power BI / Power Query?

This is the blog post where I’ll add to that and explain what type of interaction you can have with Power BI Parameters and Custom Connectors. I also highly recommend that you read this documentation from the Microsoft team about Functions and Parameters.

What’s the ‘Get Data’ User eXperience in Power BI / Power Query?

Whenever you go through the ‘Get Data’ window, you end up selecting what is known as a ‘Data Connector’.

Each connector provides their own unique experience. You can define your own connector experience when creating a Power BI Custom Connector, but the most common experience that you’ll find inside of a Power BI Connector is the following:

  1. Connector Parameters: When we first use a connector, we usually get this window that has what it’s called a Connector Parameters.
    These are the set of parameters that will define the connection that will happen against our data source. In the image above, it’s where we set the url for our WooCommerce site and the API version that the site uses.
    1. Fun fact: We call a “singleton” a connector that doesn’t require you any type of Connector Parameters
  2. Authentication phase: More often than not, you’ll get straight to the Authentication phase which is standard for every single connector. The only difference is that some connectors will only have certain authentication methods available while others might have multiple authentication methods available.
    1. If your credentials are already stored in your Power BI Desktop, then you won’t get prompted for this and you’ll go straight to the next phase of your connector.
  3. Navigation Table: This is where you, as the end user, will be able to explore what the data connector can offer you. In some cases this navigator window will offer you multiple levels for you to explore tables, functions and other components that the connector can offer you.
    1. Some connectors will not display this “Navigator” window and will just take you straight into the Power Query editor window. This behavior is not recommended for Power BI Custom Connectors.
  4. Load or Transform phase: this is where you pick what to do with the objects that you’ve selected from the Navigation table / Navigator Window. You can choose to either Load the data directly to your data model or click on the “Edit” / “Transform Data” button to first work with your data inside the Power Query Editor window before loading it to your data model.
    1. It is highly recommended that you always click the “Edit” / “Transform Data” button to see how the data looks like and if you require any transformations or cleaning to happen.

Let’s look at some examples of how some connectors work

The Excel Connector

When we use the “Excel”, “Text/CSV” connectors or other similar connectors that work with a local file, we get taken straight to a folder window to find the file that we want to connect to.

In the past, it used to take us to the “Connector Parameters” window and it’d have a “browse” button to find the file. Once we chose the file, it would take us to the Navigator window where we could see the objects inside the Workbook which would look like this:

  • Facebook
  • Twitter
  • LinkedIn

And it would follow the 4-step experience that we described before.

The Folder Connector

If you choose the “Folder” Connector, then the first window would actually be a simple window that looks like a simple form:

  • Facebook
  • Twitter
  • LinkedIn

After you input your folder path you’ll get another window that will show you the output of that connection:

  • Facebook
  • Twitter
  • LinkedIn

This is essentially a table, so the experience from this connector is different to others because it doesn’t expose a Navigation Table, but rather just a simple table.

With this table you can choose to either “Combine & Transform Data” or simply “Transform Data”, the first one being a unique experience for this Connector

About the Connector Parameters and Parameters in general

In Power BI / Power Query, we can provide a very basic experience through parameters. Check out the next image:

  • Facebook
  • Twitter
  • LinkedIn

One general rule for these parameters is that they can only hold 1 value, meaning that if the parameter is numeric, it can only hold one number. If it’s a text, it can only hold one text string.

Most of these input fields look exactly the same, except for the “myDate” field that has a calendar icon to its right and that’s actually a date picker as shown in the next image:

  • Facebook
  • Twitter
  • LinkedIn

Other than that, most of them are the same except that they have some data validation inside of them. For example, some will only hold text (the “Message” field), others only numeric (the “Count”), others only date values (the myDate one), others only logical or Boolean values (the “myLogical” field).

if you try entering something other than what their data type has been assigned to, you’ll get an error saying that the value is not supported for that parameter.

We essentially only have 3 unique experiences for the standard Power BI data connector parameters:

  • A free-form input field that has some data validation behind the scenes which is available for the following parameters
    • Text
    • Numeric
    • Logical
  • An input field for dates with a date picker on the right
    • Unique for date parameters
  • A dropdown menu so that you can select the value from a list. Available for all parameters

What I’d love to see for Power BI / Power Query Parameters

Often, I see myself trying to find ways to offer my users a way to have some sort of multi-select in some parameters.

For example, imagine a simple parameter for “Country” and I’d like my user to have the possibility to select multiple elements from a list.

An example would be this html form that you see below:

(hold the ctrl key to select multiple values at the same time)

What I’d get from the user input would be some sort of array (or more like a list of values in Power Query terms) and then it’ll be up to me to use that list into my function, but I don’t have that parameter functionality.

Right now, this is impossible. We can only ask the user to select or input a single value for every single parameter.

There’s a bunch of ideas posted on the Uservoice for Power BI. Cast your vote on this idea here so that the Power BI team can implement this.

Let me know in the comments below how valuable this experience would be to you and which connectors you’d love to see this implemented.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.