Forum Discussion
Identify start and end date in a date column
Hi all,
I have a table where we keep the absence of our employees like this:
And this is the result I want: For each period of abscence the name, the start date, the end date en the reason of abscence:
You cannot group on one or two columns because it will add different periods together. I've tried already something in Power Query by creating gaps between the different periods of abscence per name and then fill in the end date for each period. In this way I can group on name and end date. But it creates no gaps if the name is different and then the end date is not always correct:
I don't know what I can do about this?
Or does anyone else has a solution for my problem?
Thanks in advance!
Jo
4 Replies
You will need to use GroupKind.Local parameter for grouping.
See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test (later on when you use the query on your dataset, you will have to change the source appropriately. If you have columns other than these, then delete Changed type step and do a Changed type for complete table from UI again)
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WilDSUTLUN9U3MjAyAjI9c3KUYnVgwmbYhc3RhSPBwoYGMPGA1KLi/LxEFElDfJJG+CSNsUpGQSThLvfIz8lMSaxEcqYhDucbYrg/Ck0cxSJHoICRPtz5IanFJYZIEkbIEkZKsbEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, Date = _t, #"Reason of absence" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"Date", type date}, {"Reason of absence", type text}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"Name", "Reason of absence"}, {{"Start Date", each List.Min([Date]), type nullable date}, {"End Date", each List.Max([Date]), type nullable date}}, GroupKind.Local), #"Reordered Columns" = Table.ReorderColumns(#"Grouped Rows",{"Name", "Start Date", "End Date", "Reason of absence"}) in #"Reordered Columns"Great, it works! Thank you very much!!!
I've tried this approach before, but without the GroupKind.Local. Fantastic that it works by adding this.
Vijay_A_Verma unfortunatly, it does not work 100%.
For example if i add a row to my inputtable:
Z 31/01/2022 Personal Reasons
It wil show this as a result: Z 17/01/2022 31/01/2022 Personal Reasons while there normally should be two rows for this.
Any ideas?
Jo
If Z is added immediately after another Z with same reason of absence, then they will be grouped together. It can't be another row. This logic is being followed for all rows previously.
