How to get only 1st rows with ID's having date between a range (45days) in Power Query

[Raj12]

I have data with Id's and dates.

Each ID can have multiple dates with them. 

As an output I want only those rows per ID :

1) If ID subsequent dates falls between 45 days then give 1st date row

2) If susequent date is outside 45 days then give both date rows

[latimeria · Accepted solution]

Hi @Raj12 ,

Give a try to this:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Xc47DsAgDAPQu2RGShwGykgrtYdA3P8aDYiWz/pkW86Z0kmOFCzKKqpUXDcx89X8ZMdi92WGr4vJLBfHXrO6h70bF3tSz4XRbeaFgc3syP+lvA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Id = _t, Date = _t]),
    #"Changed Type1" = Table.TransformColumnTypes(Source,{{"Date", type date}}),
    #"Grouped Rows" = Table.Group(#"Changed Type1", {"Id"}, {{"AllRows", each _, type table [Id=nullable text, Date=nullable date]}}),
    #"Added Shifted Date" = Table.TransformColumns(
        #"Grouped Rows",
        {
            {"AllRows",each 
                Table.FromColumns(    
                    Table.ToColumns(_) & {{null} & List.RemoveLastN(_[Date],1)},
                    Table.ColumnNames(_) & {"Previous Date"}
                )
            }
        }
    ),
    Expanded = Table.Combine(#"Added Shifted Date"[AllRows]),
    #"Changed Type" = Table.TransformColumnTypes(Expanded,{{"Previous Date", type date}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Delta", each Duration.Days([Date] - [Previous Date]),Int64.Type),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom", each [Delta] > 45 or [Delta] = null)
in
    #"Filtered Rows"

[Raj12 · Accepted solution]

latimeria Thank you for the help!

[latimeria · Accepted solution]

Hi @Raj12 ,

Give a try to this:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Xc47DsAgDAPQu2RGShwGykgrtYdA3P8aDYiWz/pkW86Z0kmOFCzKKqpUXDcx89X8ZMdi92WGr4vJLBfHXrO6h70bF3tSz4XRbeaFgc3syP+lvA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Id = _t, Date = _t]),
    #"Changed Type1" = Table.TransformColumnTypes(Source,{{"Date", type date}}),
    #"Grouped Rows" = Table.Group(#"Changed Type1", {"Id"}, {{"AllRows", each _, type table [Id=nullable text, Date=nullable date]}}),
    #"Added Shifted Date" = Table.TransformColumns(
        #"Grouped Rows",
        {
            {"AllRows",each 
                Table.FromColumns(    
                    Table.ToColumns(_) & {{null} & List.RemoveLastN(_[Date],1)},
                    Table.ColumnNames(_) & {"Previous Date"}
                )
            }
        }
    ),
    Expanded = Table.Combine(#"Added Shifted Date"[AllRows]),
    #"Changed Type" = Table.TransformColumnTypes(Expanded,{{"Previous Date", type date}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Delta", each Duration.Days([Date] - [Previous Date]),Int64.Type),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom", each [Delta] > 45 or [Delta] = null)
in
    #"Filtered Rows"