Power Query Import mode with Filter/merge

[Hokori]

Hi All,

I need to import a table into PQ. The table has 40 mln rows. To maintain the integrity with other tables I need only 50k rows to be imported. 

I have 3 tables. let's say A contains city name with all the cities in the world and I need just London and Bristol, B contains tennant name with all the names in the world and I need people named John, C is all the combinations of cities and tennants and prices (40 mln). For first 2- I import tables with only necessary data (the real lists are of course longer and there're multiple conditions applied:). For C I want to retrive the data for John's in Bristol & London.

It works perfectly when I apply the filtering in SQL using where clause and then import the fitlered tbl. However I'd like to achive the same filtering in PQ without crashing the system.

When I import the entire tbl and attempt to filter rows by merging C tbl with A&B - the PQ freezes. 

When I import the entire tbl and try to filter rows by lists - it freezes as well.

Any ideas how to filter rows in PQ having the similar efficiency as SQL filtering?

[Anonymous · Accepted solution]

Yes, do it in Power Query. Use buffered lists for your search values. If you already have the other two tables filtered, use:

= Table.SelectRows(PriorStepOrTableName, each List.Contains(List.Buffer(List.Distinct(Table2[Names])), [ThisTable_NameColumn]) and List.Contains(List.Buffer(List.Distinct(Table1[Cities])), [ThisTable_CitiesColumn]))

--Nate

[Anonymous]

Hi @Hokori ,

Agree with mussaenda .

On top of that, if you don't want to import too much data, you can also try the DirectQuery connection mode, which is much faster.

DirectQuery in Power BI offers the greatest benefits in the following scenarios:

• The data changes frequently, and you need near real-time reporting.
• You need to handle large data without having to pre-aggregate.
• The underlying source defines and applies security rules.
• Data sovereignty restrictions apply.
• The source is a multidimensional source containing measures, such as SAP BW.

For more details about DirectQuery, please refer to

DirectQuery in Power BI - Power BI | Microsoft Learn

Best Regards,

Stephen Tao

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

[Anonymous]

If you use my method, Power Query will run your query as native SQL, with a

WHERE Name IN ('John') and City IN ('Bristol', 'London')

clause containing the contents of your buffered lists.  It will be nice and fast.

--Nate

[mussaenda · Accepted solution]

have you tried to do the SQL Statement? You can do the SQL query before importing the table

[Anonymous · Accepted solution]

Yes, do it in Power Query. Use buffered lists for your search values. If you already have the other two tables filtered, use:

= Table.SelectRows(PriorStepOrTableName, each List.Contains(List.Buffer(List.Distinct(Table2[Names])), [ThisTable_NameColumn]) and List.Contains(List.Buffer(List.Distinct(Table1[Cities])), [ThisTable_CitiesColumn]))

--Nate

[lbendlin]

Don't do it in Power Query?  Let the Vertipaq engine do it for you in the data model in memory.

[Hokori]

Can you elaborate?