cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

Previous Latest Date

Spoiler
Hi,

I have tried so many solutions. But couldn't get what I am looking for. In the below sample table, I need the Previous Latest Date column as a result as a part of the power query table itself.

 

Any help is much appreciated.

 

CIF                       Date                    Previous Latest Date

0123456789        20/07/2020         03/05/2020

0123456789        03/05/2020         02/02/2020

0123456789        02/02/2020         01/01/2020

0123456789        01/01/2020         01/01/2020

 

Regards, Ramnath

3 ACCEPTED SOLUTIONS

Accepted Solutions
Highlighted
Super User I
Super User I

Hi. This is not the best place to do it if you have a database as source because it has to query itself. Let's see. Add a custom column and insert the statement after "LastD", the final code should look like this:

 

= Table.AddColumn(#"Last Step", "LastD", 
    (Earlier) => 
        Table.Max(
             Table.SelectRows(
                Table.SelectColumns(#"Changed Type", {"Date", "CIF"})
            , each Earlier[Date] > [Date] and [CIF] = Earlier[CIF] 
        ) 
        , "Date", Earlier 
    )[Date] 
)

 

 Hope this helps,



If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Happy to help!

LaDataWeb Blog


View solution in original post

Highlighted

@Ramnath
You need to follow the Complex Table example in the link I posted.

Please try pasting this code into a Blank Query in Power Query in the Advanced Editor (replace everything that's in there with this code)

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjA0MjYxNTO3sDy0QElHyUjfXN/IwMhAKVYHQ85Y3xSnnJG+EU45A0N9IALJArkgeaA0UNhQ3xChBSZkhClkiilkgikEc3YsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [CIF = _t, Date = _t]),
#"Changed Type" = Table.Buffer( Table.TransformColumnTypes(Source,{{"CIF", Int64.Type}, {"Date", type date}})),
#"Sorted Rows" = Table.Sort(#"Changed Type",{ {"CIF", Order.Ascending},{"Date", Order.Ascending}}),
#"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Index", 0, 1, Int64.Type),
#"Added Custom" = Table.AddColumn(#"Added Index", "Previous Date", each if [Index] =0 then [Date] else if #"Added Index"{[Index]-1} [CIF] = [CIF] then #"Added Index"{[Index]-1} [Date] else [Date])
in
#"Added Custom"

As mentioned by some of the others, this may not be the most efficient place to do this as in order to sort your data to get this to work properly it will dramatically slow the data load/refresh

 


______________


Has this post solved your problem? Please mark it as a solution so that others can find it quickly and to let the community know your problem has been solved. 


If you found this post helpful, please give Kudos.


I work as a trainer and consultant for Microsoft 365, specialising in Power BI and Power Query. 


excelwithallison.com

View solution in original post

Highlighted

I am not clear if and how your problem was solved.
but in any case try if this is right for you

 

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bczBCcAwDEPRXXwOWJLTaUz2X6MtPgSHXD9PyjSSNkzhoAuCrZEm6Y+zxZKU4zklo8WS3xbzlFCLJQOXT3HH9QI=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [CIF = _t, DATE = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"CIF", Int64.Type}, {"DATE", type date}}),

    maxSmaller = (lst, el)=> 
    let 
    ls=List.Sort(lst)
    in ls{List.Max({List.PositionOf(ls,el)-1,0})},


     ms = (tab)=> Table.AddColumn(tab, "maxSmaller", each maxSmaller(tab[DATE], _[DATE])),

    
    #"Grouped Rows" = Table.Group(#"Changed Type", {"CIF"}, {{"ms", each ms(_)}}),
    #"Expanded ms" = Table.ExpandTableColumn(#"Grouped Rows", "ms", {"DATE", "maxSmaller"}, {"DATE", "maxSmaller"})
in
    #"Expanded ms"

 

View solution in original post

8 REPLIES 8
Highlighted
Super User II
Super User II

Looks like you're trying to reference a previous row in Power Query. Usually we use an Index column for this but you may be able to use your Date column as your Index.

You will also need to add a condition to check that CIF = previous CIF

With that in mind, see if this post helps you get what you need:
https://www.myonlinetraininghub.com/referencing-next-row-power-query

 


______________


Has this post solved your problem? Please mark it as a solution so that others can find it quickly and to let the community know your problem has been solved. 


If you found this post helpful, please give Kudos.


I work as a trainer and consultant for Microsoft 365, specialising in Power BI and Power Query. 


excelwithallison.com

Highlighted

Thank you Allison for the prompt response. I went through the link. But it doesn't help. I am looking for M Query formula to reference the next latest date for each of the CIF numbers. I have a database of 1 million records. 

Highlighted
Super User I
Super User I

Hi. This is not the best place to do it if you have a database as source because it has to query itself. Let's see. Add a custom column and insert the statement after "LastD", the final code should look like this:

 

= Table.AddColumn(#"Last Step", "LastD", 
    (Earlier) => 
        Table.Max(
             Table.SelectRows(
                Table.SelectColumns(#"Changed Type", {"Date", "CIF"})
            , each Earlier[Date] > [Date] and [CIF] = Earlier[CIF] 
        ) 
        , "Date", Earlier 
    )[Date] 
)

 

 Hope this helps,



If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Happy to help!

LaDataWeb Blog


View solution in original post

Highlighted

hi @Ramnath 

 

"I am looking for M Query formula to reference the next latest date for each of the CIF numbers. I have a database of 1 million records."

 

the "next latest date" is "local" by CIF?

or it is global?

The database is ordered by some column?

 

Highlighted

@Ramnath
You need to follow the Complex Table example in the link I posted.

Please try pasting this code into a Blank Query in Power Query in the Advanced Editor (replace everything that's in there with this code)

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjA0MjYxNTO3sDy0QElHyUjfXN/IwMhAKVYHQ85Y3xSnnJG+EU45A0N9IALJArkgeaA0UNhQ3xChBSZkhClkiilkgikEc3YsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [CIF = _t, Date = _t]),
#"Changed Type" = Table.Buffer( Table.TransformColumnTypes(Source,{{"CIF", Int64.Type}, {"Date", type date}})),
#"Sorted Rows" = Table.Sort(#"Changed Type",{ {"CIF", Order.Ascending},{"Date", Order.Ascending}}),
#"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Index", 0, 1, Int64.Type),
#"Added Custom" = Table.AddColumn(#"Added Index", "Previous Date", each if [Index] =0 then [Date] else if #"Added Index"{[Index]-1} [CIF] = [CIF] then #"Added Index"{[Index]-1} [Date] else [Date])
in
#"Added Custom"

As mentioned by some of the others, this may not be the most efficient place to do this as in order to sort your data to get this to work properly it will dramatically slow the data load/refresh

 


______________


Has this post solved your problem? Please mark it as a solution so that others can find it quickly and to let the community know your problem has been solved. 


If you found this post helpful, please give Kudos.


I work as a trainer and consultant for Microsoft 365, specialising in Power BI and Power Query. 


excelwithallison.com

View solution in original post

Highlighted

Yes. It's Local. Just to give you a background, it's the customer application data. So the customer can apply several times through different channels and in different months. We need to know when he has last applied and see whether it's within 30 days. If so, I need to make the current record inactive and make the previous record active.  

Highlighted

This is almost close to what I want. But the only problem is it's giving me 4x4 =16 combinations. Instead, I want only the previous max date against all the CIFs. Is it possible for you to help?

Highlighted

I am not clear if and how your problem was solved.
but in any case try if this is right for you

 

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bczBCcAwDEPRXXwOWJLTaUz2X6MtPgSHXD9PyjSSNkzhoAuCrZEm6Y+zxZKU4zklo8WS3xbzlFCLJQOXT3HH9QI=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [CIF = _t, DATE = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"CIF", Int64.Type}, {"DATE", type date}}),

    maxSmaller = (lst, el)=> 
    let 
    ls=List.Sort(lst)
    in ls{List.Max({List.PositionOf(ls,el)-1,0})},


     ms = (tab)=> Table.AddColumn(tab, "maxSmaller", each maxSmaller(tab[DATE], _[DATE])),

    
    #"Grouped Rows" = Table.Group(#"Changed Type", {"CIF"}, {{"ms", each ms(_)}}),
    #"Expanded ms" = Table.ExpandTableColumn(#"Grouped Rows", "ms", {"DATE", "maxSmaller"}, {"DATE", "maxSmaller"})
in
    #"Expanded ms"

 

View solution in original post

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors