Fetch date based on a field value in advance editor

[mft_87]

Dears,

I am fetching the data from SQL database using the below query

let
Source = Sql.Databases("database name"),
evo = Source{[Name="evo"]}[Data],
dbo_F_CONTRACT = evo{[Schema="dbo",Item="F_CONTRACT"]}[Data]
in
dbo_F_CONTRACT

The above query is giving me the data for all contracts, that bring in 100000 lines items, but I need to bring the data only related to the contract I am making the report for using field where CTR_PREFIX = Contract Prefix (every prefix I add here, the query should only bring the data for that specific prefix.)

F_Contract is a table, and CTR_PREFIX is the Field I need to filter

Can someone help me how to add this to the query as I am new to power bi and dax.

I would highly appreciate your help and support

Regards,

[Mariusz · Accepted solution]

Hi @mft_87 

You can filter CTR_PREFIX  column in Power Query, and as long as you are careful not to break Query Folding ( by adding unsupported functions before your filter ) the native query will include this filters in the SQL that is sent to the server, see the below for the ref.

Also please see the link to the documentation below.

https://docs.microsoft.com/en-us/power-query/power-query-folding

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

[v-juanli-msft · Accepted solution]

Hi @mft_87 

Mariusz shows how to filter data in Power Query.

You could click on the menu of the column to apply filters.

If you want to limit data which imported into Power BI, you could write sql query as below:

Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Mariusz · Accepted solution]

Hi @mft_87 

You can filter CTR_PREFIX  column in Power Query, and as long as you are careful not to break Query Folding ( by adding unsupported functions before your filter ) the native query will include this filters in the SQL that is sent to the server, see the below for the ref.

Also please see the link to the documentation below.

https://docs.microsoft.com/en-us/power-query/power-query-folding

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn