Forum Discussion

gmasta1129's avatar
gmasta1129
Resolver I
10 months ago
Solved

Find if a date changes

Hello,    I have a report that contains 3 columns   1. Run Date 2. Portfolio Code 3. Maturity Date   I am trying to create a formula to show if there is a difference in the maturity date colu...
Need Help
ronrsnfld's avatar
ronrsnfld
Super User
10 months ago

In Power Query, you can add an Index column to refer to the previous row.

 

Paste the below code into the Advanced Editor to see how it works:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjTQN9Q3MjAyVdJRMje0MDAG0mb6RuYgMXOlWB2wCiOCKowJqjAjqMIcU4WxAUjMAqbCgqAZlgRVGBrgURILAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Run Date" = _t, #"facility code" = _t, #"maturity date" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Run Date", type date}, {"facility code", Int64.Type}, {"maturity date", type date}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Added Index", "Change?", each 
        if [Index] = 0 then "N" 
        else if #"Changed Type"[maturity date]{[Index]-1} = [maturity date] then "N" else "Y", type text)
in
    #"Added Custom"