Power Query - Check if value in column B exists in column A

user_19505980 0
Viewed 112

I have tried this with a list of 500 rows and it works great. But when I try to add it to my real query of 500k rows it takes forever (looking at row count I see that it would take days to finish). Is there any way to speed it up by "Buffering" or "Query List"? I'm very new to Power Query and is using it with Excel only

= Table.AddColumn(#"Changed Type", "TYPE_CHECK", each List.Contains(#"Source"[TYPE_SORT],[MASTER]))

this code work great but take to much resources

Example of what is expected:

example of what is expected

powerquery
2 Answers

Looks like you are trying to see if items in column of Table2 appear in column of Table1. Another way to do it is just merge the two tables

enter image description here

base version:

let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"AAA", type text}}),
#"Merged Queries" = Table.NestedJoin(#"Changed Type", {"AAA"}, Table2, {"BBB"}, "Table2", JoinKind.LeftOuter),
#"Expanded Table2" = Table.ExpandTableColumn(#"Merged Queries", "Table2", {"BBB"}, {"BBB"})
in  #"Expanded Table2"

alternate True/False version

let  Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"AAA", type text}}),
#"Merged Queries" = Table.NestedJoin(#"Changed Type", {"AAA"}, Table2, {"BBB"}, "Table2", JoinKind.LeftOuter),
#"Expanded Table2" = Table.ExpandTableColumn(#"Merged Queries", "Table2", {"BBB"}, {"BBB"}),
ConvertTrueFalse = Table.TransformColumns(#"Expanded Table2",{{"BBB", each if _=null then false else true}})
in  ConvertTrueFalse

You can also merge the same table on top of itself to check columns against each other in the same table

enter image description here

merge table on itself:

let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"AAA", type text}, {"BBB", type text}}),
#"Merged Queries" = Table.NestedJoin(#"Changed Type", {"AAA"}, #"Changed Type", {"BBB"}, "Changed Type", JoinKind.LeftOuter),
#"Expanded Changed Type" = Table.ExpandTableColumn(#"Merged Queries", "Changed Type", {"BBB"}, {"AAAmatchfromBBB"}),
#"MakeTrueFalse" = Table.TransformColumns(#"Expanded Changed Type",{{"AAAmatchfromBBB", each if _=null then false else true}})
in  #"MakeTrueFalse"
user_92642300

Right before you AddColumn, you insert a standalone step

BufferList = List.Buffer(#"Source"[TYPE_SORT])

Then on the AddColumn step, you use

#"Added Column" = Table.AddColumn(#"Changed Type", "TYPE_CHECK", each List.Contains(BufferList,[MASTER]))
user_53325000
Related