How do I create a variable modulo value?

[Anonymous]

I am trying to achieve a result similar to the modulo function, however the number of rows between "zeros" can vary.

The data I have is the TYPE column in the table below

The result I am trying to get, is the red MODULO column, where each row containing the TYPE 'Document_Header' will have a value of 0, and each row containing the TYPE 'Document_Detail' will have a sequential number which resets after each 0

TYPE	MODULO
Document_Header	0
Document_Detail	1
Document_Detail	2
Document_Detail	3
Document_Header	0
Document_Detail	1
Document_Header	0
Document_Detail	1
Document_Detail	2
Document_Detail	3
Document_Detail	4
Document_Header	0
Document_Detail	1
Document_Detail	2

Thanks in advance for anybody that can point me in the right direction

[ImkeF · Accepted solution]

Hi @Anonymous ,

please paste this code into the advanced editor and follow the steps:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCokMcFXSUfL1dwn18VeK1YlWcslPLs1NzSuJ90hNTEktAkoaoIq7pJYkZuYAxQ1xiBvhEDemzHxauwcubkI9e2MB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"(blank)" = _t, #"(blank).1" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"(blank)", type text}, {"(blank).1", type text}}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"TYPE", type text}, {"MODULO", Int64.Type}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type1", "Index", 0, 1, Int64.Type),
    AddModIndex = Table.AddColumn(#"Added Index", "ModIndex", each if [TYPE] = "Document_Header" then [Index] else null),
    FillDownModIndex = Table.FillDown(AddModIndex,{"ModIndex"}),
    AddDynamicModulo = Table.AddColumn(FillDownModIndex, "DynamicModulo", each [Index] - [ModIndex], type number)
in
    AddDynamicModulo

What it does is to

1. Add an index column
2. Check if the index belongs to a header row and if so, take that index
3. Fill down header indices down to detail rows
4. Retrieve dynamic moduly by subtracting header index from running index
5.