Join us for an expert-led overview of the tools and concepts you'll need to pass exam PL-300. The first session starts on June 11th. See you there!
Get registeredPower BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.
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
Solved! Go to 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
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
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
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
16 | |
10 | |
10 | |
8 | |
7 |