How to make Fill Down conditions?

[Guilherme3]

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 with the last value it found assuming there is no change in state. For example if the System A changes to 0 (offline) in the next row if there is no changes the value will replicate to 0. Basicly like a Fill Down with the exception i dont want to fill down values from one system to another. 

1) Is there a way to fill down by the same table while filtering for each system in order that they dont mix?

2) Assuming the first state is at 00:20 like the system A is it possible to fill the gaps before with the contrary number? (like if it goes offline at that time it means before it was online.

Image below is what i would like to show  (Green cells are the changes of state: 0 - offline, 1 - online):

Thanks you!

[Anonymous]

@Guilherme3

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

[Guilherme3]

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 · Accepted solution]

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]

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?