SOQL filter in Power Query

[JasPadan]

Hi all,

I have a query which I am struggling on. We currently use SOQL to pull in data from salesforce in sheets and I am trying to replicate the below into PowerQuery 

WHERE isdeleted = FALSE and createddate >= THIS_FISCAL_YEAR and status = 'Completed' and ownerid <> '00558000003jg6TAAQ' and createdbyid <> '00558000003jg6TAAQ' and whoid <> '' and SalesLoft1__SalesLoft_Type__c <> 'Reply' and SalesLoft1__SalesLoft_Type__c <>
'Hot Lead' and SalesLoft1__SalesLoft_Type__c <> 'Note' and SalesLoft1__SalesLoft_Type__c <> 'Other' and SalesLoft1__SalesLoft_Type__c <> 'LinkedIn - Research' and ((not (SalesLoft1__SalesLoft_Type__c = '' and Tasksubtype = 'Task' and Type = '')) or (subject like '%Sales Navigator%'))

Now when I am in query I can get do the following:

= Table.SelectRows(Custom1, each ([IsDeleted] = false) and ([Status] = "Completed") and ([OwnerId] <> "00558000003jg6TAAQ") and ([CreatedById] <> "00558000003jg6TAAQ") and ([WhoId] <> null) and ([SalesLoft1__SalesLoft_Type__c] <> "Hot Lead" and [SalesLoft1__SalesLoft_Type__c] <> "LinkedIn - Research" and [SalesLoft1__SalesLoft_Type__c] <> "Note" and [SalesLoft1__SalesLoft_Type__c] <> "Other" and [SalesLoft1__SalesLoft_Type__c] <> "Reply"))

Where I am struggling is the last part of the SOQL which is 

((not (SalesLoft1__SalesLoft_Type__c = '' and Tasksubtype = 'Task' and Type = '')) or (subject like '%Sales Navigator%'))

Does anyone have any ideas on how to deal with this or should I be creating a measure for this part rather than run this in Query

[JasPadan]

Thank you. Really appreciate you looking into this for me as was a real struggle so glad you were able to assist me with this.

[JasPadan]

Hi Pete, thank you I will test this out now as this is superhelpful and thank you for this.
In terms of adding the OR which I completely forgot 

or ( Text.Contains([Subject], "Sales Navigator") )

Would I just insert another ( before Not or should I just add this after 

       (
            [SalesLoft1__SalesLoft_Type__c] = ""
            and [Tasksubtype] = "Task"
            and [Type] = ""
        )

[BA_Pete]

Hi @JasPadan ,

The 'not' operator should work the same way in Power Query:

not (SalesLoft1__SalesLoft_Type__c = "" and Tasksubtype = "Task" and Type = "")

The 'like' operator can be replaced with Text.Contains in this instance:

or ( Text.Contains([Subject], "Sales Navigator") )

Pete

[JasPadan]

@BA_Pete - Thanks for the above. Maybe I am doing this incorrectly as I tried this before and it still didn't work. I think it could be to with where I am possibly inseting the (( brackets as I have tried this 

= Table.SelectRows(Custom1, each ([IsDeleted] = false) and ([Status] = "Completed") and ([OwnerId] <> "00558000003jg6TAAQ") and ([CreatedById] <> "00558000003jg6TAAQ") and ([WhoId] <> null) and ([SalesLoft1__SalesLoft_Type__c] <> "Hot Lead" and [SalesLoft1__SalesLoft_Type__c] <> "LinkedIn - Research" and [SalesLoft1__SalesLoft_Type__c] <> "Note" and [SalesLoft1__SalesLoft_Type__c] <> "Other" and [SalesLoft1__SalesLoft_Type__c] <> "Reply") and ((not ([SalesLoft1__SalesLoft_Type__c]) = "" and ([Tasksubtype] = "Task") and ([Type] = "")) but I am still not having any luck

[BA_Pete]

I've just formatted your code and found there were lots of superfluous brackets.

Have a look at it now and see if this is doing what you want:

Table.SelectRows(
    Custom1,
    each [IsDeleted] = false
    and [Status] = "Completed"
    and [OwnerId] <> "00558000003jg6TAAQ"
    and [CreatedById] <> "00558000003jg6TAAQ"
    and [WhoId] <> null
    and
    (
        [SalesLoft1__SalesLoft_Type__c] <> "Hot Lead"
        and [SalesLoft1__SalesLoft_Type__c] <> "LinkedIn - Research"
        and [SalesLoft1__SalesLoft_Type__c] <> "Note"
        and [SalesLoft1__SalesLoft_Type__c] <> "Other"
        and [SalesLoft1__SalesLoft_Type__c] <> "Reply"
    )
    and
    (
        not
        (
            [SalesLoft1__SalesLoft_Type__c] = ""
            and [Tasksubtype] = "Task"
            and [Type] = ""
        )
    )
)

Pete

[JasPadan]

Thanks Pete. I will check this now but I forgot to add for the or statement as well would I just use the ( after and type = " " ) and then say ( or, or should I close of the brackets after no as I forgot to add this in.

[BA_Pete · Accepted solution]

Okay, so here's your entire WHERE clause translated to M code:

Table.SelectRows(
    Custom1,
    each [IsDeleted] = false
    and [Status] = "Completed"
    and [OwnerId] <> "00558000003jg6TAAQ"
    and [CreatedById] <> "00558000003jg6TAAQ"
    and [WhoId] <> null
    and [SalesLoft1__SalesLoft_Type__c] <> "Hot Lead"
    and [SalesLoft1__SalesLoft_Type__c] <> "LinkedIn - Research"
    and [SalesLoft1__SalesLoft_Type__c] <> "Note"
    and [SalesLoft1__SalesLoft_Type__c] <> "Other"
    and [SalesLoft1__SalesLoft_Type__c] <> "Reply"
    and
    (
        (
            not
            (
                [SalesLoft1__SalesLoft_Type__c] = ""
                and [Tasksubtype] = "Task"
                and [Type] = ""
            )
        )
        or
        Text.Contains([subject], "Sales Navigator")
    )
)

Here's the original WHERE clause formatted, so you can see more clearly where the brackets etc. are and compare to the M version:

WHERE
isdeleted = FALSE
and createddate >= THIS_FISCAL_YEAR
and status = 'Completed'
and ownerid <> '00558000003jg6TAAQ'
and createdbyid <> '00558000003jg6TAAQ'
and whoid <> ''
and SalesLoft1__SalesLoft_Type__c <> 'Reply'
and SalesLoft1__SalesLoft_Type__c <> 'Hot Lead'
and SalesLoft1__SalesLoft_Type__c <> 'Note'
and SalesLoft1__SalesLoft_Type__c <> 'Other'
and SalesLoft1__SalesLoft_Type__c <> 'LinkedIn - Research'
and
(
    (
        not
        (
            SalesLoft1__SalesLoft_Type__c = ''
            and Tasksubtype = 'Task'
            and Type = ''
        )
    )
    or
    subject like '%Sales Navigator%'
)

Pete