Forum Discussion

Luis_Gomez's avatar
Luis_Gomez
Frequent Visitor
2 years ago
Solved

Create a list from another table to feed a custom function

Hi all,

 

I have a custom fuction that counts the times words on a list are found in a string:

 

 

let
    CountKeywords = (title as text, keywords as list) as number =>
        let
            words = Text.Split(title, " "),
            matches = List.Count(List.Intersect({words, keywords}, Comparer.OrdinalIgnoreCase))
        in
            matches
in
    CountKeywords

 

 

Inside the code I hard coded a list and use the function, it works

 

 

Keywords = {"One","Two","Three"}

AddedFound = Table.AddColumn(#"Filtered Rows1", "found", each Busca_cuantos([title], Keywords)),

 

 

Now, I want to create the list from a Table, but I get errors.

 

 

Keywords= = Table.ToList(Table.SelectColumns([Table],"Brand"))

 

The error reads:

 

Expression.Error: There is an unknown identifier. Did you use the [field] shorthand for a _[field] outside of an 'each' expression?

 

What I am missing here?

 

Thank you in advance for your kind support

 

 

Need Help
  • Vijay_A_Verma's avatar
    Vijay_A_Verma
    2 years ago

    Keywords= Table.Column([Table],"Product")

    [Table] - This refers to a cell

    Table - This refers to entire table

    Use like this

    Table[Product]

    OR

    Table.Column(Table, "Product") ### This was my mistake to put Table as [Table]

3 Replies

  • Vijay_A_Verma's avatar
    Vijay_A_Verma
    Most Valuable Professional
    2 years ago

    To convert to a list - Table.Column([Table],"Brand")

    It can also be written as Table[Brand}

    • Luis_Gomez's avatar
      Luis_Gomez
      Frequent Visitor
      2 years ago

      Thank you.

      I tried Table.Column same error mesagge. Here is the sequence of my code:

       

      // Source and some cleaning...

#"Filtered Rows1" = Table.SelectRows(#"Changed Type", each ([Source.Name] <> "xxx.xlsx")),

//Keywords = {"One","Two","Three"}

Keywords= Table.Column([Table],"Product"), 

AddedFound = Table.AddColumn(#"Filtered Rows1", "found", each Busca_cuantos([title], Keywords)),

//Some conversions.....

       

      Hope this helps to find the problem.

       

      Regards

       

      • Vijay_A_Verma's avatar
        Vijay_A_Verma
        Most Valuable Professional
        2 years ago

        Keywords= Table.Column([Table],"Product")

        [Table] - This refers to a cell

        Table - This refers to entire table

        Use like this

        Table[Product]

        OR

        Table.Column(Table, "Product") ### This was my mistake to put Table as [Table]