Forum Discussion

NashvegasRob's avatar
NashvegasRob
Regular Visitor
4 years ago
Solved

Using the Min function to chose the lowest number between two columns, if null then the other number

Hi  Using a column add (not a measure)  is there a good way to create a new column that brings in the lowest value of two other columns (PO_Price) and (Benchamark_Price) and still brings back one of...
Need Help
  • Vijay_A_Verma's avatar
    Vijay_A_Verma
    4 years ago

    Use this in a custom column

    = List.Min({[PO_Price],[Benchmark_Price]})

    See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjFV0lEyMlaK1YlWMjQCsi0swWxjEyAbzALSQEUgFlAKIh8LAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [PO_Price = _t, Benchmark_Price = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"PO_Price", Int64.Type}, {"Benchmark_Price", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each List.Min({[PO_Price],[Benchmark_Price]}), type number)
in
    #"Added Custom"
Vijay_A_Verma's avatar
Vijay_A_Verma
Most Valuable Professional
4 years ago

You are a having line before let statement and that is causing the problem. The code starts from let.

Open your file in Power Query. It will generate a source line

Let's say this source line looks like

Source = Excel.CurrentWorkbook(){[Name="Table5"]}[Content]

Now copy and paste this line in your code replacing existing Source line. Hence, your code will become following

let
Source = Excel.CurrentWorkbook(){[Name="Table5"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"PO_Source_Calculated_Spend", Int64.Type}, {"TotalCalc10th", Int64.Type}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each List.Min({[PO_Source_Calculated_Spend],[TotalCalc10th]}), type number)
in
#"Added Custom"

 

NashvegasRob's avatar
NashvegasRob
Regular Visitor
4 years ago

Thanks for your help Vijay.  It does work.

 

Rob