Concatenate three types columns - DAX or Power Query

[Nielf]

A have a use case, where I need to concatenate three columns from the same table. I also need to format one of the columns in the process.

I am wondering whether the best option is to use DAX or Power Query. 

I have three columns like this:

Name (text)	Win % (whole number)	Sales price (fixed decimal number)
Blah blah 1	50	514594105,00

Which I would like to end up like this, where the Sales price gets formatted as millions in the process. 

Blah blah 1 (50% / M514)

I have tried various approached but haven't really found a solution. 

I hope you can help. 

[Nielf]

Thanks. Both of the solutions work as expected.

[MAwwad]

Power Query offers more capabilities to transform data in various way, like to change the format of the Sales Price column you can use the Power Query's Number.ToText() function.

Here's an example of how to use Power Query to format the Sales Price column to show values in millions:

Copy code

let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("...", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Name = _t, Win% = _t, Sales Price = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Win%", Int64.Type}, {"Sales Price", type number}}), #"Formatted Sales Price" = Table.TransformColumns(#"Changed Type", {{"Sales Price", each Number.ToText(_, "0.0,,M")}}) in #"Formatted Sales Price"

The above code will format the Sales Price column as millions and it uses Table.TransformColumns() to change the column type to text using Number.ToText() function.

Then, you can use DAX to create a calculated column by concatenating the columns with the correct separators and formatting the values like the next example

Copy code

Concatenated Column = CONCATENATE([Name]," (",[Win%],"% / ", [Sales Price], ")")

If the use case is simple and you just need to concatenate the column and format one of them, the above code should be more than enough, but if you have more complex use cases, Power Query will be better suited to handle them.

Keep in mind that the above code is just an example, you'll need to adjust the code to match the column and table names of your dataset.

[Idrissshatila · Accepted solution]

Hello @Nielf ,

try this

Newcolumn = Name & " " & "(" & win% & ")" & " " & Price

If I answered your question, please mark my post as solution, Appreciate your Kudos 👍

Follow me on Linkedin