How to put "" around every value in table

[TriLamda]

Hello All,

Been looking at the concatenatex function but was curious if this was the best way/practice of doing this.  I have a table with 10 columns and I need to put all the values into "" so if one column was FirstName - all would show up as "Bill" or "Betty".  

Reason i'm doing this is this will be shoved into a Flow to mail out this report regularly.  I guess I am curious if its best to do it in the Power BI model and create 10 new colmns or if I go Performance Optimizer and get the DAX Query for table - do it there?  I saw the SelectColumns option but i'm a little new to that query and how to update it - I have a DS0Core and DS0BodyLimited chunks of code in there.  

So I guess i'm looking for help from the comm on which is the best way to do it and how would it look?

Thanks!

[TriLamda]

This is awesome!! Thanks for the thorough writeup.  The concatenate example you showed was exactly what I was getting and wasn't sure why - this explained it.  Cheers!

[Anonymous]

Hi,@TriLamda .Thank you for your reply.
You are welcome.

[Anonymous · Accepted solution]

Hi,@TriLamda .I am glad to help you.

According to your description, you want to add double quotes to all the data in the table?
like this

Assuming the original data is like this:

FirstName	LastName	Email
Bill	Smith	bill.smith@example.com
Betty	Johnson	betty.johnson@example.com
John	Doe	john.doe@example.com
Jane	Brown	jane.brown@example.com

You want the end result to be like this:

FirstName	LastName	Email
"Bill"	"Smith"	"bill.smith@example.com"
"Betty"	"Johnson"	"betty.johnson@example.com"
"John"	"Doe"	"john.doe@example.com"
"Jane"	"Brown"	"jane.brown@example.com"

If so, you can create ten custom columns in Power query
Using M code.

The M code:

"""" & [FirstName] & """"

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ZcoxCoAwDIXhu3QuOYMUJ1dHcWg1oKVtRAvq7c3DSVwS/uQbBuPWlIw1fV7rojto0oFo+PJ5S0yTZDNalVzrraSTpRxSgHGh+PbPwylqhXUC0Sz8V77g73Y5oaMmBcRXjg8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [FirstName = _t, LastName = _t, Email = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"FirstName", type text}, {"LastName", type text}, {"Email", type text}}),
    #"Added Custom01" = Table.AddColumn(#"Changed Type", "FirstName01", each """" & [FirstName] & """"),
    #"Added Custom02" = Table.AddColumn(#"Added Custom01", "LastName01", each """" & [LastName] & """"),
    #"Added Custom03" = Table.AddColumn(#"Added Custom02", "Email01", each """" & [Email] & """")
in
    #"Added Custom03"

2. A similar effect can be achieved using Dax
Use & to concatenate strings

FirstName01 = """" &[FirstName] & """"

I noticed that you also mentioned the CONCATENATEX function.

The CONCATENATEX function does concatenate strings in DAX with the & operator, but it will concatenate all the data into a single string, so it may not be sufficient for your needs above.

CONCATENATEX function

CONCATENATEX function (DAX) - DAX | Microsoft Learn
You can see that the system splices all the data in the [Email] column instead of just adding quotes!

I recommend that you create the calculated columns directly: it's easier to concatenate the column names with the "&" symbol.

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Carson Jian,
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.