Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.
Hello all,
I am trying to add a cusom column to calculate the Max and Min for 3 columns(More in the future). Issue is that the data is alphanumeric for Cancelled values. So when I try it, I get the maximum value in letters. Is there anyway to get the highest numeric value as the Max, in this situation. Here is the sample table I am trying to work on.
As you can see, I get the MAX as "Cancelled", but I am trying to get "23" as the result.
I am using this basic DAX -
List.Max ({[D1],[D2],[D3]})
I am rather new to this, so I might be asking something very simple. Any assistance is much appreciated. Thank you,
Binara
Solved! Go to Solution.
Hi @Binara ,
Try copying this query in your advanced editor:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjRX0lHKK83JgVGxOtFKRsZAnnNiXnJqTk5qCpBtaKkUGwsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [D1 = _t, D2 = _t, D3 = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"D1", type text}, {"D2", type text}, {"D3", type text}}),
Custom1 = Table.AddColumn(#"Changed Type", "Lists", each Text.Split(Text.Combine({[D1],[D2],[D3]},"|"),"|")),
Custom2 = Table.AddColumn(#"Custom1", "MaxVal", each List.Max(List.Transform([Lists], each try Number.FromText(_) otherwise null))),
Custom3 = Table.AddColumn(#"Custom2", "MinVal", each List.Min(List.Transform([Lists], each try Number.FromText(_) otherwise null))),
#"Removed Columns" = Table.RemoveColumns(Custom3,{"Lists"})
in
#"Removed Columns"
Sample output:
Hi @Binara ,
Try copying this query in your advanced editor:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjRX0lHKK83JgVGxOtFKRsZAnnNiXnJqTk5qCpBtaKkUGwsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [D1 = _t, D2 = _t, D3 = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"D1", type text}, {"D2", type text}, {"D3", type text}}),
Custom1 = Table.AddColumn(#"Changed Type", "Lists", each Text.Split(Text.Combine({[D1],[D2],[D3]},"|"),"|")),
Custom2 = Table.AddColumn(#"Custom1", "MaxVal", each List.Max(List.Transform([Lists], each try Number.FromText(_) otherwise null))),
Custom3 = Table.AddColumn(#"Custom2", "MinVal", each List.Min(List.Transform([Lists], each try Number.FromText(_) otherwise null))),
#"Removed Columns" = Table.RemoveColumns(Custom3,{"Lists"})
in
#"Removed Columns"
Sample output:
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
143 | |
85 | |
66 | |
51 | |
45 |
User | Count |
---|---|
216 | |
89 | |
82 | |
66 | |
57 |