cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
JasPadan
New Member

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 = 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

1 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



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




View solution in original post

7 REPLIES 7
JasPadan
New Member

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
New Member

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
Super User
Super User

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



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




@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

 

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



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




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.

 

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



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

BizApps LATAM 2023

Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

Power Platform Bootcamp

Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.

Top Solution Authors
Top Kudoed Authors