How do you create a new column based on values in another column in the same table and another column in a different table?
Example
Table 1: | Index | Part Description | | -------- | ----------------------- | | 1 | Contain Leather Cushion | | 2 | West Hill 72W Circuit |
Table 2: "Model_Names"(Note: Replace column used in different process)
Name | Replace |
---|---|
Contain | |
West Hill |
Result:
Index | Part Description | Make |
---|---|---|
1 | Contain Leather Cushion | Contain |
2 | West Hill 72W Circuit | West Hill |
Initally I was using if and elif functions to do the process however this is not an effective method.
I attempted using Code Below, however its currently only produces "Universal" and doesn't output any matching values:
make = Table.AddColumn(#"Multiplied Column1", "Make", each if List.Contains(Table.Column(Model_Names, "Name"), [Part Description]) then Table.Column(Model_Names, "Name") else "Universal")
Here's a suggestion:
Table4
is the one with Part Description
let
Source = Excel.CurrentWorkbook(){[Name = "Table4"]}[Content],
modelNames = List.Buffer(Model_Names[Name]),
makeList = List.Transform(Source[Part Description], (descr) =>
try List.Select(modelNames, each Text.Contains(descr, _)){0} otherwise "Universal"),
columns = Table.ToColumns(Source) & {makeList},
headers = Table.ColumnNames(Source) & {"Make"},
addedMake = Table.FromColumns(columns, headers)
in
addedMake