Merge Columns IN Power Query with spacers

[SimonHughes]

Hello, getting to grips with PQ and have just merged columns and got the top row (Get). What I want though is to insert text as shown in the second row (Want). I also tried to add a custom column and use the concatenate formula to insert text and spaces but I just got an error message. Can anyone help here? Many thanks.

Get	-1;14;Back of house shower
Want	Level -1; Ref - 14; Location - Back of house shower

 

[HongRu]

To avoid error messages, maybe you should first transform the column types to "text" in order to merge columns.

 

[Alex Blakenburg]

Assuming you have -1, -14, Back of house shower as separate columns to start with, can't you simple use Transform > Format > Add Prefix to each of the 3 columns "BEFORE" you merge them ?

 

[alex78]

Hi SimonHughes,
try after updating Source and Column Names.

= Table.TransformColumns(Source, {"Column2", each Text.Combine(List.Transform(List.Zip({{"Level ","Ref - ", "Location - "}} & {Text.Split(_,";")}), Text.Combine), "; ")})

Regards,

 

[SimonHughes]

To avoid error messages, maybe you should first transform the column types to "text" in order to merge columns.

Hi Hongu - yes, I did transform to text.

 

[SimonHughes]

Assuming you have -1, -14, Back of house shower as separate columns to start with, can't you simple use Transform > Format > Add Prefix to each of the 3 columns "BEFORE" you merge them ?

View attachment 115559

View attachment 115561

Hi Alex, yes, that is just what I wanted, works perfectly, many thanks

 

[SimonHughes]

Hi SimonHughes,
try after updating Source and Column Names.

= Table.TransformColumns(Source, {"Column2", each Text.Combine(List.Transform(List.Zip({{"Level ","Ref - ", "Location - "}} & {Text.Split(_,";")}), Text.Combine), "; ")})

Regards,

Hi Alex, I have not tried your suggestion as I found the solution above which seems a very easy and tidy way of doing it but many thanks for your input (I will look at what you suggest as it may prove useful in other scenarios).

 

[smozgur]

Hi Alex, yes, that is just what I wanted, works perfectly, many thanks

The marked solution has been changed accordingly. In your future questions, please mark the post as the solution that actually answered your question, instead of your feedback message as it will help future readers. No further action is required for this thread.

 

[Alex Blakenburg]

@SimonHughes - Thanks for providing feedback to everyone who contributed.

 

[SimonHughes]

The marked solution has been changed accordingly. In your future questions, please mark the post as the solution that actually answered your question, instead of your feedback message as it will help future readers. No further action is required for this thread.

I really thought I had as I appreciate that it is important to do so. Apologies.