dataflow filter

[arkiboys2]

in dataflow of fabric datafactory I am transforming data...

one of the columns, state, at present only has 'completed' and 'planned'

if in future there is also 'deleted' state, then I want to make sure no row is ingested for:

state = 'deleted' or state = 'planned'

how do I do this?

at present in th edrop down of the state column I can only un-check 'planned' but what about 'deleted' state which is not present currently?

I want to safe-gaurd for 'deleted' state if it comes in future.

thank you

[frithjof_v · Accepted solution]

This method only keeps the "completed" state:

let
  Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WysgvKk5VAAElHSUQ5eFfFOwKZCfn5xbkpJakpijF6kQrJSeWwBQBmcVAqiAnMS8PKuuSnw7V7eLvrgBTiKzC2TEEKAIhUU328A8C2we2Gsgsxqo/LSe/PLUIKOjm4x+eWoSpJhYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [column2 = _t, column7 = _t, state = _t]),
  #"Filtered rows" = Table.SelectRows(Source, each ([state] = "completed"))
in
  #"Filtered rows"

This method explicitly excludes the "planned" and "deleted" states:

let
  Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WysgvKk5VAAElHSUQ5eFfFOwKZCfn5xbkpJakpijF6kQrJSeWwBQBmcVAqiAnMS8PKuuSnw7V7eLvrgBTiKzC2TEEKAIhUU328A8C2we2Gsgsxqo/LSe/PLUIKOjm4x+eWoSpJhYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [column2 = _t, column7 = _t, state = _t]),
  #"Filtered rows" = Table.SelectRows(Source, each ([state] <> "planned" and [state] <> "deleted"))
in
  #"Filtered rows"

Which filtering logic applies best in your case?

[frithjof_v]

You need to manually edit the M code.

Advanced Editor is useful for this.