Forum Discussion

Petri's avatar
Petri
Frequent Visitor
3 years ago
Solved

Getting a unique value of the latest date

I would like power query to return just one row:  the latest date. I have always used a workaround (select the latest date, then group by date column) but I wonder if one could do this more effectively by List.Distinct.

 

Attempts trying to nest it around List.Max did not work:

#"Filtered Rows" = Table.SelectRows(ATable, List.Distinct(let latest = List.Max(ATable[DateCharac]) in each [DateCharac] = latest))
#"Filtered Rows" = Table.SelectRows(ATable, let latest = List.Distinct(List.Max(ATable[DateCharac])) in each [DateCharac] = latest)

 

This one returned everything:

#"Filtered Rows" = Table.SelectRows(ATable, let latest = List.Max(ATable[DateCharac]) in each [DateCharac] = latest),
#"Distinct" = List.Distinct(#"Filtered Rows"[DateCharac])

 

Please let me know what goes wrong above - what would be the best way to get the latest date in one row?

Need Help
  • v-jingzhang's avatar
    v-jingzhang
    3 years ago

    Hi Petri 

     

    Do you want to put custom1, custom2, etc. in different columns of a single row? Something like below?

    custom1 = #table({"Latest Date 1", "Latest Date 2"}, {{List.Max(ATable[Date]), List.Max(BTable[Date])}})
in
custom1

     

    If it's not what you want, can you provide the expected table you want to get with some sample data? 

     

    Best Regards,
    Community Support Team _ Jing
    If this post helps, please Accept it as Solution to help other members find it.

4 Replies

  • v-jingzhang's avatar
    v-jingzhang
    Icon for Community Support rankCommunity Support
    3 years ago

    Hi Petri 

     

    Is this what you want?

    = #table({"Latest Date"},{{List.Max(ATable[Date])}})

     

    Best Regards,
    Community Support Team _ Jing
    If this post helps, please Accept it as Solution to help other members find it.

    • Petri's avatar
      Petri
      Frequent Visitor
      3 years ago

      Thank you! This indeed makes it easier and avoids navigating to the table and field first. I even managed to get the values from several tables:

      custom1 = #table({"Latest Date"},{{List.Max(ATable[Date])}}),
custom2 = #table({"Latest Date"},{{List.Max(BTable[Date])}})
in
custom1

       

      ...althoug I still struggle how to get the custom1, custom2, etc. in different columns.

       

      • v-jingzhang's avatar
        v-jingzhang
        Icon for Community Support rankCommunity Support
        3 years ago

        Hi Petri 

         

        Do you want to put custom1, custom2, etc. in different columns of a single row? Something like below?

        custom1 = #table({"Latest Date 1", "Latest Date 2"}, {{List.Max(ATable[Date]), List.Max(BTable[Date])}})
in
custom1

         

        If it's not what you want, can you provide the expected table you want to get with some sample data? 

         

        Best Regards,
        Community Support Team _ Jing
        If this post helps, please Accept it as Solution to help other members find it.