Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
AlHMCT
Regular Visitor

How to count until it reaches a blank then stop counting

‎12-06-2024 03:32 AM

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.

Labels:
Message 1 of 3
342 Views
0
1 ACCEPTED SOLUTION
dufoq3
Super User
Super User

‎12-06-2024 05:40 AM

Hi @AlHMCT, check this:

 

Output

dufoq3_0-1733492395059.png

 

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

 


Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

View solution in original post

Message 2 of 3
301 Views
2 REPLIES 2
Anonymous
Not applicable

‎12-08-2024 07:16 PM

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

Message 3 of 3
177 Views
0
dufoq3
Super User
Super User

‎12-06-2024 05:40 AM

Hi @AlHMCT, check this:

 

Output

dufoq3_0-1733492395059.png

 

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

 


Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

Message 2 of 3
302 Views

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

View All