Forum Discussion

ovetteabejuela's avatar
ovetteabejuela
Impactful Individual
8 years ago
Solved

Power Query: List.Max doesn't work on Date

Hi,

 

Need some help. Supposedly List.Max should work on Dates right? Like List.Max([Date Column]).

 

I have a column of Date type and I tried to Add Column with the formula List.Max([Date Column]) but it's giving me this error:

 

Expression.Error: We cannot convert the value #date(2017, 10, 31) to type List.
Details:
Value=10/31/2017
Type=Type

 

So I might be missing something here...

  • v-huizhn-msft's avatar
    v-huizhn-msft
    8 years ago

    Hi ovetteabejuela,

    As I tested, you need to create the date column as a list, then use it in List.Max(). I have the following sample table.



    2. Create a custom column using the formula.

    =List.Max(#"Changed Type"[Date])



    3. You will get the expected result as follows.



    Best Regards,
    Angelia

11 Replies

  • Old thread, but this is/was available on the Ribbon also: Transform tab > Date & Time Column section > Date > Latest 

     

    It generates the same M as in this thread:

    = List.Max(Source[Date])
    • KasparsT's avatar
      KasparsT
      Regular Visitor

      Thanks for answer! Probably my question was not fully described... What I want to get is MAX DATE for particular customer number and have it sorted from list.

      Example:

      customerdate
      118.06.2019
      119.06.2019
      120.06.2019
      218.06.2019
      219.06.2019
      320.06.2019

       

      Thanks!

  • This has been answered but the steps are not fully clear, for the New Power BI User I will break down the solution:

     

    There currently is NO data type of "List", just make sure you have the column as Data or Number.

    Here is where the confusing part comes in as the actual "List.Max()" function isnt complete in syntax you either have to know exactly how to code it or you need to make an adjustment in Advanced Editor Section to manualy type in the previous Query Step Name as "Table" reference.

     

    I create a new Column I type the following in the column formual page "=List.Max({INSERT COLUMN NAME})" Add Column 

    THEN I go into Advanced Editor and Copy the Query Name from the prior step in the front of my INSERTED COLUMN NAME.

     

    BEFORE:   

    #"Changed column type" = Table.TransformColumnTypes(Navigation, {{"ci_item_time", type date}}),  #"Added custom" = Table.AddColumn(#"Changed column type", "Custom", each List.Max([ci_item_time]))

     

    AFTER:   

    #"Changed column type" = Table.TransformColumnTypes(Navigation, {{"ci_item_time", type date}}),

    #"Added custom" = Table.AddColumn(#"Changed column type", "Custom", each List.Max(#"Changed column type"[ci_item_time]))

     

    NOW if you knew what your prior Query Step name was you could have manually coded this into the New Column Formula statement like List.Max(#"Changed column type"[ci_item_time]) I hope this clears up any confusion on this Topic.

    E I L O O P

  • your column type is List? That function is to find max in a list or use groupby function to get maximum date.

  • Anonymous's avatar
    Anonymous
    Not applicable

    Whats the context of the power query line of code?  Are you passing a column or list type to the function?  The error message reads like you have passed a since value of type Date.

    • ovetteabejuela's avatar
      ovetteabejuela
      Impactful Individual

      So I added a column with this formula:

       

      =List.Max([Date Column])

      • v-huizhn-msft's avatar
        v-huizhn-msft
        Microsoft Employee

        Hi ovetteabejuela,

        As I tested, you need to create the date column as a list, then use it in List.Max(). I have the following sample table.



        2. Create a custom column using the formula.

        =List.Max(#"Changed Type"[Date])



        3. You will get the expected result as follows.



        Best Regards,
        Angelia