Forum Discussion
How to make Fill Down conditions?
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"
I'm here to help you!
If you already have the State-column, you simply make a new (calculated) column like this:
= IF(Table[State]=1;1;0)
Let me know if it solves your problem.
Best,
Martin
Hello Anonymous,
When i try to use that calculated column like you sugested this is what happens:
Wich basicly means in table format:
Basicly in the way you sugested i can only see when he changes to 1(online) but doesnt replicate online in the rest of the time until there is another change of state,
Once again what i pretend is something like this:
Best Regards
Guilherme3
- MarcelBeug
Community Champion
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"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
Just replace Table1 with the name of your table. I assume you have it already in Power BI/Power Query?