Forum Discussion

Guilherme3's avatar
Guilherme3
Regular Visitor
9 years ago
Solved

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...
  • MarcelBeug's avatar
    MarcelBeug
    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"