Forum Discussion

Ramnath's avatar
Ramnath
Frequent Visitor
5 years ago
Solved

Previous Latest Date

[ Spoiler ] 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 i...
  • ibarrau's avatar
    ibarrau
    5 years ago

    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,

  • AllisonKennedy's avatar
    AllisonKennedy
    5 years ago
    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
  • Anonymous's avatar
    Anonymous
    5 years ago

    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"

     

Anonymous's avatar
Anonymous
Not applicable
5 years ago

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?

 

Ramnath's avatar
Ramnath
Frequent Visitor
5 years ago

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's avatar
    Anonymous
    Not applicable
    5 years ago

    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"