Limited record imported based on another query

[NoobAnalyst01]

Hello Power Bi Masters:

I have to created a dashboard from two database source, one is our Warehousing database, one is from our Scanning database.

my problem is, both of those two database have too many rows ,10000+ for one day.

For warehousing, i only need my late orders, so i can easy filter my status late, so I will only get 100+ records, this is easy.

But I want to see houw our employees scan those late orders in Scanning database, so I only want to get the records for my 100+ late orders in Scanning dtabase.

What I do now is I have to import 100000+ records from my scanning database.... and then in PowerBI, I join the order ID together.

Think about I have a SQL like below; but please note scanning and warehousing are not in the same database, so I need to import them from tow resouce...

select order, scanned time

from scanning

where order in (

select order 

from warehouseing 

where status='late'

)

Thanks

Frank

[drewlewis15 · Accepted solution]

I am assuming there is a field from your Warehousing Database that you are using to join your Scanning database... If that is the case:

1. Start with your Warehousing Database query where the status is filtered to "late".
2. Create another query where Source = Database Query
3. Remove all columns except for the column that you use to join your Scanning Database
4. Make sure that column is of text value
5. Convert that query to a list
   1. Named OrderID?
6. You can now reference that list in your SQL statement against your Scanning Database
7. Write your SQL statement against your Scanning Database, and just pick a random value for your IN statement (this step is to write your M for you)
8. Go into the advanced editor and modify your Source code
9. For example:
   1. Original SQL statement = SELECT ORDER, SCANNED TIME FROM SCANNING WHERE ORDER IN (1234, 5678)
   2. This will create M code Similiar to this: 

      Source = Oracle.Database("SCANNING, [HierarchicalNavigation=true, Query="SELECT ORDER, SCANNED TIME FROM SCANNING.SCANNING WHERE ORDER IN (1234, 5678)"])

   3. Break your SQL code and replace the green text with the name of your list using the Text.Combine function

      1. Source = Oracle.Database("SCANNING, [HierarchicalNavigation=true, Query="SELECT ORDER, SCANNED TIME FROM SCANNING.SCANNING WHERE ORDER IN (" & Text.Combine(OrderID,",) & ")"])

I had this very question about a month ago, and this is the article that I used:

Dynamic SQL