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

[NashvegasRob]

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 the column values when there is a NULL in the other?

Kinda new here and have read all over the place but can't find this very specific set of code.

Thanks in advance,

Rob

[NashvegasRob]

= List.MIN({[PO_Source_Calculated_Spend],[TotalCalc10th]})

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_Source_Calculated_Spend = _t, TotalCalc10th = _t]),
#"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"

TABLE NAME IS "DemoClient"

Thanks, Rob

[Vijay_A_Verma]

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]

Thanks for your help Vijay.  It does work.

Rob

[Vijay_A_Verma · Accepted solution]

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"

[NashvegasRob]

Hi Vijay,

I appreciate the response.  I was not able to get it to work unfortunately.  It's probably user error on my end.   Getting Syntax Error:  Token Literal Expected  

Also, should Min be capitalized?   Min ==> MIN   

Thanks!  Rob

[Vijay_A_Verma]

Please paste your query.

[NashvegasRob]

= List.MIN({[PO_Source_Calculated_Spend],[TotalCalc10th]})

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_Source_Calculated_Spend = _t, TotalCalc10th = _t]),
#"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"

TABLE NAME IS "DemoClient"

Thanks, Rob