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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors