Create new records from keyword list

Hi UBComma ,

 

Please refer to the following steps:

let
  Source = Table.FromRows(
    Json.Document(
      Binary.Decompress(
        Binary.FromText(
          "i45WMlTSUUrKTFdIKsovz1NITixRitWJVjICihZnJKanVyqk5+ekpOYppOSng2WMgTL5RYl56alwxSYgI3ISk7NBIgqJeSlQw8BaYgE=", 
          BinaryEncoding.Base64
        ), 
        Compression.Deflate
      )
    ), 
    let
      _t = ((type nullable text) meta [Serialized.Text = true])
    in
      type table [#"Title Index" = _t, #"Title cleaned" = _t]
  ), 
  #"Changed Type" = Table.TransformColumnTypes(
    Source, 
    {{"Title Index", Int64.Type}, {"Title cleaned", type text}}
  ), 
  #"Added Custom" = Table.AddColumn(
    #"Changed Type", 
    "Contains Keyword", 
    each List.Transform(
      List.Transform(
        {
          List.Transform(
            {0 .. List.Count(Keywords) - 1}, 
            (x) => Number.From(List.Contains(Text.Split([Title cleaned], " "), Keywords{x}))
          )
        }, 
        each List.PositionOf(_, 1, 2)
      ){0}, 
      each Keywords{_}
    )
  ), 
  #"Expanded Contains Keyword" = Table.ExpandListColumn(#"Added Custom", "Contains Keyword")
in
  #"Expanded Contains Keyword"

Best Regards,
Gao
Community Support Team

 

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

if you turn that list into a one column table, you can then add a column which uses each Table.FindText. This will bring in the whole row if it finds your text anywhere in the row.

 

--Nate

IN Dax Table

 

Table =
GENERATE (
    src,
    VAR str = src[Title cleaned]
    RETURN
        SELECTCOLUMNS (
            FILTER ( Keywords, CONTAINSSTRING ( str, Keywords[Column1] ) ),
            "val", Keywords[Column1]
        )
)

 

in DAX MEasure

Measure = 
CONCATENATEX (
    GENERATE (
        src,
        VAR str = src[Title cleaned]
        RETURN
            SELECTCOLUMNS (
                FILTER ( Keywords, CONTAINSSTRING ( str, Keywords[Column1] ) ),
                "val", Keywords[Column1]
            )
    ),
    [val],
    ",",
    [val], ASC
)