Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare 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
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
67 | |
61 | |
23 | |
17 | |
13 |