Forum Discussion
Microsoft EmployeeCan I query a Datasource based on an existing Dataset column?
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'
15 Replies
- mwegener
Most Valuable Professional
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.
sabeer6870Can you please provide me an example? mwegener
- 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,
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
Community Support
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
SourceAnother Query:
let
Source = Sql.Database("DatabaseEnpoint", "DBName", [Query="MyQuery", MultiSubnetFailover=true]),
in
SourceCan 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.
