Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
ovetteabejuela
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

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
EILOOP
Advocate I
Advocate I

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

jeffshieldsdev
Solution Sage
Solution Sage

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])

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!

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.

So I added a column with this formula:

 

=List.Max([Date Column])

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

How do i do this with an Column of type Int? There is no such thing as a list item in the dropdown i can transform this too.

Hi @v-huizhn-msft!

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

Thanks 

 

Kaspars

Anonymous
Not applicable

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.

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)
parry2k
Super User
Super User

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



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


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.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.