Forum Discussion

tfoss78's avatar
tfoss78
Regular Visitor
4 years ago
Solved

Returning latest among multiple dates by row?

Newer user, apologies in advance if I'm missing something basic here.  The table I'm using includes several dates per row corresponding to the receipt of various documents, I am trying to build a custom column that will return the latest of these dates per row.  It's my sense that MAX on the DAX side is entirely out, I've attempted to use the following script in Power Query to no avail ("a cyclic reference was enountered..."):

 

Table.AddColumn(#"Changed Type", "Custom", each Table.AddColumn(table, "Max", each List.Max({[receipt of document 1], [receipt of document 2], [receipt of document 3], [receipt of document 4], [receipt of document 5], [receipt of document 6], [receipt of document 7]})))

 

I would be eternally grateful to anyone who can set me on the right path!  Thanks very much for reading.

Need Help
  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    4 years ago

    Hi tfoss78 ,

    There two Table.AddColumn in you code. 

    I get the correct result by add a custom column.

     

    List.Max({[Column1],[Column2],[Column3]})

     

    Here's the whole code below, you can copy-paste in a blank query to see the step.

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMtI31zc0UtKBsxFMM6VYHbgaY4S4EZJyC2Q1SOKmCKalUmwsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type date}, {"Column2", type date}, {"Column3", type date}}),
    #"Added Custom1" = Table.AddColumn(#"Changed Type", "Max", each List.Max({[Column1],[Column2],[Column3]}))
in
    #"Added Custom1"

     

    I attach my sample below for reference.

     

    Best Regards,
    Community Support Team _ kalyj

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

4 Replies

  • johnt75's avatar
    johnt75
    Icon for Super User rankSuper User
    4 years ago

    You can use MAX, you just need to put the columns into a table variable, like

    Max of columns = MAXX( { 'Table'[Col1], 'Table'[Col2], 'Table'[Col3] }, [Value])
    • tfoss78's avatar
      tfoss78
      Regular Visitor
      4 years ago

      Hi John, thanks very much for this bit of insight.  However, I'm encountering a problem with the value part of the expression - I don't need any calculation per se, just that it return the latest among seven dates specified in the first part of the expression.  Is this possible using MAXX?  Sorry if I'm being dim here.  

      • johnt75's avatar
        johnt75
        Icon for Super User rankSuper User
        4 years ago

        The table constructor { } creates a table with a single column called [Value]. By adding all the dates you want to compare into a table like that you can run MAXX over it.

  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    Icon for Community Support rankCommunity Support
    4 years ago

    Hi tfoss78 ,

    There two Table.AddColumn in you code. 

    I get the correct result by add a custom column.

     

    List.Max({[Column1],[Column2],[Column3]})

     

    Here's the whole code below, you can copy-paste in a blank query to see the step.

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMtI31zc0UtKBsxFMM6VYHbgaY4S4EZJyC2Q1SOKmCKalUmwsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type date}, {"Column2", type date}, {"Column3", type date}}),
    #"Added Custom1" = Table.AddColumn(#"Changed Type", "Max", each List.Max({[Column1],[Column2],[Column3]}))
in
    #"Added Custom1"

     

    I attach my sample below for reference.

     

    Best Regards,
    Community Support Team _ kalyj

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.