Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Anonymous
Not applicable

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

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)

UnitKPIResult
#83.783.7
#92.192.1
#6767
%96.396.30%
%9494%
%88.488.40%
%92

92%

1 ACCEPTED SOLUTION
Nathaniel_C
Community Champion
Community Champion

@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.  

Nathaniel_C_0-1674409418280.png

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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

1 REPLY 1
Nathaniel_C
Community Champion
Community Champion

@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.  

Nathaniel_C_0-1674409418280.png

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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.