Get Column Name Formula

[Anonymous]

Good afternoon Folks

I have 4 columns with different values and in the 5th column, I have a formula that pics the lowest value from the 4 columns.

I am looking for a formula/ideas that will allow me populate a new column with the column name where the lowest value (i.e. the value in column 5) comes from.

Any suggestions greatly appreciated.

Regards

Herbert

[Jimmy801 · Accepted solution]

Hello @Anonymous 

use this formula

Table.Min(Record.ToTable(_), "Value")[Name]

Here the full code

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("PY7BEQAhCAN74e1Hg6i1MPbfxhEY7xOZJKy4CyBNbIXoCekqt7msztlCJiM76RqNjBQ10cVmjQZI0CLEE+6kDEa7upwna7DCJJc1I3egtn7uOyoX8jJ9H+XCCu79AA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [A = _t, B = _t, C = _t, D = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"A", Int64.Type}, {"B", Int64.Type}, {"C", Int64.Type}, {"D", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Table.Min(Record.ToTable(_), "Value")[Name])
in
    #"Added Custom"

THis is the result

Copy paste this code to the advanced editor in a new blank query to see how the solution works.

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

[Anonymous]

Hi Jimmy,

What if I need to get the name of the max-valued column from only selected columns (e.g., only column "A" and "C")? Would that be posibble?

Appreciate your help in advance!

[Anonymous]

This is great Jimmy801

Thank you so much

Much appreciated

Herbz

[CNENFRNL]

@Anonymous , you might want to try

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("PY7BEQAhCAN74e1Hg6i1MPbfxhEY7xOZJKy4CyBNbIXoCekqt7msztlCJiM76RqNjBQ10cVmjQZI0CLEE+6kDEa7upwna7DCJJc1I3egtn7uOyoX8jJ9H+XCCu79AA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [A = _t, B = _t, C = _t, D = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"A", Int64.Type}, {"B", Int64.Type}, {"C", Int64.Type}, {"D", Int64.Type}}),
    #"Added Custom" = let cols = Table.ColumnNames(#"Changed Type") in Table.AddColumn(#"Changed Type", "Min Column", each let values = Record.FieldValues(_)
 in cols{List.PositionOf(values, List.Min(values))})
in
    #"Added Custom"

[edhans]

You need to provide some sample data @Anonymous - but I'm betting the right way to do this is to normalize the data with an unpivot operation. If the values are of a similar nature, they should be in 1 column with the attribute in another, not in 4 columns. Then this is a snap.

each let
   varMinValue = [Min Value]
in
Table.SelectRows(
   [All Rows], 
   each [Value] = varMinValue
   )[Attribute]{0}

It takes this:

and returns this:

Which is now a normalized table, and will make it easier to work with in DAX. The DAX model doesn't work with coluimns like Excel does. Neither does Power Query for that matter.

What is normalizing data?
See the Normalizing an example table section of this paper

But some sample data and expected output would be helpful.

How to get good help fast. Help us help you.

How To Ask A Technical Question If you Really Want An Answer

How to Get Your Question Answered Quickly - Give us a good and concise explanation
How to provide sample data in the Power BI Forum - Provide data in a table format per the link, or share an Excel/CSV file via OneDrive, Dropbox, etc.. Provide expected output using a screenshot of Excel or other image. Do not provide a screenshot of the source data. I cannot paste an image into Power BI tables.

Full M code for my query:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSixOUdJRMgRiE1MgYQHElkqxOtFK5alFQLYREJuBxXSUDMDiQA1p6UCeMVQGpMNEKTYWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Data = _t, A = _t, B = _t, C = _t, D = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"A", Int64.Type}, {"B", Int64.Type}, {"C", Int64.Type}, {"D", Int64.Type}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Data"}, "Attribute", "Value"),
    #"Grouped Rows" = Table.Group(#"Unpivoted Other Columns", {"Data"}, {{"Min Value", each List.Min([Value]), type number}, {"All Rows", each _, type table [Data=nullable text, Attribute=text, Value=number]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Min Value Attribute", each let
   varMinValue = [Min Value]
in
Table.SelectRows([All Rows], each [Value] = varMinValue)[Attribute]{0}, Text.Type),
    #"Expanded All Rows" = Table.ExpandTableColumn(#"Added Custom", "All Rows", {"Attribute", "Value"}, {"Attribute", "Value"})
in
    #"Expanded All Rows"

How to use M code provided in a blank query:
1) In Power Query, select New Source, then Blank Query
2) On the Home ribbon, select "Advanced Editor" button
3) Remove everything you see, then paste the M code I've given you in that box.
4) Press Done
5) See this article if you need help using this M code in your model.