Desktop

[Joak]

Hello, 

In power query editor, I have to concatenate three of my columns which contains dates, numbers and letters. 
It should work as the code is as simple as = [Column1]&[Column2]&[Column3] 

However I get an issue message from power Bi ntfying me that the operator cannot be applied to number and date types. 

I see that : 

'Operator=&amp'

However in operator I only have simple text, and no null or "&amp" rows. 

What does that mean ?

Thanks  

[KHorseman · Accepted solution]

The & operator only works on text values. You'll need to use converter functions to get text values from your non-text columns.

= [TextColumn] & Date.ToText([DateColumn]) & Number.ToText([NumberColumn])

Or

= [TextColumn] & Text.From([DateColumn]) & Text.From([NumberColumn])

[KHorseman · Accepted solution]

The & operator only works on text values. You'll need to use converter functions to get text values from your non-text columns.

= [TextColumn] & Date.ToText([DateColumn]) & Number.ToText([NumberColumn])

Or

= [TextColumn] & Text.From([DateColumn]) & Text.From([NumberColumn])

[Joak]

Thanks it works perfectly !

[mbuick]

Hi,

I'm having this issue and the method below isn't working. I am trying to concatenate the following to create a unique ID;

Date in column (t1) as 10/31/2017 7:38:00 AM - Date/Time

Location ID in column (Location.id) as 2682 - Integer

Number.ToText for the location ID works but the formual returns as error when I add in Date.ToText.

Cheers.

[sowmya3192]

Hi,

I tried to use the function in a calcuated column in Direct Query mode. I got this error:

"Failed to resolve name 'Number.ToText'. It is not a valid table, variable, or function name."

Please advise.

[KHorseman]

There are a lot of query functions that are restricted in Direct Query mode. I believe that's one of them. You can't do much transformation of data in Direct Query mode, only in Import.

[sowmya3192]

Thank you for the reply. Is there a workaround?

I wanna create a column "Bay & Level" using PickAisle, PickModule, PickBay & PickLevel.

Eg: 42B-003-3. If the value is NULL, I want it to be blank. 

I used the Number.ToText function to convert the PickAisle column to text values and combine it with PickModule. But, that didn't work in DirectQuery mode. Any help would be appreciated.

[KHorseman]

If you're using direct query, you should be working with a database that has all the data you need already pretty much modeled the way you need it. If there is a column like you're describing that doesn't exist I would recommend working with your DBA to adjust the source to suit your needs. If your database doesn't suit your reporting needs and can't be changed, you will probably need to use Import instead of Direct Query.

[sphillips22]

@KHorseman Good solution!