New Custom Column to Keep track of Status Change

[Anonymous]

Hello everyone,

I've been dealing with a problem for the past few days that I unfortunately can't seem to find a solution for. I hope you can help me on this.
I have this sample of my dataset where I have 4 different Keys, which have a Status for each Extraction Date. The Status are only 3 and can variate from "A", "B" and "C", without any specific order.

(Dataset in table mode)

Key	ExtractionDate	Status
JJUYXB2764JSG59DVZ62	21/06/2023	B
JJUYXB2764JSG59DVZ62	22/06/2023	C
JJUYXB2764JSG59DVZ62	23/06/2023	C
JJUYXB2764JSG59DVZ62	24/06/2023	C
JJUYXB2764JSG59DVZ62	25/06/2023	B
JJUYXB2764JSG59DVZ62	26/06/2023	A
JJUYXB2764JSG59DVZ62	27/06/2023	A
JJUYXB2764JSG59DVZ62	28/06/2023	C
JJUYXB2764JSG59DVZ62	29/06/2023	C
JJUYXB2764JSG59DVZ62	30/06/2023	C
JJUYXB2764JSG59DVZ62	01/07/2023	C
JJUYXB2764JSG59DVZ62	02/07/2023	C
PO8326BDJ456QVZA42JDSI8	27/06/2023	C
PO8326BDJ456QVZA42JDSI8	28/06/2023	B
PO8326BDJ456QVZA42JDSI8	29/06/2023	A
HGA8635SNGJG8735DXVBC7365GS	25/06/2023	C
HGA8635SNGJG8735DXVBC7365GS	26/06/2023	C
HGA8635SNGJG8735DXVBC7365GS	27/06/2023	B
JDSYEBV74825VBC67398DXNBCD6Y	21/06/2023	B
JDSYEBV74825VBC67398DXNBCD6Y	22/06/2023	C
JDSYEBV74825VBC67398DXNBCD6Y	23/06/2023	A

(Picture with the same dataset but with colours)

Original Dataset

I need to create 4 new columns (NrDays_Status_A, NrDays_Status_B, NrDays_Status_C, NrDays_Status_B_or_C) where I would know by Key and Extraction Date how many consecutive days the Status was. Something like this:

(Dataset with Expected Outcome in table mode)

Keyy	ExtractionDate	Status	NrDays_Status_A	NrDays_Status_B	NrDays_Status_C	NrDays_Status_B_or_C
JJUYXB2764JSG59DVZ62	21/06/2023	B	0	1	0	1
JJUYXB2764JSG59DVZ62	22/06/2023	C	0	0	1	2
JJUYXB2764JSG59DVZ62	23/06/2023	C	0	0	2	3
JJUYXB2764JSG59DVZ62	24/06/2023	C	0	0	3	4
JJUYXB2764JSG59DVZ62	25/06/2023	B	0	1	0	5
JJUYXB2764JSG59DVZ62	26/06/2023	A	1	0	0	0
JJUYXB2764JSG59DVZ62	27/06/2023	A	2	0	0	0
JJUYXB2764JSG59DVZ62	28/06/2023	C	0	0	1	1
JJUYXB2764JSG59DVZ62	29/06/2023	C	0	0	2	2
JJUYXB2764JSG59DVZ62	30/06/2023	C	0	0	3	3
JJUYXB2764JSG59DVZ62	01/07/2023	C	0	0	4	4
JJUYXB2764JSG59DVZ62	02/07/2023	C	0	0	5	5
PO8326BDJ456QVZA42JDSI8	27/06/2023	C	0	0	1	1
PO8326BDJ456QVZA42JDSI8	28/06/2023	B	0	1	0	2
PO8326BDJ456QVZA42JDSI8	29/06/2023	A	1	0	0	0
HGA8635SNGJG8735DXVBC7365GS	25/06/2023	C	0	0	1	1
HGA8635SNGJG8735DXVBC7365GS	26/06/2023	C	0	0	2	2
HGA8635SNGJG8735DXVBC7365GS	27/06/2023	B	0	1	0	3
JDSYEBV74825VBC67398DXNBCD6Y	21/06/2023	B	0	1	0	1
JDSYEBV74825VBC67398DXNBCD6Y	22/06/2023	C	0	0	1	2
JDSYEBV74825VBC67398DXNBCD6Y	23/06/2023	A	1	0	0	0

(Picture with the Expected Outcome but with colours)

Expected Outcome
Let me know if you need more information and thank you in advance for your help.

[wdx223_Daniel · Accepted solution]

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ldIxDsMgDAXQuzBnIIANjAEqWoa0FSpKGuX+1yhSpSRSpeBOf3mD/e1lYSm95skJjSrlCDaUNwrWMQXcmpqOrd0psjX9Oeo5p6CegkR7pp7LmkMLKQoCykxIQZqCDAXtjT/uRgp0ISnAZ3kPSqSQb+awIMXBVum5w62waxwMSshjTNFoCWEqzmuJEPPhTJ5o5R9W7ecPeb64opURUBVqaU2YRucDzr//28aHP27h7z/XKtYP", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Key = _t, ExtractionDate = _t, Status = _t]),
    Custom1 = Table.TransformColumns(Source,{"ExtractionDate",each Date.From(Number.From(_))}),
    StatusList={"A","B","C",{"B","C"}},
    StartValues=List.Repeat({0},List.Count(StatusList)),
    ColNames=List.Transform(StatusList,each "NrDays_Status_" & (if _ is text then _ else Text.Combine(_,"_or_"))),
    Custom2 = List.Accumulate(Table.ToRows(Custom1),{{},{},{}},(x,y)=>let a=if y{0}=x{1}{0}? and Date.AddDays(y{1},-1)=x{1}{1}? then x{2} else StartValues,b=List.Transform(List.Positions(StatusList),each Byte.From(if StatusList{_} is text then StatusList{_}=y{2} else List.Contains(StatusList{_},y{2}))+a{_}*Byte.From(if StatusList{_} is text then x{1}{2}?=y{2} else List.ContainsAll(StatusList{_},{x{1}{2}?,y{2}}))) in {x{0}&{y&b},y,b}),
    Custom3=#table(Table.ColumnNames(Source)&ColNames,Custom2{0})
in
    Custom3

[Anonymous]

Hello @wdx223_Daniel , first of all thank you very much for your help/answer/solution. This is exactly what I wanted.

I'm still trying to understand the "Custom2" step 😅, not wanting to abuse your generosity, could you would be possible to explain this step?

One more thing I explained that it would be for consecutive days but I forgot to mention that on weekends and holidays there are no extractions. That is, it is necessary to take into account the last day available before the current extraction day, but not necessarily -1 day.