CODEWITHSUNDEEP
excelpowerbipowerquery
qshng
qshng

Reputation: 887

Power Query Table.FillDown with condition

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!

Upvotes: 2

Views: 5146

Answers (2)

alazyworkaholic
alazyworkaholic

Reputation: 587

You could use Table.Partition. Modification of this lends itself to more complicated situations.

MyFunc = (_) => if Text.StartsWith(_, "GB") then 1 else 0,
Partitioned = Table.Partition(YourTable, "Contract", 2, each MyFunc(_)),
DownFilled = Table.FillDown(Partitioned{0}, {"Date"}),
Result = Table.Combine({DownFilled, Partitioned{1}})

Upvotes: 2

Matt B
Matt B

Reputation: 56

You can accomplish this in multiple steps.

  • Fill down "Date" column
  • Add conditional column based on not starting with "GB" and grab the value from "Date"
  • Remove "Date" Column
  • Rename "Custom" Column To "Date"

Upvotes: 4

Related Questions