Forum Discussion

Hokori's avatar
Hokori
Frequent Visitor
1 year ago
Solved

Power Query Import mode with Filter/merge

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 nam...
Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    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

  • mussaenda's avatar
    mussaenda
    1 year ago

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

     

Anonymous's avatar
Anonymous
Not applicable
1 year ago

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.