Query ODBC data on import

[Jeffrey Green]

I create a table in a PP Model and I call it ACE.
This table might have 30,000 rows.
I need to connect to the BI ODBC tables (which i can do) and i know how to filter that data, mostly.
What i want to do is only bring in the BI data for the Rows i have in the ACE table, using a Left JOIN, so i can see the missing data.

how can i do that? thanks .

 

[Matt Allington]

Are both your source tables in the odbc db? If so, just write a SQL query to bring in what you need. If not, you can bring big tables into power query and then do the join there.

 

[Jeffrey Green]

No, they are not in the same ODBC. The "ACE" table comes from a report from an entirely different system and is then uploaded via Excel to the seta model. I need to make sure that every record on the ACE report is loaded in our ODBC table by our import department.
So, as I call it, my "LEFT" Table is a spreadsheet that has been loaded into a data model.
thanks

 

[Matt Allington]

So refer to the bit "if not..." in my prior post

 

[Jeffrey Green]

I need help with the SQL . . . my "ACE" table is not showing up as a table i can join on.

 

[Matt Allington]

I need help with the SQL . . . my "ACE" table is not showing up as a table i can join on.

Do you mean inside power query as I suggested? Load it into power query and then save the query so it creates a connection and doesn't load the table. Then create another query for your other table, and use the merge feature of power query to make the join.

 

[Jeffrey Green]

Do you mean inside power query as I suggested? Load it into power query and then save the query so it creates a connection and doesn't load the table. Then create another query for your other table, and use the merge feature of power query to make the join.

good tip, i will try that/ thanks

 

[Jeffrey Green]

Ok, i got PQ to work, almost. I am connecting to an ODBC table with 4 million rows. When i connect to it and choose my columns I put a filter on the date column to only pull in the last 90 days. But whether i load to connection or load to data model, Excel wants to go pull all 4 million rows and takes forever. How do i get PQ to just load the last 90 days' worth of data?
Thanks.

 

[Matt Allington]

Are you using the UI? Power Query uses query folding to send a query to the source system. I think odbc is supported. But you must use the UI for every step. If you write a query manually, that query will be sent, but no query folding after that. It is a very complex topic and I am not an expert.