Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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
Check out the July 2025 Power BI update to learn about new features.
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
User | Count |
---|---|
10 | |
8 | |
7 | |
6 | |
6 |