Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Get Column Name Formula

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

  • 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

5 Replies

  • Jimmy801's avatar
    Jimmy801
    Community Champion

    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's avatar
      Anonymous
      Not applicable

      This is great Jimmy801

       

      Thank you so much

       

      Much appreciated

       

      Herbz

    • Anonymous's avatar
      Anonymous
      Not applicable

      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!

  • edhans's avatar
    edhans
    Community Champion

    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.

  • CNENFRNL's avatar
    CNENFRNL
    Community Champion

    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"