excelpowerquery

Creating Conditional Column based on another Table's Column


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")


Solution

  • Here's a suggestion:

    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
    

    Power Query Result