Which is more efficient filter vs criteria: power query or excel formula?

[kcmuppet]

If I have a lot of data that I am going to filter with a power query against some criteria, and am wondering which is the most efficient method for Excel to work:, when some of the criteria refer to variables / parameters.

For example if one criterion is that a date is after a certain date, referenced LowerDate I could:

a) create a power query parameter LowerDateParameterValue from the LowerDate value cell, and then filter the data against those parameters, using something like:

  #"Filtered Rows" = Table.SelectRows(Source, each ([Date] >= LowerDateParameterValue )),

b) add a helper column [Date is on after LowerDate] to my source data with something like:

=[@Date]>=LowerDate

and then in my query could filter with something like:

 #"Filtered Rows" = Table.SelectRows(Source, each ([Date is on after LowerDate] = True)),

Which is more efficient for excel to calculate?

 

[smozgur]

Power Query is executing a comparison in either way.
Unless you need the helper column in Excel for another purpose, then I would use the first way to make it work one less step and save an unnecessary column in Excel.

 

[kcmuppet]

Power Query is executing a comparison in either way.
Unless you need the helper column in Excel for another purpose, then I would use the first way to make it work one less step and save an unnecessary column in Excel.

Hi - Thanks for taking the time to reply.

The reason I'm wondering is because some things seem to take much longer in Power Query than the equivalent in Excel - like pivoting data - and because if I understand correctly, Power Query doesn't really hold any data, but rather pulls it through from the source each time, so I'm wondering if its more work for PQ to keep pulling the parameters and making the comparison between the data and the parameters than pulling the data from the table and filtering by the helper column values

 

[smozgur]

Any M code implementation that is using "each" keyword or any M function that requires a function to loop through the data set normally comes with some performance concerns, because it basically loops through that record set, whether it is a list or thousands of rows. You are right that Power Query doesn't really hold any data, but simply querying the data to produce the desired result. However, it doesn't mean that it keeps running all the time but only when you refresh the data, and it is also using partial data during the query design.

My idea is, if we consider Power Query actually works similar to a database query, using an extra helper column on the worksheet wouldn't be different than creating an extra field in a database and save the calculation in that field and then query the table by using SQL. There are times that I really needed to do this, especially to avoid really expensive queries in tables that contain fields that are not indexed but rarely.

So, perhaps I should say, "the method to chose depends on the data structure". Thanks for the question by the way, I already started researching about this. Hope we can hear others' ideas too.