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:
Columns | Data Types |
---|---|
Product | Single line of text |
Country | Single line of text |
Units Sold | Currency |
Sales | Number |
Profit | Currency |
Gross Sales | Number |
Sales Price | Currency |
Segment | Single 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.
2. Under the Home tab, click Transform data.
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).
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.
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.
6. After that, the Unit Sold column data types change to Whole Number.
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).
2. Under the Home tab -> expand the Data Type drop-down. Then click Fixed Decimal Number.
3. After that, the Sale Price column data types change to Fixed Decimal Number.
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.
2. Next, right-click on any of the selected headers, then choose Change Type, and finally, select Fixed Decimal Number from the options.
3. After that, the Sales and Gross Sales column data types change to Fixed Decimal Number.
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.
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.
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
4. After changing the currency format, you’ll notice a new column added to our dataset with the currency format applied.
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.
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
3. After changing the integer format, you’ll notice a new column added to our dataset with the Integer format applied.
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:
- Difference Between Calculated Column and Measure in Power BI
- Power BI Conditional Formatting Based On Field Value
- How to Convert Date to Text in Power BI
- Power BI Group by Column
I am Bijay a Microsoft MVP (10 times – My MVP Profile) in SharePoint and have more than 17 years of expertise in SharePoint Online Office 365, SharePoint subscription edition, and SharePoint 2019/2016/2013. Currently working in my own venture TSInfo Technologies a SharePoint development, consulting, and training company. I also run the popular SharePoint website EnjoySharePoint.com