Fetch Data based from a table where we can define that get data if field value = contract name

[Anonymous]

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,

[Icey]

Hi @Anonymous ,

Is this problem solved?

If it is solved, please always accept the replies making sense as solution to your question so that people who may have the same question can get the solution directly.

If not, please let me know.

Best Regards,
Icey

[Anonymous · Accepted solution]

Hi Icey, sorry for not replying earliear, it did not solve my problem, i did it through filtering

[sturlaws]

Hi @Anonymous 

You can alter your connection in the advanced editor:

Sql.Database("database name", "DataWarehouse", [Query="SELECT * FROM [DataWarehouse].[dbo].[F_CONTRACT] where CTR_PREFIX = 'yourPrefix'"]),
    dbo = Source{[Schema="dbo",Item="Snapshots"]}[Data]

Or, perhaps easier, double click on the source step in power query, click on 'advanced options' and type/copy-paste you sql query:

SELECT * FROM [DataWarehouse].[dbo].[F_CONTRACT] where CTR_PREFIX = 'yourPrefix'

Cheers,
Sturla

If this post helps, then please consider Accepting it as the solution. Kudos are nice too.