New Parameter, Transpose, Reverse Rows And Extract In Power BI

Introduction

In this article, we will learn about the New Parameter – What If in Power BI. We will learn the definition, creating and using New Parameter – What If in Power BI. We will also learn about the transpose, reverse rows and extract in Power BI. These are the parts of data transformation. We can do all these functions by SQL or other queries but it takes lots of effort while in Power BI and we can do it by following easy steps efficiently.

New Parameter – What If

New Parameter is also called What-if Parameter or What if variable. It is included in Power BI with the August 2017 release. It is the part of Power BI data modeling available on Modeling tab. It is used as a variable value. Its value can be set by slicer. It is also used as a numeric slicer. Once what if variable is created we can use it throughout the table, report or slicer as it is a part of model. Its value can be dynamically changed.

For this article an excel sheet has been taken as data source. To know more about how to use excel sheets as a data source in Power BI desktop please have a look at my previous article,

• Implementing Power BI – Part 1

Creating New Parameter

New Parameter is the part of Modeling. For creating it click on New Parameter as below.

Modeling – New Parameter What If

 

After clicking on New Parameter below configuration window will open for configuring New Parameter as below -

• Point 1 – Name: It is the name of New Parameter.
• Point 2 – Data type: It is the data type of New Parameter and can be either whole number, decimal number or fixed decimal number.
• Point 3 – Minimum: It is the start value or minimum value of the parameter.
• Point 4 – Maximum: It is the end value or maximum value of the parameter.
• Point 5 – Increment: It is the value by which it will be incremented. Default value of increment for whole number is 1. Increment value also can be set as 0.
• Point 6 – Default: It is the default value of the parameter.

Add slicer to this page checkbox

Once you check this option, it automatically adds a slicer in report visualization report page.

 

After configuring and putting all these above values click on OK button. It will create a New Parameter as below. 

 

Creating Relationship between What If parameter and table 

We can create New Parameter and reference it to table. Below steps are used for creating relationship of New Parameter and table.

Click on Relationships as shown in below image.

 

Now drag Tax Rate and drop it to on Gax Rates column. Now the relationship has already made between them as shown below.

 

To know more about creating and using relationship in Power BI you can refer my previous article,

• Creating and Managing Relationships in Power BI

Using New Parameter as Slicer

As stated above New Parameter can be used as a numeric slicer. So we will create a table and then use New Parameter as slicer. For this click on table option of VISUALIZATIONS available on report section and drag the value of column in values part of table. Now enter the value in What If parameter and click on enter. The result after filter based on the numeric slicer will be displayed as shown below.

 

Now you can see I have entered 12 in new parameter values. So, all the products with GST rates 12 are displayed because I have given reference and created relationship of new parameter with GST rate column.

We can also directly use New Parameter with table without giving its reference.

Transpose

This is used to reverse the rows and column of a table. Once you select the column and click on transpose option the rows becomes columns and column becomes rows. For using it click on Transpose option of Transform tab as below.

The result of Transpose becomes as below. The default name of column transpose remains Column1, Column2, Column3 and so on.

We can change the name of column by clicking on Use First Row as Headers. This option comes after clicking on table icon of table as below. After using this option the name of column become the column data as available. It is shown in below image.

Reverse Rows

It reverses the rows in a table in such a way that the last row becomes first row. So, the last row displayed first. For working with reverse rows first we create an index column to the table as below. To know more about index column in Power BI you can refer my previous article 

• Conditional Column Index Column and Duplicate Column in Power BI

 

Now click on reverse rows it will give the result as below

Transform – Reverse Rows

 

 

Extract

This is the part of data transformation in Power BI. It is used to extract characters from text value. We can use it to extract character from table. We can implement Extract by following methods like Length, First Characters, Last Characters, Range, Text Before Delimiter, Text After Delimiter and Text Between Delimiter. For using these functions select one of the available option in Extract of Power BI query editor’s transform tab. It is as below in image.

Transform – Extract

Let’s discuss each Extract method separately.

Length

It gives the length of text in selected column. The gives the integer value as result. For using length select the column and click on

Transform – Extract – Length

It will give the following result as in below image

First Characters and Last Characters

Once you click on first characters or last characters following window will open to enter number of starting and ending characters. It asks to enter numeric value as below. If you enter another value like text, blank or any special character then it gives error. You can enter 0 as number of character.

For First Characters option it opens a window which is as below. Enter value and click on OK.

The output of First Characters will be as shown in below image

Range

It returns a specified number of characters from each value in this column, starting at a specified index. Once you enter starting index and number of characters to be extract it will give below output.

 

Text Before Delimiter and Text After Delimiter

Both above option returns the text. Text Before Delimiter returns text which appears before the delimiter while Text After Delimiter returns the text which appears after the specified characters.

 

The result will be as shown in below image

 

You can also use advance option for using Text Before and Text After delimiter option. You can set number of delimiters to be skipped and select either from the start of the input or from the end of the input as below. The default value for number of delimiter to be skipped is 0.

Text Between Delimiters

It returns the text between two delimiters as output. You can use it as you have used Text Before and Text After delimiter with the advanced option as below.

In the above window you need to enter the start delimiter and end delimiter. You can use either the same or a different delimiter in start delimiter and end delimiter.

Summary

In this article, we have learned about the New Parameter or What If variable in Power BI. We have learned how to create and use a new parameter. Apart from New Parameter we have learned about the Transpose, Reverse Rows and extract in Power BI.

Hope you learned and enjoyed from this article. You’re welcome to like, comment, share and any type of suggestion for my appreciation.