Forum Discussion
Concatenate dates
Hello everyone.
When I try to concatenate 3 columns, one of them has dates, I get in the results column a number that equales a date. Very annoying.
My column formula
=CONCAT([@[Name]],"-",[@[Record _number]],"-",[@[Expiration_Date]])
Example Result:
John Smith-033111-43706
What I really want:
John Smith-033111-08/29/2019
Any help on this? Thanks in advance
In DAX
you may use
=CONCATENATE(Table1[Name],CONCATENATE("-",CONCATENATE(Table1[Record Number],CONCATENATE("-",Table1[Expiration Date]))))but much better is
=Table1[Name] & "-" & Table1[Record Number] & "-" & Table1[Expiration Date]
9 Replies
In DAX
you may use
=CONCATENATE(Table1[Name],CONCATENATE("-",CONCATENATE(Table1[Record Number],CONCATENATE("-",Table1[Expiration Date]))))but much better is
=Table1[Name] & "-" & Table1[Record Number] & "-" & Table1[Expiration Date]
Alternatively, try this:
=CONCAT([@Name],"-",[@Record_number],"-",TEXT([@Expiration_Date],"dd/mm/yyyy"))=CONCAT([@Name],"-",[@Record_number],"-",TEXT([@Expiration_Date],"dd/mm/yyyy"))
=CONCAT([@[Name]],"-",[@Record_number],"-",TEXT([@[Expiration_Date]],"dd/mm/yyyy"))
Made a slight modification. For some reason I MUST keep the first and last columns with [@[]] or else i get a mess, IDK why
What's "IDK"?
I replicated your example as shown in the picture below. Don't understand why you must have the extra
"[@[]]". But if it works for you, that's okay, I guess.
The best way to achieve your desired outcome is as follows
1. Click anywhere in your data and press CTRL + T. Ensure my data has headers is checked.
2. Click OK
3. In the Data tab, click on From Table/Range
4. In the Power Query editor, select all the three columns
5. Click on Merge Columns in the Add Column tab
6. In the Merger Column dialogue box, select Custom as Separator
7. In the box below, type in - (as your separator)
8. Click OK
In the caption below, you have the newly added column
9. Right-click on the first 3 columns and select Remove columns
10. Finally, select Close and Load in the Home tab to load the result back to Excel
That is the final answer.
TEXTJOIN method
=TEXTJOIN("-",TRUE,D5,E5,TEXT(F5,"mm/dd/yyyy"))
See attached
If this solve your question, kindly do accept as the Best Response.
Regards
