Comparing dates in different multiple rows based on ID

[Sharkybu]

Hello,

I have a database in which ID's can have multiple rows with multiple start dates. 

I need to know to know if the same ID has the same start dates or different ones.

I already got help on that: https://community.fabric.microsoft.com/t5/Power-Query/Comparing-multiple-rows-with-the-same-ID/m-p/3...

but I need further information about the different start dates, I need to know if they are consecutive or not.

For exmple I can have a table like this:

I need to know that it has the same date (31/12/22), consecutive dates (31/12/22,1/1/23) and non consecutive 

In the end I need one row per ID with:

either one column that says one of the following values:

same

consecutive 

non consecutive 

same and consecutive 

same and non consecutive 

consecutive and non consecutive 

same, consecutive and non consecutive 

or three columns: 

same | consecutive | non consecutive 

with a yes and no values.

Thank you in advance for your help.

[ronrsnfld · Accepted solution]

Try this:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTLUN9Q3MlaK1YFwTfXN9I0M4FxjoLSRvpERFoFYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [id = _t, #"Start date" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"id", Int64.Type}, {"Start date", type date}},"en-150"),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"id"}, {
        {"same", each if List.Count(List.Distinct([Start date])) < Table.RowCount(_) then "yes" else "no", type text},
        
        {"consecutive", (t)=> 
            let 
               sorted = List.Sort(t[Start date]),
               offset = List.RemoveFirstN(sorted,1) & {null},
               tbl = Table.FromColumns({sorted} & {offset},
                        type table[d1=date, d2=date]),
               com = Table.AddColumn(tbl,"consec", each Date.AddDays([d1],1) = [d2], type logical)
            in  
               if List.AnyTrue(com[consec]) then "yes" else "no", type text
                 },
        
        {"non consecutive", (t)=> 
            let 
               sorted = List.Sort(t[Start date]),
               offset = List.RemoveFirstN(sorted,1) & {null},
               tbl = Table.FromColumns({sorted} & {offset},
                        type table[d1=date, d2=date]),
               remSame = Table.SelectRows(tbl, each[d1] <> [d2]), 
               com = Table.AddColumn(remSame,"consec", each Date.AddDays([d1],1) = [d2], type logical)
            in  
               if List.AllTrue(com[consec]) = false then "yes" else "no", type text
                 }})
in
    #"Grouped Rows"

[Sharkybu]

Thank you so much.

That is what I needed but I'm having problem with the last part.

for the non consecutive I'm getting all yes and I know that's a mistake.

I changed It to:

{"non consecutive", (t)=> 
let 
sorted = List.Sort(t[Start date]),
offset = List.RemoveFirstN(sorted,1) & {null},
tbl = Table.FromColumns({sorted} & {offset},
type table[d1=date, d2=date]),
com = Table.AddColumn(tbl,"consec", each Duration.Days([d2]-[d1])>1, type logical)
in 
if List.AnyTrue(com[consec]) = true then "yes" else "no", type text
}

and now it workes.

[ronrsnfld]

Glad you figured it out.

Not having a comprehensive set of examples made it difficult for me.

The problem turns out to be that the last row of the subtable for non-consecutives (or for any of them for that matter) has a null in the offset date column.

This only makes a difference in the algorithm for non-consecutive.

So perhaps just removing that last row would also work.

Something like:

remSame = Table.RemoveLastN(Table.SelectRows(tbl, each[d1] <> [d2]),1),

[Sharkybu]

Thank you. I'll try that.