Miguel Escobar Published September 23, 2019

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

Power BIPower Query

IMPORTANT: You can read the official documentation that Microsoft has on this topic from the following link (url).

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:

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:

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

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:

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:

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.

Power BIPower Query
Subscribe
Notify of
guest
13 Comentario
Oldest
Newest Most Voted
Inline Feedbacks
View all comments
Jane

Thank you for all your training notes & exercises.
Your very hardwork is deeply appreciated

Jay

Hola a todos, como están? tengo una consulta a ver si me pueden ayudar. Es bastante basica, ya que soy novato en esto. Quiero acceder a la base de las transacciones de ventas (se hacen muchas diariamente) para poder relacionar con otras tablas (Clientes,Vendedores, etc) para poder armar un reporte. Como puedo hacerlo? Gracias!

Jay

Gracias! como se cual es mi fuente de datos de todas esas?. Perdón la ignorancia. Gracias!

Jay

Yo necesito que vaya a buscar la ventas del ERP que se utiliza aquí en la empresa, para poder después relacionar con las bases de Clientes; Proveedores; Articulos, etc. Gracias

Jay

Genial gracias, que la consulta que haga y guarde, me la guarde en un archivo .CUB, me dice algo?

Jay

Gracias! como se cual es mi fuente de datos de todas esas?. Perdón la ignorancia. Yo necesito que vaya a buscar la ventas del ERP que se utiliza aquí en la empresa, para poder después relacionar con las bases de Clientes; Proveedores; Artículos, etc. Gracias!

Jay

Hola de nuevo! como están? Tengo una consulta, tal vez me puedan ayudar. Tengo un archivo pbix de varias paginas/hojas/solapas, puedo agregar una hoja para consolidar esa info. Vienen todas de bases de datso distintas (una por empresa), hay alguna manera de consolidar la info en la primer hoja/solapa y que los origenes sean el resto de las hojas? Muchas Gracias.

Akash Gera

Hi Miguel,
I am using REST API to fetch the data from 3rd party using POST method, I have used the same parameters in Header, Body in postman and it is giving me the data in json format, but when I am using the same thing in power query of power bi, it is giving me an error: Response Msg: Invalid APi credentials, Response code:11 & response : NULL, Please have a look in my power query below and tell me what’s is wrong in this :-

let
ApiOwner= “___”,
ApiKey=”________”,
url = “https://dentalkart.vineretail.com/RestWS/api/eretail/v3/sku/inventoryStatus”,
header= [#”Authorization” = ApiKey & ApiOwner,
#”Content-Type” = “application/x-www-form-urlencoded”],

RequestBody=
“{
“”skuCodes””:””[]””,
“”fromDate””:””18/06/2021 15:40:30″”,
“”toDate””:””23/06/2021 15:40:30″”,
“”pageNumber””:””1″”,
“”dropShipFlag””:””no””,
“”locCode””:””
}”,

webdata = Web.Contents(url, [Content = Text.ToBinary(RequestBody), Headers=header]),
response = Json.Document(webdata)

in
response

Any kind of help would really be appreciated !