How to create Fill Down with additional index

[rikofebriyan]

can someone help me with my problem...  i want to fill down my zero values become additional +1 like index until it reach value of 1 and repeat

for example i have a table like this

 OK

1
0
0
0
0
1
0
0
0
0
0
1
0
0
0
1
0
0
0
0
1
0
0
0
0
0
0
0
0
0
0
0
1
0
0
0
0
0
0
0
0
0
0
0

what i expecting is become like this

OK

OK

1
2
3
4
5
1
2
3
4
5
6
1
2
3
4
1
2
3
4
5
1
2
3
4
5
6
7
8
9
1

[MFelix · Accepted solution]

Hi @rikofebriyan ,

For this you need to do some advance steps on the Power Query:

• Add an index column to your model

• Add a custom column with the following code:

if[#" OK"] = 1  then [#" OK"]* 100* [Index] else null

• Do a fill down on the custom column:

• Do a group by the custom column with the aggregation of all the All rows:

• Add a new column with the following code:

Table.AddIndexColumn ( [RowCount], "RowNumber", 1 , 1)

• Expand the new column values:

• Delete all the columns you don't need:

Full code below:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlSK1YlWMsBB4pfFr4Zyk4khqWWOglJsLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#" OK" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{" OK", Int64.Type}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1, Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Added Index", "Custom", each if[#" OK"] = 1  then [#" OK"]* 100* [Index] else null),
    #"Filled Down" = Table.FillDown(#"Added Custom",{"Custom"}),
    #"Grouped Rows" = Table.Group(#"Filled Down", {"Custom"}, {{"RowCount", each _, type table [#" OK"=nullable number, Index=number, Custom=number]}}),
    #"Added Custom1" = Table.AddColumn(#"Grouped Rows", "Custom.1", each Table.AddIndexColumn ( [RowCount], "RowNumber", 1 , 1)),
    #"Expanded Custom.1" = Table.ExpandTableColumn(#"Added Custom1", "Custom.1", {" OK", "Index", "RowNumber"}, {"OK", "Index", "RowNumber"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Custom.1",{"Custom", "RowCount", "Index"})
in
    #"Removed Columns"