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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors
Top Kudoed Authors