Forum Discussion
Solved
Extracting start and end dates
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.
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".
1 Reply
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".