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
NashvegasRob
Regular Visitor

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

1 ACCEPTED SOLUTION
Vijay_A_Verma
Super User
Super User

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"

View solution in original post

7 REPLIES 7
NashvegasRob
Regular Visitor

= 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

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"

 

Thanks for your help Vijay.  It does work.

 

Rob

Vijay_A_Verma
Super User
Super User

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"

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

Please paste your query.

= 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

Helpful resources

Announcements
July PBI25 Carousel

Power BI Monthly Update - July 2025

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

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 community update carousel

Fabric Community Update - June 2025

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