cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper I
Helper I

most recent value date. how to do to use the type column ?

Hi ;

I would want to add a most recent value of date in the new column . I filled in the formula of custom column :

Date.From(List.Max([#"End time - date"])). 

I have a error message : capture error most date query power bi.PNG

 

 

capture error most date query power bi 2.png

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

first of all, the end date is of type DATE. I thought of copying the same value and the same type for a new column! ...

 

With this error, I must then add a type, in addition, in the formula to force a type like DATE for a new column.

How to do ?

 

Thank you in advance for your answer

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Microsoft
Microsoft

Re: most recent value date. how to do to use the type column ?

Oh, what you want to use is a group operation:

Table.Group(PreviousStep, {"Number"}, {"Row", each Table.Max(_, "Date"), type record})

 

Then you would need to expand the Row column to get all the column values that are also in the row with the max date per number.

View solution in original post

3 REPLIES 3
Highlighted
Microsoft
Microsoft

Re: most recent value date. how to do to use the type column ?

You are refering to the max date for a single row, which won't work. What you want to do is the max date of all values in that column regardless of the current row.

 

Change:

Date.From(List.Max([#"End time - date"]))

to

Date.From(List.Max(#"Colonnes supprimées4"[#"End time - date"]))

Highlighted
Helper I
Helper I

Re: most recent value date. how to do to use the type column ?

Hi,

 

Thank you for your answer.

 

but I don't understand  that you want say...

My goal is for the extraction of record if the end date is max (latest as most recent) for each number.

for instance :

Number     Date

1                 2/01/2020

1                 3/01/2020

2                 2/01/2019

2                 1/01/2020

2                 2/01/2020

3                  null

4                 1/01/2020

4                 4/01/2020

 

Results :

 

Number     Date

1                 3/01/2020

2                 2/01/2020

3                  null

4                 4/01/2020

 

I hope you understand that I want say this ....

Highlighted
Microsoft
Microsoft

Re: most recent value date. how to do to use the type column ?

Oh, what you want to use is a group operation:

Table.Group(PreviousStep, {"Number"}, {"Row", each Table.Max(_, "Date"), type record})

 

Then you would need to expand the Row column to get all the column values that are also in the row with the max date per number.

View solution in original post

Helpful resources

Announcements
Super Users of the Quarter - Q2 2020

Super Users of the Quarter - Q2 2020

Who are our Super User Superstars? Who made it to the top of the leaderboards? Get the answers!

Community Summit Australia – Join Online!

Community Summit Australia – Join Online!

Be a part of the leading Microsoft Business Applications digital event, curated for the APAC community.