Power BI is a powerful data visualization and business intelligence tool that allows users to connect to various data sources, create interactive reports and dashboards, and share them with others. In today’s blog, we will be exploring the most powerful function call in Power BI.

This function call will greatly enhance the capabilities of Power BI and take your data visualization and analysis to the next level. This would let you take the analytical capabilities of Power BI up a bunch of notches and do amazing things all within the context of the Power BI framework. **You can watch the full video of this tutorial at the bottom of this blog.**

## Dataset Function Call In Power BI: Problem Overview

This sample problem is from a site called** Excel BI Nightly Power Query Challenge** which is run by VJ Verma. He puts out a problem every night, and the community solves it and discusses it among members.

In the example, the question has a very simple construction with given upper and lower limits. Between these two numbers, we have to calculate the number of prime numbers in that range.

Though it’s quite simple and straightforward in concept, it’s a lot harder to implement in Power BI. That’s because, among the 800 M functions and around 400 DAX functions, there’s no feature that directly lets us determine whether a number is prime or not in Power BI .

Instead, we will delve into the **Dataset Call** which is a way to move from Power Query to R or Python, execute instructions, and then go back into Power Query seamlessly and continue our analysis.

Let us take the data from the Excel BI challenge and start working.

## Changing The Data Type

Shown below are the Lower and Upper Limits.

These givens are in text format, and we need to change them into whole numbers. To do that, double click on the header row (**Lower Limit or Upper Limit)**.** **In the options that will appear, choose **Change Type** then select **Whole Number. **

The next step is to create a new column for all the numbers between the lower and upper limits. Then, we’ll pivot the column to make a list.

Go to the **Add Column **tab and pick **Custom Column **since there is no way to do this directly through the user interface.

Create some simple M code and make a column named as **Number. **Use the **List.Numbers **function which returns a list of numbers given an initial value, count, and optional incremental value.

Set the initial value to be the **[Lower Limit]** and the count as the **[Upper Limit] – [Lower Limit] + 1** to account for the N point. Make sure that no syntax error is detected. Click **OK. **

A new column with Lists should appear. Click on the **List **to see the numbers in the range at the bottom.

For example, if we click the **List **in the first row, we’ll get a list showing numbers from 10 to 45.

Click the arrow beside **Number **and click **Expand to New Rows.**

Next, click the format icon on the left of **Number **and change the format to **Whole Number.**

At this point, we want to determine if each of these numbers is prime or not. Again, there is no way to do that directly in Power BI. We could program a series of steps that would determine prime numbers but here is an easier way to do that in Python.

## The R Prime Number Package

In R, there are different **packages**. These are add-ons that we can build into the program in order to accomplish certain functions, equivalent to custom visuals or external tools in Power BI.

METACRAN presents a catalog of all the approved R packages. There are over 18,800 active packages in R, but we can also load in directly from the developer site. This means that we can do any analysis within the program by finding the right package.

In our case, we will look and try to find something that will determine whether a number is prime.

Type **prime **in the search box.

Choose the first package named **primes – Fast Functions for Prime Numbers**. This is a package that tests whether a number is prime and generates a sequence of prime numbers.

## Inserting The R Prime Package Using The Dataset Function Call In Power BI

Go back to Power BI and click **Run R Script **from the ribbon**. **

A **Run R Script **wizard will pop-up.

The pound sign (#), which is an indicator for comment, says that the variable ‘**dataset’ holds the input data for the script. **It lets us clean, structure, and shape our data, as well as add columns and pivot things. As soon as we call **dataset**, everything that we have done up to that point gets fed into R. The same thing is true for Python – the data set works the same.

We can take all the knowledge we have on Power Query and do all our shaping there before calling R. This is very helpful because we do not have to learn any R functions that do the same thing as we did in Power Query. Basically, we can go directly into the analytical portion which is what we will demonstrate today.

Let’s start by installing the package. We can use the **install.packages **command, but in this case, we’ll use the **library.**

Type **library **and call the **primes** package. This is equivalent to bringing a custom visual in Power BI from AppSource.

Then, create a table called **df **for data frame and assign the **dataset** table to that.

In Power Query, our dataset is composed of everything from **Source **to **Changed Type 1 **and that was what we assigned to **df.**

Create a new column equivalent to the calculated column in Power BI by typing** df$isprime**. Note that the dollar sign (**$**) in R is similar to the square brackets in Power BI.

**Isprime **will be our column name and to that, we will assign the **is_primes **function** **from the primes package**. **We will then call the **Number **column from **df**.

This takes the number column and runs it through the **is_prime function** to say TRUE if the number is prime, and FALSE otherwise. The function will then put the TRUE or FALSE in the **isprime** column in the **df table**.

Call the **df **table in the end. Click **OK. **

We can see the **df **appear in the **Applied Steps** pane. When we click on it, we should see the **isprime **column which contains the TRUE or FALSE classification.

## Filtering the Prime Numbers

Next, we’ll remove the rows for numbers that are not prime. Click the arrow beside **isprime **to open the filter. Uncheck **FALSE **so we can count the prime numbers.

Click **OK** then go to the **Home** tab and choose **Group By**.

Open the** Advanced** option. In the pop-up window, group by the **Lower Limit **and add a group for the **Upper Limit.** Then, create a column called **Count **and set **Count Rows **as the operation to count the number of TRUE. Click **OK. **

This will yield the result similar to that from the challenge.

******* Related Links *********Create a Perpetually Updated Practice Dataset in Power BI****Three Ways To Use R Script In Power BI****R For Power BI | A Beginner’s Guide**

**Conclusion**

In conclusion, the dataset call is the most powerful function call in Power BI, and is a valuable tool for any user looking to gain insights from their data. This single, simple Power BI function call lets you radically expand the analytical capabilities of the program, literally putting hundreds of thousands of additional functions right at your fingertips from within Power Query.

By understanding how to properly use this function, you can easily manipulate and analyze large sets of data, leading to more effective and informative visualizations. With the insights gained from this function, you’ll be able to make data-driven decisions and improve your overall understanding of your data.

All the best,

Brian Julius

## Be the first to comment