CODEWITHSUNDEEP
sql-serveroracle-databasesubquerypowerbipowerquery
user2659035
user2659035

Reputation: 109

How to subquery using M in Power Query/Power BI

So I have two queries that I'm working on, one comes from an Oracle DB and the other SQL Server DB. I'm trying to use PowerBI via Power Query as the cross over between the two. Because of the size of the Oracle DB I'm having a problem with running it, so my thought is to use one query as a clause/sub-query of the other to limit the number of results.

Based on the logic of MSFT's M language I'd assume there's a way to do sub-queries of another but I've yet to figure it out. Does anyone have any ideas on how to do this?

Upvotes: 0

Views: 2638

Answers (2)

user8107418
user8107418

Reputation: 11

What I have learned to do is create a connection to the two under lying data sets, but do not load them. Then you can merge them, but do not use the default Table.NestedJoin().

After the PQ generates this, change it to:

= Table.Join(dbo_SCADocument,{"VisitID"},VISIT_KEY,{"VisitID"}) .

Also remove the trailing name. The reason is , it keeps query folding alive. For some reason, Table.NestedJoin() kills query folding. Note, if there are similar fields in the two sources other than the join it will fail.

Also it brings everything from both sources, but that is easy to alter . Also you will need to turn off the function firewall as this does not allow you to join potential sensitive data with non sensitive data. This is setting your privacy level to ignore all.

Upvotes: 1

Mike Honey
Mike Honey

Reputation: 15027

I would attempt this using the Merge command. I'm more of a UI guy, so I would click the Merge button. This generates a PQL statement for Table.Join

https://msdn.microsoft.com/en-us/library/mt260788.aspx

Setting Join Kind to Inner will restrict the output to matching rows.

I say attempt as your proposed query design will likely slow your query, not improve it. I would expect PQ to run both queries against the 2 servers and download all their data, then attempt the Join in Memory.

Upvotes: 0

Related Questions