How to Change Data Type in Power BI?

Do you know how to change the data type in Power BI? Utilizing the Power Query Editor, you can effortlessly modify data types within Power BI.

Recently, I worked on a Power BI report where I had to change the data type. I could do it within three clicks in the Power Query Editor in Power BI.

In this Power BI tutorial, we will learn what Power BI data types are and how to change the data type in Power BI. How to change multiple columns of data types in Power BI simultaneously. Also we learn how to change the data type in Power BI using DAX.

What are Power BI Data Types?

In Power BI, various data types are presented to help users understand and manage their data effectively. Here are some common data types:

  • Decimal Number = This feature allows for numbers with fractional parts, and the number of decimal places is not fixed.
  • Fixed Decimal Number = This feature allows a number with two decimal places.
  • Whole Number = This feature allows only whole numbers without any decimal or fractional parts.
  • Percentage = This feature allows the use of values to store as percentages.
  • Date/Time = This feature allows storing both date and time information.
  • Date = This feature allows the storage of only date information.
  • Time = This feature allows the storage of only time information.
  • Date/Time/Timezone = This feature allows you to store the date and time along with timezone information.
  • Duration = This feature allows you to store the time span like 2 days 1 hour.
  • Text = This feature allows storing words, sentences, or text information.
  • True/False = This feature allows you to store boolean values, True or False.
  • Binary = This feature allows the storage of binary data.

These are the data types presented in Power BI.

How to Change the Data Type in Power BI?

Now, I will tell you how to change the data type in Power BI.

Here, we have a SharePoint list (Financial Sample) that contains below columns with various data types:

ColumnsData Types
ProductSingle line of text
CountrySingle line of text
Units SoldCurrency
SalesNumber
ProfitCurrency
Gross SalesNumber
Sales PriceCurrency
SegmentSingle line of text

Follow the below steps to change the data types.

1. Open Power BI Desktop and load the data using the Get data option. Then, you see data in the Data pane.

power bi change type

2. Under the Home tab, click Transform data.

change data type power bi

3. Next, It will open a new window known as Power Query Editor. In this window, you can observe that “Units Sold” is set as Fixed Decimal Numbers, “Sales” and “Gross Sales” as Decimal Numbers, while the “Sales” column is loaded as the Text data type. (Automatically detected by the Power BI).

Change Data Type in Power BI

4. After that, select the column in which you want to change the Data type (In my case, Units Sold) -> click on the left corner of the column header. Check the screenshot below.

Change Data types in Power BI Desktop

5. Once the dialog box opens with all data types, click Whole Number to change the Units Sold column data type to the Whole Number. Check the screenshot below.

change data type in power bi

6. After that, the Unit Sold column data types change to Whole Number.

change data type of a column in power bi

This way, you can change the data type of a column in Power BI.

Now, I will explain another method for changing data types in Power BI.

1. In the Power Query Editor, Select the column in which you want to change the data type (In my case, Sale Price).

how to change data type of column in power bi

2. Under the Home tab -> expand the Data Type drop-down. Then click Fixed Decimal Number.

power bi change data type

3. After that, the Sale Price column data types change to Fixed Decimal Number.

how to change the data type in power bi

You can also use the above steps to change the data type in Power BI.

Change Multiple Columns Data Types at One Time in Power BI

Now, I will learn how to change multiple columns of data types at one time in Power BI.

1. In Power Query Editor, you can select multiple columns where you want to change the data type by holding down ‘Ctrl’ and clicking on each column. In my case, Sales and Gross Sales.

Checking and Changing Data Types in power bi Power Query Editor

2. Next, right-click on any of the selected headers, then choose Change Type, and finally, select Fixed Decimal Number from the options.

How do you change data type on Power BI Power Query

3. After that, the Sales and Gross Sales column data types change to Fixed Decimal Number.

Change Column Data Type Power Query Editor Power BI

Using this method, you can simultaneously change the data types of multiple columns in Power BI.

How to Change Data Type in Power BI using DAX?

Now, I will show you how to change the data type in Power BI using DAX.

1. Now Go to the Table view.

change date format power bi dax

Here, you can see that the Sales column is not in currency format, so I want to change it to currency format. To do this, follow the below steps.

2. Under the Home tab, click the New column.

dax change data type

3. In the formula bar, click the below expression. Then click Commit.

New Sales = CONVERT('Financial Sample'[Sales],CURRENCY)

Where:

  • New Sales = Name of the Measure
  • CONVERT = DAX Function
  • Financial Sample = Name of the Table
  • Sales = Name of the Column
  • CURRENCY = Data Type
how to change data type in power bi using dax

4. After changing the currency format, you’ll notice a new column added to our dataset with the currency format applied.

Change column data type in Power BI

You can change the data type using Power BI DAX using the above steps.

Example – 2

Now, let’s learn how to convert currency format to an integer. Follow the steps below to do this.

1. Under the Home tab, click the New column.

Power BI Data Types

2. In the formula bar, click the below expression. Then click Commit.

New Units Sold = CONVERT('Financial Sample'[Units Sold],INTEGER)

Where:

  • New Units Sold = Name of the Measure
  • CONVERT = DAX Function
  • Financial Sample = Name of the Table
  • Units Sold = Name of the Column
  • INTEGER = Data Type
How to Change Data Type From Power BI

3. After changing the integer format, you’ll notice a new column added to our dataset with the Integer format applied.

How to replace data type of a column in power bi DAX

This way, we can change the currency format to integer format in Power BI using DAX.

Conclusion

Now, I hope you follow all the steps to change the data type in Power BI.

This tutorial covered the concept of Power BI data types and demonstrated how to change the data type in Power BI, including changing multiple columns simultaneously. Additionally, we learned how to change the data type in Power BI using DAX.

You may also like:

>