Forum Discussion

M_SBS_6's avatar
M_SBS_6
Helper V
2 years ago
Solved

Flag to exclude data in table

Hi, 

I have a table which has the following values:

 

App.ID

Call.LastCall

Meeting.Lastmeet

App.LastCash

App.Cash_val

 

In some instances, we have a blank record for all 3 of the following columns.

Call.LastCall (latest function)

Meeting.Lastmeet (latest function)

App.LastCash (latest function)

 

Given that these columns of data come from different tables, I'm not sure how to remove these rows of data where they're all blank values. 

 

Any suggestions as to how I could do this please? 

 

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi all,thanks for the quick reply, I'll add more.

    Hi M_SBS_6 ,

    Regarding your question, please first create a custom column in Power Query.

    if [Call.LastCall] = null and
[Meeting.Lastmeet] = null and 
[App.LastCash] = null 
then 1 else 0

    Then filter by custom column

    M code

        #"Added Custom" = Table.AddColumn(#"Expanded Table3", "Custom", each if [Call.LastCall] = null and
    [Meeting.Lastmeet] = null and 
    [App.LastCash] = null 
    then 1 else 0),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom", each [Custom] <> 1)
in
    #"Filtered Rows"

    Final output

     

    Best Regards,
    Wenbin Zhou

3 Replies

  • AbhinavJoshi's avatar
    AbhinavJoshi
    Responsive Resident
    2 years ago

    Hello M_SBS_6 ,

     

    You can create a custom column to set a value when you meet all three conditions. Here's an example 
    ( [#"Call.LastCall "] = null) and ( [#"Meeting.Lastmeet "] = null ) and (App.LastCash = null) then 0 else 1

     

    Then you can filter out the values with 0.

     

    Hope this helps. 

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi all,thanks for the quick reply, I'll add more.

    Hi M_SBS_6 ,

    Regarding your question, please first create a custom column in Power Query.

    if [Call.LastCall] = null and
[Meeting.Lastmeet] = null and 
[App.LastCash] = null 
then 1 else 0

    Then filter by custom column

    M code

        #"Added Custom" = Table.AddColumn(#"Expanded Table3", "Custom", each if [Call.LastCall] = null and
    [Meeting.Lastmeet] = null and 
    [App.LastCash] = null 
    then 1 else 0),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom", each [Custom] <> 1)
in
    #"Filtered Rows"

    Final output

     

    Best Regards,
    Wenbin Zhou