Forum Discussion
Super UserNested functions - unclear on scope impact
Community ChampionHi Lutz,
have you checked that the results are actually identical?
I would have guessed that Version 2 must look like this to return the same results:
A = Table.Buffer(Table.SelectColumns(B,{"U","L","P","S"})),
#"Added Custom" = Table.AddColumn(#"Previous Step", "Match",
each Table.SelectRows(A,
(k) => (k[L]="*" or k[L]=[L])
and (k[P]="*" or k[P]=[P])
and (k[S]="*" or k[S]=[S])
)
),
(... have added a "k" in front of the column selectors that compare against the "*".)
Frohes Neues ebenso 🙂
Super UserImke,
no, haven't actually checked the results (beyond comparing the run times). So it is well possible that option 2 has a totally different result because the viewpoint has changed from the left table to the right table. I guess I'll have to cut down my sample set somewhat to make the tests manageable.
In parallel I am backporting most of the logic into SQL Server (I hadn't even mentioned that "#Previous Step" was the result of an earlier merge between a SQL Server source and a Sharepoint based CSV source ...) . It's not lightning fast either but already orders of magnitude faster than Power Query for this particular problem.
Why can't Power Query be like Vertipaq and do everything in memory? 🙂
- ImkeF
Yes, you have to use special techniques to ensure that combinations with non-SQL-sources actually fold:
SQL-query folding bug still alive and sucking in PowerBI and PowerQuery in Excel – The BIccountantAnother option to speed it up could be to rearange the logic so that you can work with a merge on multiple columns instead. But this of course depends on the actual filter requirement.