Answered

Transform data using Power Query - Custom filter

(0) Share

Report

Posted on by Chris22

Hello,

I have a large data set in our SQL server. I table that shows all quotes both live and ones that have been release. I'm working on a flow that sends emails to clients that released a quote with a link to a survey on the day they release the quote. I've got everything square apart from the senario where a client releases a quote but also has a live quote. I don't want them getting the survey if they have other quotes that are live.

In the below example

Client 123 would not get an email unless they released the last quote.

Client 199 & 200 would

Release Date	Status	Client Num
11/06/2021	Released	123
	Pending	123
11/06/2021	Released	199
10/06/2021	Released	200

In excel I would create a helper column with a countifs formula and filter accordingly. How can I accomplish the same in power query?

Thanks

Categories:

Building flows

I have the same question (0)

All responses (4)

Answers (2)

Verified answer

v-bofeng-msft on at

Like (1)

Report
Hi @Chris22 :

I suggest you create a view in your database and then call the view in Power Query. I've made a test for your reference:

1\My Database

2\Create a View

create view Chris22View as select * from dbo.Chris22Test Where not ClientNum in ( Select ClientNum from dbo.Chris22Test Where TheStatus = 'Pending' )

3\Call the view in Power Query

Best Regards,

Bof

Was this reply helpful? Yes No
Chris22 39 on at

Like (0)

Report

Hi @v-bofeng-msft ,

Thanks for this. This will definitly work but unfortunatly I don't have access to create the views and the person that does has a rather large amount on the backlog and as this is a one off view I was wondering if it was somthing I could do in the power query.

Thanks for your help, really aprecieted.
Chris

Was this reply helpful? Yes No
Verified answer

v-bofeng-msft on at

Like (0)

Report
Hi @Chris22 :

Sorry,I'm not formula with M language ,I spent some time to learn it:

1\My Data source

2\Navigate to "Advanced editor"

3\Modify the code

let Source = Sql.Database("XXXX", "XXXX"), Navigation = Source{[Schema = "dbo", Item = "Chris22Test"]}[Data], SelectPendingTask = Table.Column(Table.SelectRows(Navigation, each Text.Contains([TheStatus], "Pending")),"ClientNum"), SelectNotPendingTask = Table.SelectRows(Navigation,each not List.ContainsAny(SelectPendingTask,{[ClientNum]})) in SelectNotPendingTask

The Result:

Best Regards,

BOf

Was this reply helpful? Yes No
Chris22 39 on at

Like (0)

Report

Hi BOf,
Apologies, I completly forgot to respond to your message. This is exactly what I needed, thank you some much for your help. It was really apreciated.
All the best
Chris

Was this reply helpful? Yes No