Fuzzy Reverse vlookup - any idea

For each row of the Source table, you can filter the lookup table to only include rows where the text from the Source table contains the keyword in the lookup table. Then take the category column from the lookup table. This returns a result of all the category matches and you can take the first one.

let
  Source = Table.FromRows(
    {
      {#date(2023, 12, 21), "this is a long text with a KEYWORD", "additional data", 4}, 
      {#date(2023, 12, 19), "this is a text containg a TEXTSTRING which is good to define category", null, 5}}, 
    type table [Date = date, Text = text, Data = text, Number = number]
  ), 
  lookup = Table.FromRows(
    {{"KEYWORD", "long"}, {"TEXTSTRING", "short"}, {"5232355", "small"}}, 
    type table [keyword = text, category = text]
  ), 
  Result = Table.AddColumn(
    Source, 
    "lookup", 
    each List.First(
      Table.SelectRows(
        lookup,
        (row) => Text.Contains([Text], row[keyword])
      )[category]
    ), 
    type text
  )
in
  Result