none
Check if a string from a column contains any value from another table/column then, if it contains, bring this value RRS feed

  • Question

  • Hello guys,

    I'm really having an issue with this one.

    I need to check if a string from a column contains any value from another table/column then, if it contains, bring this value into another column, else dont bring anything.

    Eg.: Table1.ColumnA has a value "Green Apple". Table2.ColumnB is a list that contains "Apple". Since Table1.ColumnA contains "Apple", it should bring "Apple" into, say, Table1.Category.

    I'm really having a hard time here! Can you guys help? Im trying to use "List.ContainsAny" without much success.

    Monday, May 28, 2018 6:16 PM

Answers

  • The following code should work. (note that it does not deal with duplicates nor cases)

    let
        Table1 = #table( type table[#"Inventory" = text], {{"Green Old Apple"},{"Yellow Lemon"},{"Red Pear"},{"New Orange"}} ),
        Table2 = #table( type table[#"Fruits" = text], {{"Apple"},{"Potatoes"},{"Pear"},{"Orange"}} ),
    
        AddInventoryList = Table.AddColumn(Table1, "Inventory List", each Text.Split([Inventory], " "), type list),
        AddPositionList = Table.AddColumn(AddInventoryList, "Position", each List.PositionOfAny(_[Inventory List], Table2 [Fruits], Occurrence.First), Int64.Type),
        AddCategory = Table.AddColumn(AddPositionList, "Category", each 
            if [Position] <> -1 
            then [Inventory List] {[Position]}
            else null,
            type text
        ),
        RemCol = Table.RemoveColumns(AddCategory,{"Inventory List", "Position"})
    
    in RemCol
     

    Tip:

    List.PositionOfAny : If the value is not found in the list, -1 is returned





    Monday, May 28, 2018 8:44 PM