Filtering before import of data

[TANTR]

Hi,

I have a on-premise Hive table that has close to 100M records, out of which ~10,000 belongs to a certain category. I would like to import only these 10,000 records using Power BI Desktop. I have tried indicating the optional sql statement e.g. select * from hive_table where category = 'ABC' at connection. However, it fails to pick up any records. I have also tried to filter the records after import, but it seems that only 20M of the records are imported hence it's not the full base. Does anyone have any other suggestions?

[v-alq-msft]

Hi, @TANTR 

You may try the following steps.

Here is my example.

Plan:

You may create a filter which gets the record with 'Days' = 1.

Then you may right-click the 'Filter Rows' and click 'View Native Query'.

You can copy the code and create a new query to get data from the same data source, and then go to 'Advanced options' and paste the code to sql statement. 

Finally you may get a table filtered by 'Days' = 1.

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Syndicate_Admin]

Thank you very much Allan, I had the same concern and I was very useful in your practical way of explaining it to reach the solution.

Best regards
Fabio

[Greg_Deckler]

You could check the Issues forum here:

https://community.powerbi.com/t5/Issues/idb-p/Issues

And if it is not there, then you could post it.

If you have Pro account you could try to open a support ticket. If you have a Pro account it is free. Go to https://support.powerbi.com. Scroll down and click "CREATE SUPPORT TICKET".