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

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