Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors