Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago
Solved

How to count until it reaches a blank then stop counting

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.

 

DateOrder NumberCurrent Status
06/12/2024 1arrived
06/12/20242arrived
06/12/20243arrived
06/12/20244in transit 
06/12/20245in transit 
06/12/20246in transit 
06/12/20247null
06/12/20248arrived
06/12/20249in transit 
06/12/202410null
06/12/202411in transit 
06/12/202412in transit 
07/12/20241arrived
07/12/20242arrived
07/12/20243arrived
07/12/20244arrived
07/12/20245in transit 
07/12/20246arrived
07/12/20247null
07/12/20248in transit 
08/12/20241arrived 
08/12/20242arrived 
08/12/20243in transit 
08/12/20244in transit 
08/12/20245in transit 
08/12/20246arrived
08/12/20247null
08/12/20248in transit 
08/12/20249null
08/12/202410in transit 
08/12/202411in transit 
08/12/202412in transit 

 

the desired output looks like this:

 

DateOrder NumberCurrent Status desired output
06/12/20241arrived 0
06/12/20242arrived 0
06/12/20243arrived 0
06/12/20244in transit  1
06/12/20245in transit  1
06/12/20246in transit  1
06/12/20247null 0
06/12/20248arrived 0
06/12/20249in transit  0
06/12/202410null 0
06/12/202411in transit  0
06/12/202412in transit  0
07/12/20241arrived 0
07/12/20242arrived 0
07/12/20243arrived 0
07/12/20244arrived 0
07/12/20245in transit  1
07/12/20246arrived 0
07/12/20247null 0
07/12/20248in transit  0
08/12/20241arrived  0
08/12/20242arrived  0
08/12/20243in transit  1
08/12/20244in transit  1
08/12/20245in transit  1
08/12/20246arrived 0
08/12/20247null 0
08/12/20248in transit  0
08/12/20249null 0
08/12/202410in transit  0
08/12/202411in transit  0
08/12/202412in transit  0

 

DateTotals
06/12/20243
07/12/20241
08/12/20243

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

Need Help
  • dufoq3's avatar
    dufoq3
    1 year ago

    Hi Anonymous, 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

     

2 Replies

  • dufoq3's avatar
    dufoq3
    Icon for Community Champion rankCommunity Champion
    1 year ago

    Hi Anonymous, 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

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi Anonymous ,

    Thanks for dufoq3's good answer!
    And Anonymous , 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