Conditional Custom column or Calculated column-Need to create single column based on 2 datatype

[Anonymous]

Hi,

Need Custom column or calculated column, where if Unit = #, then Actual KPI needs to come without any calculation and if Unit = % then kpi needs to come as % format.

Note: both needs to come same column.(Need Exactly as Result column)

Unit	KPI	Result
#	83.7	83.7
#	92.1	92.1
#	67	67
%	96.3	96.30%
%	94	94%
%	88.4	88.40%
%	92	92%

[Nathaniel_C · Accepted solution]

@Anonymous ,
You will not be able to have two different formats in the same column.  If you only need this for display purposes, you can make this column text and it will do this. You could then convert the value from text by using dax as needed.  

In PQ, here is the column:

if [Unit] = "%" then Text.Combine({Text.From([KPI]),"%"}) else Text.From([KPI])

Here is my table code in PQ.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WUlbSUbIw1jOHUbE6EDFLIz1DGAUTMzOHECC+KkjSTM8YShmoIkRNwARCwMJCzwRKISszAhNAgVgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Unit = _t, KPI = _t, Result = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Unit", type text}, {"KPI", type number}, {"Result", type number}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "myResult", each if [Unit] = "%" then Text.Combine({Text.From([KPI]),"%"}) else Text.From([KPI])),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"myResult", type text}})
in
    #"Changed Type1"

Paste this into a blank query to get the table.

Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
Nathaniel