Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Netrelemo
Helper IV
Helper IV

Row number by group only if condition is true

I am struggling to count how long "the run" is, in each group. 

each group should have a line item every month, but sometimes they don't. 

The challenge is that if a lineitem does not occur in any particular month period, then the "counter" must reset. 

 

Here's a data sample:

 

LabelPeriodRun
alpha2023-04-251
alpha2023-05-252
alpha2023-06-253
alpha2023-08-251
beta2023-03-251
beta2023-08-251

 

 

I can easily use the two index approach to find the run, but I cannot see how to take into account thatthere may be gaps in the group. 

 

 

 

  #"Added Index1" = Table.AddIndexColumn(#"Sorted Rows", "Index1", 0, 1, Int64.Type),
    #"Added Index2" = Table.AddIndexColumn(#"Added Index1", "Index2", 1, 1, Int64.Type),
    #"Merged Self" = Table.NestedJoin(#"Added Index2", {"Index2"}, #"Added Index2", {"Index1"}, "Added Index2", JoinKind.LeftOuter),

 

 

 

 

Help?

1 ACCEPTED SOLUTION
danextian
Super User
Super User

Hi @Netrelemo 

Please try this (beware: the solution is not very straightforward)

danextian_0-1693217492652.png

 

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSswpyEhU0lEyMjAy1jUw0TUyVYrVQRc2xS5shl3YAiaclFqCEDXGKopdrSVMND0xNxeLEajCJtiFTbELm4OFYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Label = _t, Period = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Label", type text}, {"Period", type date}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Label"}, {{"Grouped", each _, type table [Label=nullable text, Period=nullable date]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Grouped_Index", each Table.AddIndexColumn([Grouped], "Index", 0, 1)),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom_Index2", each let 
x = [Grouped_Index],
y = x[Index]
in
Table.AddColumn(x, "Prev", each try x[Period]{[Index]-1} otherwise null)),
    #"Expanded Custom_Index2" = Table.ExpandTableColumn(#"Added Custom1", "Custom_Index2", {"Index", "Period", "Prev"}, {"Index", "Period", "Prev"}),
    #"Removed Columns1" = Table.RemoveColumns(#"Expanded Custom_Index2",{"Grouped", "Grouped_Index"}),
    #"Added Custom2" = Table.AddColumn(#"Removed Columns1", "Diff", each let x = Number.Round(Number.From(([Period]- [Prev])/( 365.25 / 12 )) ,0 ) in if x = null then 1 else x, Int64.Type),
    #"Removed Columns2" = Table.RemoveColumns(#"Added Custom2",{"Prev"}),
    #"Added to Column" = Table.TransformColumns(#"Removed Columns2", {{"Index", each _ + 1, type number}}),
    #"Added Custom3" = Table.AddColumn(#"Added to Column", "Running Count", each 
let
x = [Label], 
y = Table.SelectRows(#"Added to Column", each [Label] = x) 
in List.Accumulate(List.FirstN(y[Diff],[Index]),  0,
                (state, current) =>
                    if current = 1 then state + 1
                    else 1
            )
        , Int64.Type),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom3",{{"Period", type date}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"Index"})
in
    #"Removed Columns"

 










Did I answer your question? Mark my post as a solution!


Proud to be a Super User!









"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu or follow my page on Facebook @DAXJutsuPBI.

View solution in original post

2 REPLIES 2
Netrelemo
Helper IV
Helper IV

Clever 🙂

Thank you very much. 

danextian
Super User
Super User

Hi @Netrelemo 

Please try this (beware: the solution is not very straightforward)

danextian_0-1693217492652.png

 

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSswpyEhU0lEyMjAy1jUw0TUyVYrVQRc2xS5shl3YAiaclFqCEDXGKopdrSVMND0xNxeLEajCJtiFTbELm4OFYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Label = _t, Period = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Label", type text}, {"Period", type date}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Label"}, {{"Grouped", each _, type table [Label=nullable text, Period=nullable date]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Grouped_Index", each Table.AddIndexColumn([Grouped], "Index", 0, 1)),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom_Index2", each let 
x = [Grouped_Index],
y = x[Index]
in
Table.AddColumn(x, "Prev", each try x[Period]{[Index]-1} otherwise null)),
    #"Expanded Custom_Index2" = Table.ExpandTableColumn(#"Added Custom1", "Custom_Index2", {"Index", "Period", "Prev"}, {"Index", "Period", "Prev"}),
    #"Removed Columns1" = Table.RemoveColumns(#"Expanded Custom_Index2",{"Grouped", "Grouped_Index"}),
    #"Added Custom2" = Table.AddColumn(#"Removed Columns1", "Diff", each let x = Number.Round(Number.From(([Period]- [Prev])/( 365.25 / 12 )) ,0 ) in if x = null then 1 else x, Int64.Type),
    #"Removed Columns2" = Table.RemoveColumns(#"Added Custom2",{"Prev"}),
    #"Added to Column" = Table.TransformColumns(#"Removed Columns2", {{"Index", each _ + 1, type number}}),
    #"Added Custom3" = Table.AddColumn(#"Added to Column", "Running Count", each 
let
x = [Label], 
y = Table.SelectRows(#"Added to Column", each [Label] = x) 
in List.Accumulate(List.FirstN(y[Diff],[Index]),  0,
                (state, current) =>
                    if current = 1 then state + 1
                    else 1
            )
        , Int64.Type),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom3",{{"Period", type date}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"Index"})
in
    #"Removed Columns"

 










Did I answer your question? Mark my post as a solution!


Proud to be a Super User!









"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu or follow my page on Facebook @DAXJutsuPBI.

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.