Concatenate OR Join Columns values

[bajimmy1983]

Hi community!

Could you please help? 

I am trying to add a custom column called [IndexKey] in Power Query to deliver the same outcome I have in a normal Excel table. 

In a normal excel I have the following columns and values:

[PO number] (type as general) = 123456

[BillingDate] (type as short date) = 01/01/16

[CampaignName] (type as general) = Emirates is the best

When I use EXCEL CONCATENATE function I got "12345642370Emirates is the best" (this is exactly what I need as a kind of an index key)

Now jumping into Power Query, when I try this: [PO number] & [BillingDate] & [CampaignName] I got: "12345601/01/16Emirates is the best" (this is what I do NOT want).

I can imagine why this is happening (I think because of the special character &), but I want discover a way in which I could get the same outcome as Excel tables, but adding a custom column through user interface in Power Query.

Thanks again for your support.

[Greg_Deckler · Accepted solution]

Either convert your Date column to a Whole Number or create another column that just gets your Date column and switch it to a whole number, use that column instead of the original Date column in your concatenation. 

Or, do it all in one column like this:

Column = CONCATENATE(CONCATENATE([PO Number],[Date]*1.),[Campaign])

[BhaveshPatel · Accepted solution]

@bajimmy1983

If you have your data coming from excel sheets, You can convert the date format to number. Import this data to powerquery and change the data type of date column to Text. 

In order to join them together in one column, data types of all the columns must be same. The "& " operator is similar to Excel in powerquery custom column. 

If your data is not coming from excel, you can split the column in Query Editor and change the data type of all columns to text and then can be joined together.

[bajimmy1983]

Thank you very much BhaveshPatel. Also a Good way. 🙂

Have a nice rest,

[Greg_Deckler · Accepted solution]

Either convert your Date column to a Whole Number or create another column that just gets your Date column and switch it to a whole number, use that column instead of the original Date column in your concatenation. 

Or, do it all in one column like this:

Column = CONCATENATE(CONCATENATE([PO Number],[Date]*1.),[Campaign])

[bajimmy1983]

Hi @Greg_Deckler,

Thanks. Good solution. Just a final question? Why did you use *1.? Is it was to convert Date into number format?

Thanks again.

[Greg_Deckler]

Yes, it is one of those shorthand tricks you pick-up over time. Comes in handy.