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
Solved! Go to 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"
= 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
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
Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!