Power Query load times get longer after edited steps

[xtro]

Hello!

I'm using power query for my daily work and I notice whenever I edited some steps inside my query, the output result take longer than before.

My query usually have a couple of master data table and raw table, then I perform merge, group, pivot, etc

The rows vary, from around 10k ~ 300k

For example, I have a query that has a custom column to define a metric.. let's say:

variable A1 = if (field A) = 1 then "X" else "Y"

The estimated query load time usually takes around 30 ~ 1 hour

Then for the next report, I need to change the logic on "variable A1" to :

variable A1 = if (field A) = 1 then "X" else if (field A) = 0 and (field B) = 1 then "Z" else "Y"

For some reason the query load time becomes longer than before, it could takes up around more than 1 hours

Can somebody explain what happen behind the program? I thought it is just a simple tweak why does it have big impact?

Thanks

[Anonymous · Accepted solution]

Well, "AND" statements take longer in every system, and in your case, you have two "if" equality tests as well. So in your original query, one column gets searched once. In your second query, you search the first column, and if false, then first column again, along with another column if that "if" predicate is true. Instead of looking for a value in one column, the query now has to look through one, but maybe two, or maybe three.

--Nate

[Gurps · Accepted solution]

I have found that the query load only begins to take a very long time when you start merging / appending tables that contain structured columns (the ones that you can expand to access data in linked tables). 

When removing these before performing the merge the load times are drastically reduced. 

[Anonymous · Accepted solution]

Well, "AND" statements take longer in every system, and in your case, you have two "if" equality tests as well. So in your original query, one column gets searched once. In your second query, you search the first column, and if false, then first column again, along with another column if that "if" predicate is true. Instead of looking for a value in one column, the query now has to look through one, but maybe two, or maybe three.

--Nate