Forum Discussion
Comparing values from two columns and write result in third one
In the query-editor (!) you can add a column with this formula:
List.Contains(NameOfThePreviousStep[ID1], [ID2])
This will check, if the value of the current row from column "ID2" matches any occurances within column "ID1". In order to search the whole column "ID1", you need to prefix it with the name of the previous step in your query.
This is a sample code, which demonstrates it if you paste it into the advanced editor:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSAeJYnWglIyDLWCk2FgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ID1 = _t, ID2 = _t]), ChgType = Table.TransformColumnTypes(Source,{{"ID1", Int64.Type}, {"ID2", Int64.Type}}), #"Added Custom" = Table.AddColumn(ChgType, "Exists", each List.Contains(ChgType[ID1], [ID2])) in #"Added Custom"
In the query-editor (!) you can add a column with this formula:
List.Contains(NameOfThePreviousStep[ID1], [ID2])
This will check, if the value of the current row from column "ID2" matches any occurances within column "ID1". In order to search the whole column "ID1", you need to prefix it with the name of the previous step in your query.
This is a sample code, which demonstrates it if you paste it into the advanced editor:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSAeJYnWglIyDLWCk2FgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ID1 = _t, ID2 = _t]),
ChgType = Table.TransformColumnTypes(Source,{{"ID1", Int64.Type}, {"ID2", Int64.Type}}),
#"Added Custom" = Table.AddColumn(ChgType, "Exists", each List.Contains(ChgType[ID1], [ID2]))
in
#"Added Custom"
ImkeFthis is a very nice lookup code.
You are using this code to compare two columns within same table. Is it possible to use List.Contains to lookup ID1 column in table 1 to ID2 in table 2 by any chance.
It would work like this:
List.Contains(NameOfTheQuery[ID1], [ID2])
But it might be slow on large tables.
Instead you can merge the 2 tables and instead of expanding the merged column, create a new one where you check if the merged column is empty or not:
Table.IsEmpty([MergedColumn])
This will return a true/false-column.
ImkeFthanks for your reply and actually you have guessed it correctly.
I am currently working with a very large dataset and Merging Queries is making the query very very slow. Because there are multiple tables with large datasets and to come the final result I am doing Merging multiple times at different steps of the query.
That's why I am looking for any alternative Lookup techniques in Power Query (not DAX) which may be faster than Merging.
I tried List.Contains on my current query and did find it somewhat quicker than Merging Tables. Your help is much appreciated.
Is there any other Merging/Lookup tricks you can suggest that might improve the query performance.
You can try setting a key in the table on the 1-side of the merge: https://blog.crossjoin.co.uk/2018/03/16/improving-the-performance-of-aggregation-after-a-merge-in-power-bi-and-excel-power-query-gettransform/
A merge should be much faster than my lookup-formula. Maybe you want to check again with the key added.