Transform a column with an 'If statement'

[Anonymous]

Hi,

I would like to modify a column without creating a new one. Basically I want to apply a rule:

If value >= 5, then 5.

Is there an easy way to do it without creating a new calculated field or measure?

Thanks

Jon

[Anonymous]

You can do that in power query. While importing the data, select "Add conditional column" option.

The result will be as follows...

Then remove the field "Value" (Right-click, Remove) before applying the changes. Internally the power query generated automaticall will look like this.

let
    Source = Excel.Workbook(File.Contents("C:\Users\S\Documents\Book1.xlsx"), null, true),
    Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Item", type text}, {"Value", Int64.Type}}),
    #"Added Conditional Column" = Table.AddColumn(#"Changed Type", "Custom", each if [Value] >= 5 then 5 else [Value]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Conditional Column",{"Value"})
in
    #"Removed Columns"

[amitchandak]

@Anonymous , May be power query you can edit the source script and try. Best is you create a new column only

if([value]>5,5,blank())

[harshnathani · Accepted solution]

Hi @Anonymous ,

You will need to create this as a new column or a new measure.

Else filter this condition of value >= 5 at the source itself.

Regards,

Harsh Nathani

Appreciate with a Kudos!! (Click the Thumbs Up Button)

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