How to count until it reaches a blank then stop counting

[AlHMCT]

Hello everyone,

I am creating a powerBI which counts how many apples are in transit before a blank occurs. The blank signifies that the apple has not been picked yet, hence ending our reach. 

This also resets every day to be able to display our daily reach.

Date	Order Number	Current Status
06/12/2024	1	arrived
06/12/2024	2	arrived
06/12/2024	3	arrived
06/12/2024	4	in transit
06/12/2024	5	in transit
06/12/2024	6	in transit
06/12/2024	7	null
06/12/2024	8	arrived
06/12/2024	9	in transit
06/12/2024	10	null
06/12/2024	11	in transit
06/12/2024	12	in transit
07/12/2024	1	arrived
07/12/2024	2	arrived
07/12/2024	3	arrived
07/12/2024	4	arrived
07/12/2024	5	in transit
07/12/2024	6	arrived
07/12/2024	7	null
07/12/2024	8	in transit
08/12/2024	1	arrived
08/12/2024	2	arrived
08/12/2024	3	in transit
08/12/2024	4	in transit
08/12/2024	5	in transit
08/12/2024	6	arrived
08/12/2024	7	null
08/12/2024	8	in transit
08/12/2024	9	null
08/12/2024	10	in transit
08/12/2024	11	in transit
08/12/2024	12	in transit

the desired output looks like this:

Date	Order Number	Current Status		desired output
06/12/2024	1	arrived		0
06/12/2024	2	arrived		0
06/12/2024	3	arrived		0
06/12/2024	4	in transit		1
06/12/2024	5	in transit		1
06/12/2024	6	in transit		1
06/12/2024	7	null		0
06/12/2024	8	arrived		0
06/12/2024	9	in transit		0
06/12/2024	10	null		0
06/12/2024	11	in transit		0
06/12/2024	12	in transit		0
07/12/2024	1	arrived		0
07/12/2024	2	arrived		0
07/12/2024	3	arrived		0
07/12/2024	4	arrived		0
07/12/2024	5	in transit		1
07/12/2024	6	arrived		0
07/12/2024	7	null		0
07/12/2024	8	in transit		0
08/12/2024	1	arrived		0
08/12/2024	2	arrived		0
08/12/2024	3	in transit		1
08/12/2024	4	in transit		1
08/12/2024	5	in transit		1
08/12/2024	6	arrived		0
08/12/2024	7	null		0
08/12/2024	8	in transit		0
08/12/2024	9	null		0
08/12/2024	10	in transit		0
08/12/2024	11	in transit		0
08/12/2024	12	in transit		0

Date	Totals
06/12/2024	3
07/12/2024	1
08/12/2024	3

Any help would be greatly appreciated, feel free to ask for more details to understand it better.

[Anonymous]

Hi @AlHMCT ,

Thanks for dufoq3's good answer!
And @AlHMCT , it looks like the solution in this reply can solve your problem well. Have you solved your problem? If solved please accept the reply in this post as a solution to help more others facing the same problem to find a solution quickly, thank you very much!

Best Regards,
Dino Tao

[dufoq3 · Accepted solution]

Hi @AlHMCT, check this:

Output

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("lZK9DsIwDIRfJcpcqYkLtfssVYdKMESqMoTC81OYSuIfMTjLnU+5T55nH8Y+Qg8BLr7zLh7PWkp63W9+6SoVNHHQxM+k7Pay5kfaXWu4WobRMuAx+bltrULazyYrNwYxOEZzGTgHnh3151ADjhpwrICLIgsbK9ji9i9orEA3ucSXZVRQ1cEKZ2+MrNqk1SaxNv1TexJXv8elI2NvjNQbW94=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, #"Order Number" = _t, #"Current Status" = _t]),
    CorrectedNulls = Table.ReplaceValue(Source,"null",null,Replacer.ReplaceValue,{"Current Status"}),
    ChangedType = Table.TransformColumnTypes(CorrectedNulls,{{"Order Number", type number}, {"Date", type date}}, "sk-SK"),
    GroupedRows = Table.Group(ChangedType, {"Date"}, {{"All", each Table.FillDown(Table.AddColumn(_, "Output", (x)=> if x[Current Status] = null then 0 else null), {"Output"}), type table}}, GroupKind.Local),
    Combined = Table.Combine(GroupedRows[All]),
    RaplecedOutput = Table.ReplaceValue(Combined,
        each Text.Trim([Current Status]) = "in transit" and [Output] = null,
        each 1,
        (x,y,z)=> if y then z else x ?? 0,
        {"Output"} )
in
    RaplecedOutput