Forum Discussion

arkiboys2's avatar
arkiboys2
Helper IV
1 year ago
Solved

dataflow filter

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

Data Engineering
  • frithjof_v's avatar
    frithjof_v
    1 year ago

    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?

2 Replies

Replies have been turned off for this discussion
  • frithjof_v's avatar
    frithjof_v
    Community Champion
    1 year ago

    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's avatar
    frithjof_v
    Community Champion
    1 year ago

    You need to manually edit the M code.

     

    Advanced Editor is useful for this.