How do you cast an integer to a string in Excel's PowerQuery

3
votes

I have a PowerQuery which pulls back two columns; one a Number (an integer), the other Text (a string). I'd like to create a custom column which concatenates these.

It seems this should work: ="(" & ([Value] as text) & ", " & [Description] & ")"

But this returns error Expression.Error: We cannot convert the value 1 to type Text.

NB: This question relates to the PowerQuery editor (i.e. the M language); not regular Excel worksheet functions. The worksheet function to achieve what I'm after would be =concatenate("(", A2, ", ", B2, ")"), where A2 is Value and B2 is Description.

2
excelpowerquery
Found It! msdn.microsoft.com/en-us/library/… – JohnLBevan

2 Answers

6
votes

Use the Number.ToText(n) function.

i.e. ="(" & Number.ToText([Value]) & ", " & [Description] & ")"

See https://msdn.microsoft.com/en-us/library/mt253385.aspx?f=255&MSPPError=-2147217396

3
votes

Use Text.From(s). It also supports non-number types. It is useful if you're not sure the type of the input (ex: a user-defined cell).

Returns the text representation of a number, date, time, datetime, datetimezone, logical, duration or binary value. If a value is null, Text.From returns null.

https://msdn.microsoft.com/en-us/library/mt253343.aspx