Forum Discussion
Solved
How to create Fill Down with additional index
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 |
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"
1 Reply
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"