Problem Statement
You have imported a dataset from Excel or an SQL database. The dataset has columns containing numeric data which data type is text. Before going ahead and use those columns in your Data Model you need to convert them to decimal numbers (whole or fractional).
Convert string column to numbers in Power Query
- From Power BI Desktop (either the Report, Data or Model Views), pick your table and hit the Transform button.
- The Power Query interface will open up.
- You’ll now see your data in a tabular form as shown below.
- Right click the header of your text column you would like to transform to numeric values. In our Case that will be the Interviews column.
- Then pick the Change Type option and pick Whole Number or Decimal Number as needed.
- When prompted, hit Add a New Step.
- Your column was converted to numeric values as seen below.
- From the Ribbon, hit Close and Apply.
Cast text to whole decimals in Power BI
To convert your string columns to whole numbers or decimals in Power BI, proceed as following:
- From your right hand side Data Panel, search for an select the column you would like to cast.
- Then From the Ribbon modify the Data Type to be Whole or Decimal Number.
- Adjust the number format as needed.
- Acknowledge the data Change Type.
- Go to the Home tab and use the Refresh button to reload your data as needed.
Convert text column using DAX
Proceed as following to use DAX to transform your column to number:
- In the right hand side Data Panel select your table.
- In the Table Tools Menu, hit New Column.
- Type the following DAX expression – modify as needed per your column names
Interviews (Number) = VALUE ('Dates Tutorial Text'[Interviews])
Explanation: The VALUE function in DAX derives the numeric value of a string.