Hi @fakladios,
The most straight forward solution is to add some additional logic in power query. You can transfer the M code below from the SortRows step onward to your existing “Transformed Table” query.
let
Source = Excel.Workbook(File.Contents(FileLocation), null, true),
tData_Table = Source{[Item="tData",Kind="Table"]}[Data],
#"Changed Type" = Table.TransformColumnTypes(tData_Table,{{"Patient ID", Int64.Type}, {"Drug", type text}, {"Current DispDATE", type date}, {"Previous Script date", type date}, {"Index", Int64.Type}, {"Disp Inrtevals", Int64.Type}, {"Overlap logic", Int64.Type}}),
SortRows = Table.Sort(#"Changed Type",{{"Patient ID", Order.Ascending}, {"Current DispDATE", Order.Ascending}, {"Previous Script date", Order.Ascending}}),
#"Grouped Rows" = Table.Group(SortRows, {"Patient ID"}, {{"AllRows", each _, type table [Patient ID=nullable number, Current DispDATE=nullable date, Drug=nullable text, Index=nullable number, Disp Inrtevals=nullable number, Overlap logic=nullable number, Previous Script date=nullable date]}}),
#"Added Custom" = Table.AddColumn(#"Grouped Rows", "Transformations", each
let
myTable = [AllRows],
Test1 = Table.AddColumn(myTable, "Test logic", each let myValue = if [Disp Inrtevals] = null then 0 else [Disp Inrtevals] in if ([Drug]= "1" and myValue <=90) or ([Drug]= "2" and myValue <=30 ) then 1 else 0),
GroupTest1 = Table.Group(Test1, {"Test logic"}, {{"AllRows2", each _, type table [Patient ID=nullable number, Current DispDATE=nullable date, Drug=nullable text, Index=nullable number, Disp Inrtevals=nullable number, Overlap logic=nullable number, Previous Script date=nullable date, Test logic=number]}}, GroupKind.Local),
Test2 = Table.AddColumn(GroupTest1, "TestLogic2", each Table.FirstN([AllRows2], 1){0}[Drug] ="1" and Table.LastN([AllRows2], 1){0}[Drug] ="1" and List.Contains([AllRows2][Drug], "2" )),
ExpandAllRows2 = Table.ExpandTableColumn(Test2, "AllRows2", {"Patient ID", "Current DispDATE", "Drug", "Index", "Disp Inrtevals", "Overlap logic", "Previous Script date"}, {"Patient ID", "Current DispDATE", "Drug", "Index", "Disp Inrtevals", "Overlap logic", "Previous Script date"}),
CountSeq = Table.AddColumn(ExpandAllRows2, "Count Seq", each if [TestLogic2] = true and [Drug] = "2" then 1 else 0)
in
CountSeq, type table [Patient ID=nullable number, Current DispDATE=nullable date, Drug=nullable text, Index=nullable number, Disp Inrtevals=nullable number, Overlap logic=nullable number, Previous Script date=nullable date, Test logic=number, Count Seq=number]
)[[Transformations]],
#"Expanded Transformations" = Table.ExpandTableColumn(#"Added Custom", "Transformations", {"Patient ID", "Current DispDATE", "Drug", "Index", "Disp Inrtevals", "Overlap logic", "Previous Script date", "Count Seq"}, {"Patient ID", "Current DispDATE", "Drug", "Index", "Disp Inrtevals", "Overlap logic", "Previous Script date", "Count Seq"})
in
#"Expanded Transformations"
.
With this result.
A simple SUM( ‘Transformed Table’[Count Seq] ) should get you the desired result.
And this is a break down of what the query steps above do:
Performed the initial test.
Group By on the test result with the additional GroupKind.Local parameter setting
With that setting, every time the value changes it splits into another nested table.
Performed the second test to check for the required sequence
Expanded the nested table and added the Count Seq
I hope this is helpful.
