Converting an Integer to a Text Value in Power BI

Question

I’m creating a calculated column in a Power BI report. The calculated column concatenates integer and text columns. I tried using the below query to accomplish this, but it resulted in a syntax error.

CalculatedColumn = Number.ToText(table1.[RegionID]) & " " &  table1.[RegionName]

I tried some other conversion methods also, which were not successful. Could someone please guide me on how one could achieve the above objective in Power BI?

Why do you need to convert at all? You should be able to do: `= table1.[RegionID] & " " & table1.[RegionName]` — John Kurlak, Jun 24 '16 at 14:39

score 19 · Answer 1 · edited Dec 07 '16 at 08:37

19

Try

= "Text" & Number.ToText(Number)

edited Dec 07 '16 at 08:37

DreamTeK

32,537
27
112
171

answered Dec 06 '16 at 13:18

Rafael Loureiro

191
1
2

score 10 · Answer 2 · answered Oct 26 '16 at 19:44

10

You must use the function format. The first argument is the value itself, and the second one is the format you want. Use "string", like the code below:

=FORMAT([RegionID], "string") & " " & [RegionName]

answered Oct 26 '16 at 19:44

Daniel Labbe

1,979
3
15
20

9

I am using FORMAT([Year], "string") on an integer year field and always get "0tri0g" – Keith May 10 '17 at 17:23
2

Try `FORMAT([RegionID], "")` insted of `FORMAT([RegionID], "string")` – Michele Bortot Oct 06 '21 at 08:47

score 10 · Accepted Answer · edited Dec 08 '18 at 09:22

10

Make sure you have the correct format string.

Try this:

= FORMAT(table1.[RegionID], "#") & " " & table1.[RegionName]

edited Dec 08 '18 at 09:22

Unheilig

16,196
193
68
98

answered Dec 08 '18 at 09:03

ziviland

124
1
4

score 4 · Answer 4 · answered Oct 05 '18 at 13:11

=FORMAT(numeric_value, string_format) recognises nine formats for the second argument of =FORMAT(), where the type of string format is specified. The 0tri0g error referred to above arises because string itself isn't one of the nine formats. You can see the full list here: https://msdn.microsoft.com/query-bi/dax/pre-defined-numeric-formats-for-the-format-function.

In the original case here, you'd use =FORMAT([Year], "General Number"] to return a year as a four-digit number, stored as text.

score 0 · Answer 5 · answered Oct 27 '16 at 06:49

0

I have created the calculated column as below without any explicit conversion:

Region = table1.[RegionID] + " " + table1.[RegionName]

answered Oct 27 '16 at 06:49

bmsqldev

2,627
10
31
65

If you use this method, you may run into problems trying to '+' (it's not a number). User '&' - for strings instead. – Coffee and Code Sep 02 '19 at 16:14

score 0 · Answer 6 · answered Sep 02 '19 at 16:20

You can use the FORMAT keyword as stated the the above comments.

Just make sure if concatenating strings, use the '&' not '+'. If you still get errors, use format.

Region = FORMAT(table1.[RegionID], "#") & " " & table1.[RegionName]

You can use "string" instead of "#" too.

Converting an Integer to a Text Value in Power BI

6 Answers6