Return all values based on lookup table

[Anonymous]

Hello,

I recieve data logs, and one of the columns contains extremely long strings that are a combination of many different variables.  In an effort to parse out some of this data into usable pieces of information I am struggling to achieve the following (note, data is simplified for the example).

I have values that look like this:

I want to lookup in this table to see if the string contains ANY of the values in this table:

The ideal result would be:

Now, the strings are much more complicated than the above, and the Key Values I want to parse out are 30+. Otherwise I might just use a few conditional columns and then concatenate them.

Not quite sure how to proceed and any help would be greatly aprpeciate.

Thanks!

[Anonymous]

Hi @Anonymous 

In addition to  CNENFRNL 's reply,  CNENFRNL's code has three steps: 1. load the source 2. build a Key query 3. lookupvalue from key in String column.

Your actual table may be complex, so build a key query by yourself is not a good idea.

Try to build a key value table ,right click the column and add key column as new query. Key(2) is our query list.

Then copy this Lookup code and paste it behind source code, don't forget to add ",".

In lookupcode Key(2) after List.Accumulate is our query list name and [column1] is column name in Table like string.

let
Source = ...
...    ,
Lookup = Table.AddColumn(Source, "Result", each Text.Combine(List.Accumulate(Key(2), {}, (s,c) => if Text.Contains([Column1], c) then s&{c} else s), ","))
in
    Lookup

Result is as below. Please make sure all key value is in query list.

Best Regards,

Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. 

[Anonymous]

Thank you for the response!

I unfortunately couldn't get this to work quite right.  My results column kept throwing an error, and when I tried to just recreate the above by uploading a single column table and single column key table to replicate the above I only managed to end up with blank values.

The strings in my table can be quite long, so not sure if that may be an issue.  Regardless, I apprecitae you taking the time to try and help me.  

[Anonymous · Accepted solution]

Hi @Anonymous 

I will show you an other sample.

This time I get a sample from Excel.

Build a key table and create a key query list by this table.

Then let's add the lookup step in advance editor.

let
    Source = Excel.Workbook(File.Contents("...\Case Sample.xlsx"), null, true),
    Sheet10_Sheet = Source{[Item="Sheet10",Kind="Sheet"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(Sheet10_Sheet,{{"Column1", type text}}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"String", type text}})
    ,
    Lookup = Table.AddColumn(#"Changed Type1", "Result", each Text.Combine(List.Accumulate(#"Key (2)", {}, (s,c) => if Text.Contains([String], c) then s&{c} else s), ","))
in
    Lookup

Change the code in red box. Result is as below.

Please Set Privacy as always ignore in Options.

Best Regards,

Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. 

[Anonymous]

If you hadn't posted the privacy settings I'd have been stuck forever. Thank you, thank you, thank you!! I beleive because I am working with sharepoint files that those settings needed to be shifted. Works beautifully!

[Anonymous]

Thanks for the reply @CNENFRNL !

Unfortunately the strings I put in my above example were just to show what I was hoping to accomplish. The actual strings and lookup values are quite different (and very complex/long strings).  I'm not sure how to repurpose the above solution to use with the actual data I have (which I unfortunately cannot share due to privacy policy).

thanks!

[CNENFRNL]

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSsnJNtbJSQ5OTc9NzSsxUkhMyUnPSXQvyi8tMFSK1YlWCgYCiKSJDljYKDkxJwUsBVFlZGxiqgNTkpyTaK0UGwsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [String = _t]),
    Key = {"Segment1","Segment2","Segment3","Segment4","Group1","Group2"},
    Lookup = Table.AddColumn(Source, "Result", each Text.Combine(List.Accumulate(Key, {}, (s,c) => if Text.Contains([String], c) then s&{c} else s), ","))
in
    Lookup