Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Group Dates into date ranges

Hi,

 

I need to group a series of dates into date ranges. My data is as follows:

 

DateName
Thursday, 01 March 2018Mark
Thursday, 01 March 2018Mark
Saturday, 03 March 2018Mark
Wednesday, 07 March 2018John
Thursday, 08 March 2018John
Thursday, 15 March 2018Steve
Tuesday, 20 March 2018Mark
Wednesday, 21 March 2018Mark
Thursday, 22 March 2018Mark
Sunday, 01 April 2018Claudia
Tuesday, 03 April 2018Claudia

 

I need to determine the start and end dates for that episode for each name. Allong the lines of this:

 

DateNameSeriesBoundariesLast Date
Thursday, 01 March 2018MarkSeries StartThursday, 01 March 2018
Thursday, 01 March 2018Mark  
Saturday, 03 March 2018MarkSeries StartSaturday, 03 March 2018
Wednesday, 07 March 2018JohnSeries StartThursday, 08 March 2018
Thursday, 08 March 2018JohnSeries End 
Thursday, 15 March 2018SteveSeries StartThursday, 15 March 2018
Tuesday, 20 March 2018MarkSeries StartThursday, 22 March 2018
Wednesday, 21 March 2018Mark  
Thursday, 22 March 2018MarkSeries End 
Sunday, 01 April 2018ClaudiaSeries StartSunday, 01 April 2018
Tuesday, 03 April 2018ClaudiaSeries StartTuesday, 03 April 2018

 

Any help appreciated...

Need Help

7 Replies

  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    Community Champion
    7 years ago

    Anonymous

     

    Try this Power Query Solution

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCskoLSpOSazUUTAwVPBNLErOUDAyMLRQ0lECcrKBFC4FsTpEaFYAKwtOLCktgigzxqoMlwKQ5vDUlLxUqCXmqLq98jPyUF1ogceFFlg1K6ApMzRFVRZcklqWimIJqgqw7lKoA40MCAShkREe7xnhC0NcZqAHdWkeLD4cC4oyc2CKnHMSS1MyE0EewqoCxRvASMChGYeS2FgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Date = _t, Name = _t, #"Last Date" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Name", type text}, {"Last Date", type date}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"Date", Order.Ascending}}),
    #"Grouped Rows" = Table.Group(#"Sorted Rows", {"Name"}, {{"ALL", each _, type table}},GroupKind.Local),
    #"Expanded ALL" = Table.ExpandTableColumn(#"Grouped Rows", "ALL", {"Date", "SeriesBoundaries", "Last Date"}, {"Date", "SeriesBoundaries", "Last Date"}),
    #"Added Index" = Table.AddIndexColumn(#"Expanded ALL", "Index", 1, 1),
    #"Added Custom" = Table.AddColumn(#"Added Index", "Custom", each let pd=
let
myindex=[Index],
myname=[Name]
in
List.Max(
Table.SelectRows(#"Added Index",each [Index]=myindex-1 and [Name]=myname)[Date])
in
List.AnyTrue({pd=null,pd=[Date],pd=Date.AddDays([Date],-1)})),
    #"Reordered Columns" = Table.ReorderColumns(#"Added Custom",{"Name", "Custom", "Date", "SeriesBoundaries", "Last Date", "Index"}),
    #"Grouped Rows1" = Table.Group(#"Reordered Columns", {"Name", "Custom"}, {{"ALL", each Table.AddIndexColumn(_,"IndexF",1,1), type table}},GroupKind.Local),
    #"Added Custom1" = Table.AddColumn(#"Grouped Rows1", "LastDate", each List.Max([ALL][Date])),
    #"Expanded ALL1" = Table.ExpandTableColumn(#"Added Custom1", "ALL", {"Date", "IndexF"}, {"Date", "IndexF"}),
    #"Added Custom2" = Table.AddColumn(#"Expanded ALL1", "Custom.1", each if [IndexF]=1 then [LastDate] else null),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom2",{"Custom", "IndexF", "LastDate"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Custom.1", "LastDate"}})
in
    #"Renamed Columns"