Find Value from a list within a cell and return said value if found

[Anonymous]

Hi,

I have a list or records that describe issues related to specific devices:

Description

DEVICE 1 Peer Briefly Not Established

Port Down DEVICE 2

Network DEVICE 9  Outage

DEVICE 4 Device Reboot

And I also have a table list of names of all devices in the environment :

Device Name

DEVICE 1

DEVICE 2

DEVICE 3

DEVICE 4

DEVICE 5

DEVICE 6

DEVICE 7

DEVICE 8

DEVICE 9

How do I add a column to the first table that will return the name of the DEVICE found in the Description ?

I was able to define this with a small list of devices where I need to specify each device (using

SWITCH(TRUE(),

CONTAINSSTRING(

, but the data I am supposed to apply this to has hundreds of records so I cannot just define each one 

[DimaMD]

hi @Anonymous  DEVICE 1 Does the place have a numerical value?

[Anonymous]

Hi Dima, 

No , not all of them , I just used it in order to easily identify them as examples

[DimaMD]

@Anonymous too bad, if the devices contained a numerical value then I would have a solution for you.
look at the attached file, maybe it will help you.

[AlB]

Hi @Anonymous 

This is best done in Power Query. Place the following M code in a blank query to see the steps.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnEN83R2VTBUCEhNLVJwKspMTcupVPDLL1FwLS5JTMrJLM5ITTm0AAKVYnWilQLyi0oUXPLL8w4tgGo2UlAAy/illpTnF2UrQIUtFRT8S0sS01NRtEMlTRRcUssyk1MVglKT8vNLlGJjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Description = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Description", type text}}),

    #"Added Custom" = Table.AddColumn(#"Changed Type", "Device", each "Device" & " " & Text.Trim(Text.BeforeDelimiter(Text.AfterDelimiter([Description], "DEVICE "), " ")), type text)
in
    #"Added Custom"

Please accept the solution when done and consider giving a thumbs up if posts are helpful.  Contact me privately for support with any larger-scale BI needs, tutoring, etc.

[amitchandak]

@Anonymous , I think my video can help

Power BI- Text Part slicer to filter/search text - https://youtu.be/MKKWeOqFG4c