RelativePath/Query Option in Power BI

This blog post will provide a comprehensive guide or content to relative queries in Power BI. We will cover the following topics:

What is a RelativePath/query?
How do RelativePath & queries work?
Examples of relative queries

What is a RelativePath/query?

RelativePath & Query are two powerful options that can be used to construct URLs for web service calls in Power BI. They are both records that can be used to add extra text or query parameters to the base URL given in the first parameter for the Web.Contents() function.

RelativePath allows you to add extra text to the base URL. This can be useful for constructing URLs for APIs that have a complex structure. For example, you could use RelativePath to add a resource path or version number to the base URL.

Query allows you to add query parameters to the URL. Query parameters are key-value pairs that can filter or sort the results of the API call. For example, you could use Query to filter the results by a specific date range or to sort the results by one particular field.

RelativePath and Query can make your code more readable, maintainable, and efficient. It can also help improve your code’s performance by reducing the HTTP requests needed.

How does RelativePath/query work?

We can use RelativePath/Query in Power Query with some code amendments under the advanced editor.

A relative path in Power Query is a path to a file or folder relative to the workbook’s current location. This means that the path does not include the full path to the file or folder but rather the path relative to the current workbook.

Relative Query is the key to getting data from web pages and web services and has several useful options that make it easier to construct URLs for your web service calls.

Let us move to an example to understand it in a better way:

RelativePath:

So here we are trying to find a solution to load an external data file but from a relative path so that when someone else opens the PBIX, it will still work on their computer.

To ease the pain, we can create a variable that contains the path where the files are located and use that variable to determine the path of each table. That way, you only need to change a single place (that variable), and all the tables will automatically point to the new location.

Create a Blank Query, give it a name (e.g. – TestRelativePath), and type in the path where your files are (E.g., C:\Users\username\Desktop)

You will get a dialogue box to define the path for the query.

With the variable created, edit each of your tables in the Advanced Editor and concatenate your variable with the file’s name.

e.g., instead of ” “C:\Users\username\Desktop\Sales Table (1).xlsx” “, change it to TestRelativePath & “\ Sales Table (1).xlsx “

This will help us in using RelativePath in Power Query with Power BI.

RelativeQuery

Let us take another example or scenario to understand the concept of RelativeQuery in Power BI.

Consider the API below, which we call the data in Power BI.

https://mia-lucy-next.mybluemix.net/datapoint/InstanceData?page=0

Lucy is a chat tool used by an XYZ company. We had an API to get the data for how Lucy performs regarding end users’ query resolution. We can use this API to get the data in Power BI to visualize the report. If we notice the API, we can see a page param has been used in the last part of API, which contains a number as zero here. API is limited to 10K rows per page; page zero will always contain the latest 10K rows by default.

If we need more historical data from this API, we must keep changing the page parameters going forward as 1, 2, 3, and till the page where the data is available.

https://mia-lucy-next.mybluemix.net/datapoint/InstanceData?page=1

https://mia-lucy-next.mybluemix.net/datapoint/InstanceData?page=2

https://mia-lucy-next.mybluemix.net/datapoint/InstanceData?page=3

Changing page parameters and using multiple URLs in a single Power BI report is not good. To overcome this issue, we will use the RelativeQuery concept.

Steps to follow:

Create a parameter for a page with the default value as page=0; the type should be text.

Let us pull the data now using default API; at the end of API, we can pass the parameter name like the image below.

Now, let us create a custom function by right-clicking on the query.

In this custom function, we need to edit the RelativePath option in an advanced editor like we did here. Here, we are referencing our API with our parameter, which will get the data for respective pages based on availability.

In the next step, we must manually enter some data for page parameters in the report. We can use the page param till 50, or it will also depend on the size of the data uploaded in APIs. The data type of this table should be text. We can name this table as Master_data.

Invoke custom function in Master_data table.

After invoking the custom function, the pages containing the data should be visible, and the rest will show an error. Here, we can filter out the error values. After expanding the custom function, we will get data for all the pages in one query.

Benefits of using RelativePath and Query

They make it easier to construct complex URLs.
They make your code more readable and maintainable.
They can help to improve the performance of your code.

RelativePath and Query are two powerful options that can be used to construct URLs for web service calls in Power BI. By using these options, you can make your code more readable, maintainable, and efficient.

TekLink International LLC.