Power Query - Column that Concatenates a column and a text version of a date.

[jaihawk8]

I've got a PowerQuery that's currently set up and working great, with the exception of one thing. I need a column that Concatenates the Sales Rep's name and a text version of the date, so it can be picked up in another file. Currently, I have a column in the sheet where data is returned that has the following formula: =CONCATENATE(F2,"-",TEXT(T2,"mmddyyyy")), which gives me what I need.

The problem is that when I refresh the report for different payroll dates, if there are more rows than on the previous version, it doesn't carry the formula all of the way down. Currently I'm handling that as part of a macro, but I'd rather have it just come through from the PowerQuery itself.

Is that possible? I have tried to Add a column and use the formula =CONCATENATE([Sales Rep],"-",TEXT([Payroll Date],"mmddyyyy")). It says no syntax errors have been detected, but when I hit OK, I get the following error: Expression.Error: The name 'CONCATENATE' wasn't recognized. Make sure it's spelled correctly.

Is it possible to do this?

 

[ExcelToDAX]

Since Power Query loads the table to an Excel table, you could essentially just add "=CONCATENATE([Sales Rep],"-",TEXT([Payroll Date],"mmddyyyy"))" to a new column and every time you refresh the table, that column will stay there. If you want to use Power Query you can do this:

[Sales Rep] & "-" & Date.ToText([Payroll Date], "MMddyyyy")

 

[jaihawk8]

Since Power Query loads the table to an Excel table, you could essentially just add "=CONCATENATE([Sales Rep],"-",TEXT([Payroll Date],"mmddyyyy"))" to a new column and every time you refresh the table, that column will stay there. If you want to use Power Query you can do this:

[Sales Rep] & "-" & Date.ToText([Payroll Date], "MMddyyyy")

Thanks! I will give that a try!!! I assume that is the coding that I would use in the Custom Column?