PQ - search item return category

[sandy666]

The goal is to have a formula that when you enter the item number on column A it will return the category name on column B.
I will have multiple item numbers, each one will get 1 category name.


Item #CategorySearchItem #Category

95200-020-01Car2555-044-8544568-052-03Bike

95200-040-02Car44568-052-032555-044-85Toy

45887-011-08Toy

44568-052-03Bike

2555-044-85Toy

99557-845-25Bike

33894-516-87Car



// Join
let
Table1 = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Table2 = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
Join = Table.NestedJoin(Table1,{"Search"},Table2,{"Item #"},"Table2",JoinKind.LeftOuter),
Exp = Table.ExpandTableColumn(Join, "Table2", {"Item #", "Category"}, {"Item #", "Category"}),
RC = Table.RemoveColumns(Exp,{"Search"})
in
RC