Forum Discussion

JasPadan's avatar
JasPadan
New Member
3 years ago
Solved

SOQL filter in Power Query

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 = FALS...
Need Help
Tips and Tricks
  • BA_Pete's avatar
    BA_Pete
    3 years ago

     

    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

BA_Pete's avatar
BA_Pete
Icon for Super User rankSuper User
3 years ago

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's avatar
JasPadan
New Member
3 years ago

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's avatar
    BA_Pete
    Icon for Super User rankSuper User
    3 years ago

     

    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's avatar
      JasPadan
      New Member
      3 years ago

      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's avatar
        BA_Pete
        Icon for Super User rankSuper User
        3 years ago

         

        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