Filter SQL Data from Local table

[Aggro]

SQL data has 200M rows, and it's impossible to load them all in PowerBI

I usually have 1M~2M orders that need to search in a local table. Is there a way to apply the filter from my local table in SQL based on the order_id.

Below is my SQL Statement but have an error message "DataSource.Error: ODBC: ERROR [42P01] [Amazon][Amazon Redshift] (30) Error occurred while trying to execute a query: [SQLState 42P01] ERROR: relation "local_table" does not exist" 

SELECT d.order_id,d.order_status
FROM SQL_Order  as d
JOIN "local_table" as t
ON d.order_id=t.order_id

[v-jingzhang · Accepted solution]

Hi @Aggro 

What is the data source of the "local_table"? If you want to use the SQL statement in your original post, the "local_table" should also be a table in the same SQL database. Otherwise you will have the "local_table" does not exist error. 

If the "local_table" is from other data source, you can try the Merge Queries feature: How To Merge Queries In Power BI | Enterprise DNA

When you connect to the SQL table, select Transform data to open Power Query Editor. At this time, Power Query Editor only displays the data preview from data sources and the data hasn't been imported into Power BI. You can select the "local_table" query, merge SQL table to it. In "Merge" window, select order_id column as matching column. 

After the above merge operation, you will have a new table column in "local_table". Click Expand icon and only select order_id and order_status columns to expand. This will only bring values that can be matched into the "local_table". You can also only select order_status to expand. 

After that, right click on the SQL table query in Queries pane and uncheck "Enable load" option. This will not load the SQL table into your model. 

Then you can apply the change to load only the local_table into your model. 

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.