Can I query a Datasource based on an existing Dataset column?

[sabeer6870]

Hi,

I queried a data source and got the dataset, in that datasource there is column lets say, "region".

Region           |  Column2 |

US East 2       |         x       |

Asia West 1   |         y       |

Is it possible to query another data source, where "region" in "US East2" & "Asia West1"?

EDIT: The reason why I am trying to filer is that I cannot fetch the full data, its huge. That is why I am trying to filer before I

fetch it.

EDIT 2:

This is the once Dataset(Data from Datasource1):

Region           |  Cluster|

US East 2       |       C1   |

Asia          1   |       C2   | 

I want another column in this which will fetch the data from another dataset base on one other columns.

Region     |Cluster | DerivedCol

US East 2 |  C1      | (query datasource2)select * from sometable where region = [Data from Region Column] and Cluster = [Cluster] // this query should translate to =  select * from sometable where region = 'US EAST 2' and Cluster = 'C1'

Asia    1   |  C2      | (query datasource2)select * from sometable where region = [Data from Region Column] and Cluster = [Cluster] // this query should translate to =  select * from sometable where region = 'Asia 1' and Cluster = 'C2'

[mwegener]

Hi @sabeer6870 ,

hope this helps...

You can generate a filter string as in this screenshot.

The filter string can be inserted as a parameter in the query.

You have to deactivate the Formula.Firewall and grant the Native Query Permission

Then you get the filtered results table.

Regards,

Marcus

Dortmund - Germany
If I answered your question, please mark my post as solution, this will also help others.
Please give Kudos for support.

[sabeer6870]

Thanks @mwegener  but my use case much more complex, I actually changed the data while sanitization.

I tried all I could, now I am fairly confident that PowerBI Cannot do what I want.

Reiterating : 

1. query SQL get some data

2. Use some columns from #1 to query another data source.

#1 Creates some regex that I want to use in #2. Anyway, I know why PowerBI prohibits that, because when you refresh your data all the query goes on parallel, hence cannot have dependency between two PowerBI queries.

I appreciate your responses, thanks again @mwegener 

[mwegener]

Hi @sabeer6870 ,

is your problem solved?
If I answered your question, please mark my post as solution, this will also help others.
Please give Kudos for support.

[mwegener]

Hi @sabeer6870,

parallel loading can be deactivated.

If you have two interdependent queries, they will be processed accordingly.

Take a look on this.

https://www.thebiccountant.com/speedperformance-aspects/

Regards,

Marcus

Dortmund - Germany
If I answered your question, please mark my post as solution, this will also help others.
Please give Kudos for support.

[v-diye-msft]

Hi @sabeer6870 

You might refer to the official document:

https://docs.microsoft.com/en-us/power-bi/desktop-shape-and-combine-data 

Combine queries:

https://docs.microsoft.com/en-us/power-bi/desktop-shape-and-combine-data#combine-queries 

[sabeer6870]

Thanks for the reply @v-diye-msft .

I am trying to query datasource but on the basis of another data set.

Suppose I am querying two dataset:

let
Source = Sql.Database("DatabaseEnpoint", "AnotherDBName", [Query="select RegionName, ID, Value from TableXY", MultiSubnetFailover=true]),
in
Source

Another Query:

let
Source = Sql.Database("DatabaseEnpoint", "DBName", [Query="MyQuery", MultiSubnetFailover=true]),
in
Source

Can I use in Myquery = "select * from Table where col1 in (RegionName From First Source(c.f. above))" ???

[mwegener]

Hi @sabeer6870 ,

do not use a SQL query, let Power Query create the query with query folding.

Use "Merge" to combine the two datasources with a inner join.

Power Query should create an appropriate query for data loading.

Regards,

Marcus

Dortmund - Germany
If I answered your question, please mark my post as solution, this will also help others.
Please give Kudos for support.

[sabeer6870]

Thanks for the reply @mwegener .

I see you are merging two queries from existing two datasets but the issue is one of the dataset is too big that I cannot fetch it, hence merge won't work.

I am going to update the question with more accurate example please have a look. TIA.

[mwegener]

Hi @sabeer6870 ,

did you test my suggestion?

What error message do you get?

Regards,

Marcus

Dortmund - Germany
If I answered your question, please mark my post as solution, this will also help others.
Please give Kudos for support.

[sabeer6870]

I cannot because i just have one data, the other one is too large to fetch.

[mwegener]

But the Power Query Editor only loads the first 1000 rows into the preview.

How can it be too large for that?

[sabeer6870]

Even then it wouldn't be efficient, i have millions of rows in the table and out of those I just want 2-3k records.

[mwegener]

Hi @sabeer6870,

I am disappointed that you do not believe in Query Folding and have not even tried it.

[mwegener]

Hi @sabeer6870 ,

use "Merge Queries" with a "Inner" join.

Regards,

Marcus

Dortmund - Germany
If I answered your question, please mark my post as solution, this will also help others.
Please give Kudos for support.

[sabeer6870]

Can you please provide me an example? @mwegener