Forum Discussion

Nielf's avatar
Nielf
Icon for Helper I rankHelper I
3 years ago
Solved

Concatenate three types columns - DAX or Power Query

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 ...
Need Help
MAwwad's avatar
MAwwad
Icon for Solution Sage rankSolution Sage
3 years ago

 

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.