To cast a Power BI column from integer or decimal format to string texts use the following DAX code:
Your_text_column = FORMAT(your_table[your_number_column], "#")Import dataset to Power BI
I will first go ahead and import a sample office list from an Excel file managed by the Real Estate folks.
Here is the data set that we have acquired into the Power Bi data model:
Concatenate columns in Power BI
My goal was to define an Office_Code field that contains the combined values of the three columns of our data set.
- In Power BI, highlight your table (on our case its name is offices) in the Data pane.
- Open the Data view
- From the Calculations section, hit the New Column button.
- In the formula bar, type the following expression:
Office_Code = offices[Office_Area] & offices[Office_Number] & offices[Office_Size]- This will render the following result – note that the Office_Code values contains dots. which won’t be too helpful here.
Convert numbers to strings with FORMAT
Luckily, i could use the FORMAT function to get rid of the decimal notes in the concatenated column.
- In Power BI, navigate to the Data tab.
- Then, go ahead and write the following expression:
Office_Code = offices[Office_Area] & offices[Office_Number] & FORMAT(offices[Office_Size], "#")Explanation:
- offices – is the table name
- Office_Code – is the new column we are creating.
- Office_Area, Office_Number and Office_Size are the columns we are combining.
- We are using the digit numeric format placeholder # to get rid of the decimal dot.
This will render the correct result:
