none
Table.FillDown with condition RRS feed

  • Question

  • Is there a way to fill down a table based on a condition?

    A sample of my table below. I want to filldown the Date column based on the Contract column. For example, if the contract does not starts with "GB", then filldown the Date of the previous row, otherwise leave blank.

     Contract    Date     Role
    01F001     3/7/2016     A
    01F017     5/6/2016     A
    GB0007                  B
    GB0007                  B
    LBCA09     2/29/2016    A
    LBCA09                  B
    LBCA09                  B
    LBCA09                  B
    LBCA12     2/25/2016    A
    LBCA12                  B
    

    Here is the final data I want

    Contract    Date    Role
    01F001  3/7/2016    A
    01F017  5/6/2016    A
    GB0007              B
    GB0007              B
    LBCA09  2/29/2016   A
    LBCA09  2/29/2016   B
    LBCA09  2/29/2016   B
    LBCA09  2/29/2016   B
    LBCA12  2/25/2016   A
    LBCA12  2/25/2016   B
    

    I tried to add a if statement outside of the Table.Filldown function but got an syntax error.

    = if not Text.StartsWith([Contract],"GB") then Table.FillDown(#"Replaced Value",{"Date"})
    

    Any help is appreciated!

    Monday, September 26, 2016 6:39 PM

Answers

  • Hi qshng. You can do this in multiple steps. Something like this, for example:

    1. Add a new conditional column called ConditionalDate, defined as "= if Text.StartsWith([Contract],"GB") and [Date] = null then 0 else Date" 
    2. Now you can do a Fill Down of ConditionalDate, and it should behave differently for GB rows with no Date (which will contain a zero) versus non-GB rows.
    3. Replace the dummy ConditionalDate zero values with null, and convert the column to Date.

    If I'm understanding your data correctly, another option would be to group the rows based on Contract, fill down the nested table Date values, and then expand them out again. The resulting M would look something like this in the Advanced Query Editor:

        #"Grouped Rows" = Table.Group(PreviousStepName, {"Contract"}, {{"Rows", each Table.FillDown(_, {"Date"}), type table}}),
        #"Expanded Rows" = Table.ExpandTableColumn(#"Grouped Rows", "Rows", {"Date", "Role"}, {"Date", "Role"})

    Ehren



    Monday, September 26, 2016 7:32 PM
    Owner

All replies

  • Hi qshng. You can do this in multiple steps. Something like this, for example:

    1. Add a new conditional column called ConditionalDate, defined as "= if Text.StartsWith([Contract],"GB") and [Date] = null then 0 else Date" 
    2. Now you can do a Fill Down of ConditionalDate, and it should behave differently for GB rows with no Date (which will contain a zero) versus non-GB rows.
    3. Replace the dummy ConditionalDate zero values with null, and convert the column to Date.

    If I'm understanding your data correctly, another option would be to group the rows based on Contract, fill down the nested table Date values, and then expand them out again. The resulting M would look something like this in the Advanced Query Editor:

        #"Grouped Rows" = Table.Group(PreviousStepName, {"Contract"}, {{"Rows", each Table.FillDown(_, {"Date"}), type table}}),
        #"Expanded Rows" = Table.ExpandTableColumn(#"Grouped Rows", "Rows", {"Date", "Role"}, {"Date", "Role"})

    Ehren



    Monday, September 26, 2016 7:32 PM
    Owner
  • Thank you Ehren!

    I figured out the multi-step approach. But your fill down grouped rows approach is so much more powerful! I would never have thought of it on my own. Thanks a lot!

    Tuesday, September 27, 2016 2:53 PM