create flag using complex if condition in power query

[rob_vander2]

Hi All,

I have below dataset and required output in screenshot.

I would like to create Flag column in power query with below conditions
if Count is blank, all rows should be flagged as 1
if Count is not blank, I would like to Flag rows only for Ref 11,21 and 3.
for Ref 11,21 and 3, if Count is 2, Flag only Row where Type ="B" 

[Akash_Varuna · Accepted solution]

Hi @rob_vander2 ,You could achive this by custom column and if condition please try these 

1. Add a Custom Column:
   • Go to the "Add Column" tab and select "Custom Column."
   • Formula:
     if [Count] = null then 1
     else if [Count] = 1 and ([Ref] = 11 or [Ref] = 21 or [Ref] = 3) then 1
     else if [Count] = 2 and [Type] = "B" then 1
     else null
     
     

2. Filter the Rows:

   • After adding the Flag column, filter out rows where the Flag is null if you only want flagged rows in the final output.
     If this post helped please do give a kudos and accept this as a solution 
     Thanks In Advance

[SundarRaj · Accepted solution]

Hi @rob_vander2 , here's a solution you can check out. I'll attach a snippet of M code I used. Thanks

[Akash_Varuna · Accepted solution]

Hi @rob_vander2 ,You could achive this by custom column and if condition please try these 

1. Add a Custom Column:
   • Go to the "Add Column" tab and select "Custom Column."
   • Formula:
     if [Count] = null then 1
     else if [Count] = 1 and ([Ref] = 11 or [Ref] = 21 or [Ref] = 3) then 1
     else if [Count] = 2 and [Type] = "B" then 1
     else null
     
     

2. Filter the Rows:

   • After adding the Flag column, filter out rows where the Flag is null if you only want flagged rows in the final output.
     If this post helped please do give a kudos and accept this as a solution 
     Thanks In Advance