Help with GroupKind.Local MCode

[JanoLehocky]

Overview of Issue

I am trying to identify sequencial strings of dates that contain dates that are flagged as A/AType = MUA.

The current code that I have is not grouping correctly and I am not understanding why.

Example of Output

The code which I will share next is looking at the following data:

If the idea is to look for sequencial groupings of dates then in the example above the sequences would be:

March 1st to March 3 >> 3 days - with no MUAs in the sequence

March 12 to March 15 >> 4 days - with no MUAs in the sequence

May 1st to May 2nd >> 2 days - with a count of 2 MUAs in the sequence

Here is the code taht is grouping to provide the above table:

#"Grouped Rows" = Table.Group(#"Changed Type1", {"AbsentCode"}, {{"# of Days", each Table.RowCount(_), Int64.Type}, {"MUADaysPresent", each List.Max([#"AA Code - Selections with MUA"]), type logical}, {"Start Date",
each List.Min([#"Start Date A/A Type"]), type nullable date}, {"End Date",
each List.Max([#"Start Date A/A Type"]), type nullable date}, {"Details",
each _, type table [Org. Text=nullable text, WS rule=nullable text, #"Pers.No."=nullable number, Name of Employee=text, First Working Date=date, Permanent Start Date=date, #"Start Date A/A Type"=nullable date, #"A/AType"=nullable text, #"Absence/Attendance hours"=nullable number, Planned working hours=nullable number, Rec. Order=any, Rec.WBS el=any, Cost Centre=number, Name=nullable text, Department=any, Chit Work Area=any, Send. CCtr=any, ActTyp=any, Activity=any, Rec. CCtr=any, Network=any, OC=any, PS group=any, Lv=any, #"Prem. no."=any, ID=any, Created on=nullable number, Time=nullable number, Created by=text, Last chnge=date, Time2=datetime, Changed by=text, Short Text=any, L=any, Rg=any, Delay=any, OT Rsn=any, OT Rsn Desc=any, Reason=any, OpAc=any, Sub Operation=any, Reason Code Desc=any, AbsentCode=text, #"AA Code - Selections with MUA"=logical]}}, GroupKind.Local),

Any advice would be greatly appreciated to set me on the right path.

Thank you,

Jano

[JanoLehocky]

Thanks to @v-zhangti  for pointing out that more clarity on the desired output is required.

I have a spreadsheet here that has more details

https://docs.google.com/spreadsheets/d/1tTzzK0oDWJb9Aqwf5QPjJ7GEFBPRTeIH/edit?usp=sharing&ouid=10053...

[v-zhangti]

Hi, @JanoLehocky 

I couldn't finish understanding. Based on your example data, what would you expect the output to look like? You can show it in Excel.

Employee names can be expressed using employee ID1, 2, 3.

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.