Extracting start and end dates

[NickDSL]

Extracting Start and End Dates from a single column based on a set of unique values.

10m ago

Hi I have the following data:

ContactID	EffectiveDate	TotalPts
405950	1/26/2023	86
405950	12/8/2022	57
405950	8/23/2023	204
405950	8/7/2023	129
405992	6/23/2023	78
405992	1/15/2023	62

I'm trying to get it in the following format in power query to be transformed/expanded later in the query.

ContactID	StartDate	EndDate	TotalPts
405950	12/8/2022	1/26/2023	57
405950	1/27/2023	8/7/2023	86
405950	8/8/2023	8/23/2023	129
405950	8/24/2023	PRESENTDATE	204
405992	1/15/2023	6/23/2023	62
405992	6/24/2023	PRESENTDATE	78

Where PRESENTDATE is equal to today so in this case it would be equal to 10/3/2023.

Looking for a how to here. Or if its even possible.

Please note my EffectiveDate ARE NOT ordered. This is how they would appear in the raw data.

Thanks.

[lbendlin · Accepted solution]

let
    Convert = (tbl)=>
    let
        #"Sorted Rows" = Table.Sort(tbl,{{"EffectiveDate", Order.Ascending}}),
        #"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Index", 0, 1, Int64.Type),
        #"Added Custom" = Table.AddColumn(#"Added Index", "StartDate", each if [Index]=0 then [EffectiveDate] else [EffectiveDate]+#duration(1,0,0,0),type date),
        #"Added Custom1" = Table.AddColumn(#"Added Custom", "EndDate", each try #"Added Custom"{[Index]+1}[EffectiveDate] otherwise null,type date)
    in
        #"Added Custom1",
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("XczBCQAhDETRXnIWkowmai1i/20YDy66tw+PmTGoiHUTSqQMZwhydHOa6TZw24Zoq68F5LODlD/WY4r+Wd9Hfg1re01Z7ZiD5lw=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ContactID = _t, EffectiveDate = _t, TotalPts = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ContactID", type text}, {"EffectiveDate", type date}, {"TotalPts", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"ContactID"}, {{"Rows", each _, type table [ContactID=nullable text, EffectiveDate=nullable date, TotalPts=nullable number]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Convert([Rows])),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"TotalPts", "StartDate", "EndDate"}, {"TotalPts", "StartDate", "EndDate"}),
    #"Removed Other Columns" = Table.SelectColumns(#"Expanded Custom",{"ContactID", "TotalPts", "StartDate", "EndDate"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Removed Other Columns",{{"TotalPts", Int64.Type}, {"StartDate", type date}, {"EndDate", type date}})
in 
    #"Changed Type1"

How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done".