Reply
Frequent Visitor
Posts: 14
Registered: ‎07-18-2017
Accepted Solution

Concatenate date, number and letters in power query

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 Smiley Happy 


Accepted Solutions
Highlighted
Super User
Posts: 1,183
Registered: ‎12-29-2015

Re: Concatenate date, number and letters in power query

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])

View solution in original post


All Replies
Highlighted
Super User
Posts: 1,183
Registered: ‎12-29-2015

Re: Concatenate date, number and letters in power query

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])

Frequent Visitor
Posts: 14
Registered: ‎07-18-2017

Re: Concatenate date, number and letters in power query

Thanks it works perfectly !

Frequent Visitor
Posts: 7
Registered: ‎11-28-2017

Re: Concatenate date, number and letters in power query

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.

Frequent Visitor
Posts: 7
Registered: ‎04-04-2018

Re: Concatenate date, number and letters in power query

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.

Super User
Posts: 1,183
Registered: ‎12-29-2015

Re: Concatenate date, number and letters in power query

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.

Frequent Visitor
Posts: 7
Registered: ‎04-04-2018

Re: Concatenate date, number and letters in power query

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.

 

BayLevel.PNG

 

Super User
Posts: 1,183
Registered: ‎12-29-2015

Re: Concatenate date, number and letters in power query

[ Edited ]

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.

Frequent Visitor
Posts: 9
Registered: ‎04-03-2018

Re: Concatenate date, number and letters in power query

[ Edited ]

@KHorseman Good solution! Smiley Happy