Forum Discussion
Guilherme3
9 years agoRegular Visitor
How to make Fill Down conditions?
Hello All, I have a group of data referring to systems that changes state: online(1) and offline(0) in certain dates/time. Like the example below: I would like to fill the gaps w...
- 9 years ago
In the solution below, the table is grouped on System, initially with option all rows.
Next I created a function AddFirstValueAndFillDown to perform the required operations and adjusted the previously generated code so the function is used for each subtable. I also changed type table in Value.Type(Source) to keep the data types from the Source columns.
let AddFirstValueAndFillDown = (Table as table) as table => let #"Added Index" = Table.AddIndexColumn(Table, "Index", 0, 1), #"Added Custom" = Table.AddColumn(#"Added Index", "Custom", each if [Index] = 0 and [State] = null then 1 - List.First(List.Select(Table[State],each _ <> null)) else [State], Int64.Type), #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"State", "Index"}), #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Custom", "State"}}), #"Filled Down" = Table.FillDown(#"Renamed Columns",{"State"}) in #"Filled Down", Source = Table1, #"Grouped Rows" = Table.Group(Source, {"System"}, {{"AllData", AddFirstValueAndFillDown, Value.Type(Source)}}), #"Expanded AllData" = Table.ExpandTableColumn(#"Grouped Rows", "AllData", {"Date", "State"}, {"Date", "State"}) in #"Expanded AllData"
Guilherme3
9 years agoRegular Visitor
Hello MarcelBeug
Sorry i try your code but when i invoke my table it gives this error:
Expression.Error: The import Table1 matches no exports. Did you miss a module reference?
Can you please explain? :/
Best Regards
Guilherme3
MarcelBeug
Community Champion
9 years agoJust replace Table1 with the name of your table. I assume you have it already in Power BI/Power Query?