cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Impactful Individual
Impactful Individual

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...

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Microsoft
Microsoft

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

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.

1.PNG

2. Create a custom column using the formula.

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

2.PNG

3. You will get the expected result as follows.

3.PNG

Best Regards,
Angelia

View solution in original post

11 REPLIES 11
Highlighted
Super User IV
Super User IV

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

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






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





Highlighted
Community Champion
Community Champion

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

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.

Highlighted
Impactful Individual
Impactful Individual

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

So I added a column with this formula:

 

=List.Max([Date Column])

Highlighted
Community Champion
Community Champion

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

So when you look at the actual code in the Advanced Editor, does this come after an 'each' statement?  If so, you are passing a single value into that statement.

Highlighted
Community Champion
Community Champion

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

The column name must be preceded by the table name, which can be either the name of another query or the name of another step in the current query.

 

=List.Max(Table[Date Column])
Specializing in Power Query Formula Language (M)
Highlighted
Microsoft
Microsoft

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

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.

1.PNG

2. Create a custom column using the formula.

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

2.PNG

3. You will get the expected result as follows.

3.PNG

Best Regards,
Angelia

View solution in original post

Highlighted
New Member

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

Hi @v-huizhn-msft!

But how to get max date for each Customer number if it have few of them?

Thanks 

 

Kaspars

Highlighted
Continued Contributor
Continued Contributor

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

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])
Highlighted
New Member

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

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!

Helpful resources

Announcements
May 2020 Community Highlights

May 2020 Community Highlights

It’s time for another PBI Community recap!

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Galleries

Galleries

Looking for inspiration on how to present your data? Need instructional videos? Check out our Galleries!

Top Solution Authors