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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
faustusxanthis
Frequent Visitor

Check if dates are continuous using Power Query

Hello!

 

I apologize for probable unclear questions. I do not know exactly what to ask so I can't easily search the forums or online. 

 

I have this set of data

 

Person NameStart Date of activityEnd Date of activityType of activityResult
Person 109/26/202309/26/2023Activity 1HAS GAPS
Person 108/05/202309/01/2023Activity 1HAS GAPS
Person 107/07/202308/05/2023Activity 1HAS GAPS
Person 109/26/202309/26/2023Activity 2NO GAPS
Person 108/05/202309/26/2023Activity 2NO GAPS
Person 107/07/202308/05/2023Activity 2NO GAPS
Person 209/26/202309/26/2023Activity 1HAS GAPS
Person 205/02/202309/26/2023Activity 1HAS GAPS
Person 202/01/202304/04/2023Activity 1HAS GAPS
Person 204/03/202304/04/2023Activity 1HAS GAPS

 

I would need to know which person name and corresponding type of activity has gaps in the start and end dates

Eg: Person 1 doing Activity 1 has gap from 09/01/2023 to 09/26/2023

Person 1 does not have gaps doing Activity 2 which started from 07/07/2023 to 09/26/2023

Person 2 has gaps doing Activity 1 from 04/04/2023 to 05/02/2023

 

The result should be in the Result tab on the right. And if possible, identify the date ranges that has gaps. 

 

I need this to be done in Power Query. 

 

Thank you in advance!

1 REPLY 1
lbendlin
Super User
Super User

lbendlin_0-1695862529530.png

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCkgtKs7PUzBU0lEysNQ3MtM3MjAyRuc4JpdklmWWVAKVxeqg6rHQNzBF0mNgSIQec30ggulBNgCPHsJuMyLkNqL0EHYbih4jMsINrMdU38CIVD1GSMFrYKIPRIT1AJUZE9QTCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Person Name" = _t, #"Start Date of activity" = _t, #"End Date of activity" = _t, #"Type of activity" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Person Name", type text}, {"Start Date of activity", type date}, {"End Date of activity", type date}, {"Type of activity", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Person Name", "Type of activity"}, {{"From", each List.Min([Start Date of activity]), type nullable date}, {"To", each List.Max([End Date of activity]), type nullable date}, {"Rows", each _, type table [Person Name=nullable text, Start Date of activity=nullable date, End Date of activity=nullable date, Type of activity=nullable text]}}),
    GapFinder = (tbl)=>
        let
            #"Sorted Rows" = Table.Sort(tbl,{{"Start Date of activity", Order.Ascending}}),
            #"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Index", 0, 1, Int64.Type),
            #"Added Custom" = Table.AddColumn(#"Added Index", "Gap", each if [Index]=0 then 0 else  Int64.From([Start Date of activity]-#"Added Index"{[Index]-1}[End Date of activity]) ,Int64.Type)
        in
            #"Added Custom",
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each GapFinder([Rows])),
    #"Aggregated Custom" = Table.AggregateTableColumn(#"Added Custom", "Custom", {{"Gap", List.Sum, "Sum of Gap"}}),
    #"Removed Other Columns" = Table.SelectColumns(#"Aggregated Custom",{"Person Name", "Type of activity", "From", "To", "Sum of Gap"})
in
    #"Removed Other Columns"

or like this  (which also highlights that you have overlaps)

lbendlin_1-1695862771829.png

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCkgtKs7PUzBU0lEysNQ3MtM3MjAyRuc4JpdklmWWVAKVxeqg6rHQNzBF0mNgSIQec30ggulBNgCPHsJuMyLkNqL0EHYbih4jMsINrMdU38CIVD1GSMFrYKIPRIT1AJUZE9QTCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Person Name" = _t, #"Start Date of activity" = _t, #"End Date of activity" = _t, #"Type of activity" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Person Name", type text}, {"Start Date of activity", type date}, {"End Date of activity", type date}, {"Type of activity", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Person Name", "Type of activity"}, {{"From", each List.Min([Start Date of activity]), type nullable date}, {"To", each List.Max([End Date of activity]), type nullable date}, {"Rows", each _, type table [Person Name=nullable text, Start Date of activity=nullable date, End Date of activity=nullable date, Type of activity=nullable text]}}),
    GapFinder = (tbl)=>
        let
            #"Sorted Rows" = Table.Sort(tbl,{{"Start Date of activity", Order.Ascending}}),
            #"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Index", 0, 1, Int64.Type),
            #"Added Custom" = Table.AddColumn(#"Added Index", "Gap", each if [Index]=0 then 0 else  Int64.From([Start Date of activity]-#"Added Index"{[Index]-1}[End Date of activity]) ,Int64.Type)
        in
            #"Added Custom",
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each GapFinder([Rows])),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Start Date of activity", "End Date of activity", "Gap"}, {"Start Date of activity", "End Date of activity", "Gap"}),
    #"Removed Other Columns" = Table.SelectColumns(#"Expanded Custom",{"Person Name", "Type of activity", "Start Date of activity", "End Date of activity", "Gap"})
in
    #"Removed Other Columns"

 

 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

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