none
How can I lookup a value with the find method RRS feed

  • Question

  • I have one data table with a string in one column. A random part of that string corresponds to a value I want to retrieve from another table. So the problem is that the strings in both tables are not exactly the same, see this example:

    https://onedrive.live.com/redir?resid=744550B858A22804!614&authkey=!ADpSbTkzVLELju4&ithint=file%2cxlsx

    How can I best achieve the result I want? I am quite stuck and hope somebody has an idea.

    Friday, January 30, 2015 3:06 PM

Answers

  • Here is your solution:

    Open the Query Editor (From Other Sources->Blank Query).

    In View->Advanced Editor type the following M formula, and Close and Load to Worksheet.

    let Tabelle1= Excel.CurrentWorkbook(){[Name="Tabelle1"]}[Content], Tabelle3= Excel.CurrentWorkbook(){[Name="Tabelle3"]}[Content], #"Added Custom" = Table.AddColumn(Tabelle1, "Custom", each Table.SelectColumns(Tabelle3, {"keyword","lookup value" })), #"Expand Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"keyword", "lookup value"}, {"keyword", "Lookup"}), #"Filtered Rows" = Table.SelectRows(#"Expand Custom", each Text.Contains([String], [keyword])), #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"keyword"}) in #"Removed Columns"

    Hope it helps,

    Gil

    Monday, February 2, 2015 7:11 AM

All replies

  • You could try the Fuzzy Lookup Add-In for Excel.
    https://www.microsoft.com/en-gb/download/details.aspx?id=15011

    + Good blog post on how it works and how to use it
    Friday, January 30, 2015 5:41 PM
  • Here is your solution:

    Open the Query Editor (From Other Sources->Blank Query).

    In View->Advanced Editor type the following M formula, and Close and Load to Worksheet.

    let Tabelle1= Excel.CurrentWorkbook(){[Name="Tabelle1"]}[Content], Tabelle3= Excel.CurrentWorkbook(){[Name="Tabelle3"]}[Content], #"Added Custom" = Table.AddColumn(Tabelle1, "Custom", each Table.SelectColumns(Tabelle3, {"keyword","lookup value" })), #"Expand Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"keyword", "lookup value"}, {"keyword", "Lookup"}), #"Filtered Rows" = Table.SelectRows(#"Expand Custom", each Text.Contains([String], [keyword])), #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"keyword"}) in #"Removed Columns"

    Hope it helps,

    Gil

    Monday, February 2, 2015 7:11 AM