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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Ramnath
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
ibarrau
Super User
Super User

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

@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

Please @mention me in your reply if you want a response.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as 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 C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

View solution in original post

Anonymous
Not applicable

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
ibarrau
Super User
Super User

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

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?

AllisonKennedy
Super User
Super User

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

Please @mention me in your reply if you want a response.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as 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 C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

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. 

@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

Please @mention me in your reply if you want a response.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as 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 C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

Anonymous
Not applicable

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?

 

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.  

Anonymous
Not applicable

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"

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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

Top Solution Authors
Top Kudoed Authors