Forum Discussion

JanoLehocky's avatar
JanoLehocky
Helper I
3 years ago

Help with GroupKind.Local MCode

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

 

 

2 Replies