How to do full-text search and return coding for a new column?

[asas27]

Hi Sir/Madam,

I got a coding table like this:

Name	Code
Bob	1
Ann	2
Cat	3
Peter	4
Mary	5
Tina	6

Then, I want to do full-text search in the first column of the following table and ask Query editor to fill-in the code for me in the 2nd column of the following table.

Full-text search	Code (This is the column I need Query Editor to fill-in for me)
David is here	N/A (no match)
Tina is leaving	6
I am Peter	4
Peter, Tina and Cat are here	3; 4; 6 (more than one match)
Where is Bob?	1
Ann and Bob are my friends	1; 2 (more than one match)
Please catch-up with Ann	2 (did not confuse between Cat and Catup)

How to do it with Query Editor?

Many thanks!

Best regards,

Catherine

[edhans]

@asas27 - can you advise if any of the above solutions work for your needs and mark it (them) as a solution? 
If none do, please reply back with clarifications so we can assist.

[edhans]

@asas27 - Got it in a cleaner step.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("RY07DgIxDESvMkq9ewbEp6GjQKKIUpiNIZZYg5LsIm5PYgq68fP42Xt3oFUipCBxZhcG786i1MGD20rvxo6gGSeunG20NMCapBF7qqDMf8elpy7ZPa8bI1tVqzZg1fmDWxbWWH7G9q0wJqpTGpcX3lIT2o0L4Qs=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Full-text search" = _t]),
    #"List Generate" = 
        Table.AddColumn(
            Source,
            "Generate",
            each 
                let
                    varTotalRecords = Table.RowCount(Text),
                    varCurrentText = [#"Full-text search"]
                in
            Text.Combine(
                List.Transform(
                    List.Generate(
                        () => [x = -1, y = {}],
                        each [x] < varTotalRecords,
                        each [
                                y = 
                                    if Text.Contains(varCurrentText, Text[Name]{x})
                                    then [y] & {Text[Code]{x}}
                                    else [y],
                                x = [x] + 1
                            ]
                        ){varTotalRecords}[y],
                    Text.From),
                    ","
                )
            )
in
    #"List Generate"

It uses List.Generate to cycle through each record and build a list of the hit codes. The table returned is:

My full PBIX file is here with both List Accumulate and List Generate.

[Anonymous]

You could perform the task with the following steps.

Define a record having names as fields and codes as values.

Split text to list of words and transform that list appliing the function record.fjeldOrDefault. wrap that in the function list remove nulls and then combine the elements of list tighter with ";".

https://community.powerbi.com/t5/Power-Query/Replace-multiple-values-in-multiple-columns-in-one-step...

[Anonymous]

now I could get my hands on a pc.
here is the code I was talking about:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("RY07DgIxDESvMkq9ewbEp6GjQKKIUpiNIZZYg5LsIm5PYgq68fP42Xt3oFUipCBxZhcG786i1MGD20rvxo6gGSeunG20NMCapBF7qqDMf8elpy7ZPa8bI1tVqzZg1fmDWxbWWH7G9q0wJqpTGpcX3lIT2o0L4Qs=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Full-text search" = _t]),


    #"Added Custom" = Table.AddColumn(Source, "CodeName", each Text.Combine(List.Transform(Text.SplitAny([#"Full-text search"], Text.Combine({" ".."@"})), each Record.FieldOrDefault(Dict, _)),";"))

in
    #"Added Custom"

[edhans]

@Anonymous - the Text.SplitAny is nice. I can redo this without the more complex List.Generate using this.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("RY07DgIxDESvMkq9ewbEp6GjQKKIUpiNIZZYg5LsIm5PYgq68fP42Xt3oFUipCBxZhcG786i1MGD20rvxo6gGSeunG20NMCapBF7qqDMf8elpy7ZPa8bI1tVqzZg1fmDWxbWWH7G9q0wJqpTGpcX3lIT2o0L4Qs=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Full-text search" = _t]),
    #"Added Custom" = 
        Table.AddColumn(
            Source, 
            "CodeName", 
            
            each
            let 
                varCurrentText = [#"Full-text search"]
            in
            Text.Combine(
                List.Transform(
                    Table.SelectRows(
                        Codes,
                        each List.Contains(
                            List.Intersect({
                                Text.SplitAny(varCurrentText, Text.Combine({" ".."@"})),
                                Codes[Name]}
                                ),
                                [Name]
                            )
                    )[Code],
                Text.From
                ), 
            ", "
            )
        )
in
    #"Added Custom"

 However, I cannot get your Record.FieldOrDefault to work. Your code (using my Codes table name) fails:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("RY07DgIxDESvMkq9ewbEp6GjQKKIUpiNIZZYg5LsIm5PYgq68fP42Xt3oFUipCBxZhcG786i1MGD20rvxo6gGSeunG20NMCapBF7qqDMf8elpy7ZPa8bI1tVqzZg1fmDWxbWWH7G9q0wJqpTGpcX3lIT2o0L4Qs=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Full-text search" = _t]),
    #"Added Custom" = 
        Table.AddColumn(
            Source, 
            "CodeName", 
            each 
                Text.Combine(
                    List.Transform(
                        Text.SplitAny(
                            [#"Full-text search"], 
                            Text.Combine({" ".."@"})
                            ), 
                            each Record.FieldOrDefault(Codes, _)
                        ),
                    ";"
                    )
            )

in
    #"Added Custom"

Which makes sense as Codes is a table, not a record.

How are you getting it to either select the records you want, or cycle through them?  I can select using Table.SelectRows, or cycle through them with List.Generate. 

Record.FieldOrDefault only works with a single record it appears.

[Anonymous]

I have to read your code thoroughly to try to understand where the difference lies.
But this makes me realize that I have not explicitly described the Dict record (which I had already talked about in the verbal description of the algorithm in the previous message)

To complete the answer here the code that "generate" the record (I suppose you should do the same with you codes table, what I did with source table).

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcspPUtJRMlSK1YlWcszLA7KNwGznxBIg2xjMDkgtSS0C8kzAPN/EokogxxTMCcnMSwRyzJRiYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Name = _t, Code = _t])
in
   Record.FromList(Source[Code],Source[Name])​

but of course the record could be construct in many other ways.

I only stolen part of (peraphs) your code to use the already inserted data.

---

[edhans]

Ok - here "Define a record having names as fields and codes as values."

I'll have to play with that. I am assuming this table of codes is coming from a data source, so there will need to be a transformation to do this, unless @asas27 keys that in manually, which means editing the PBIX and publishing each time it changes.

I've not done a lot of record transformations, so time to crack out the Record.* commands.

[edhans]

This code will do it. (NOTE: The code using List.Generate is cleaner which is a but further down in this thread.)

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("RY07DgIxDESvMkq9ewbEp6GjQKKIUpiNIZZYg5LsIm5PYgq68fP42Xt3oFUipCBxZhcG786i1MGD20rvxo6gGSeunG20NMCapBF7qqDMf8elpy7ZPa8bI1tVqzZg1fmDWxbWWH7G9q0wJqpTGpcX3lIT2o0L4Qs=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Full-text search" = _t]),
    #"Simple Replace" =
        Table.AddColumn(
            Source,
            "New Col",
            each
            try
                Text.RemoveRange(
                        List.Accumulate(
                            {0..Table.RowCount(Text)-1},
                            [#"Full-text search"],
                            (state, current) => 
                                let
                                    varCurrentName = Text[Name]{current},
                                    varCurrentNumber = ", " & Text[Code]{current} 
                                in
                                if Text.Contains(state, varCurrentName) = true
                                    then Text.Combine({state & varCurrentNumber})
                                    else state
                        ),
                        0,
                        Text.Length([#"Full-text search"]) + 2
                )
            otherwise ""
        )
in
    #"Simple Replace"

For it to work, you need a query called "Text" that is the following code:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcspPUtJRMlSK1YlWcszLA7KNwGznxBIg2xjMDkgtSS0C8kzAPN/EokogxxTMCcnMSwRyzJRiYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, Code = _t])
in
    Source

The Text query looks like this:

THe main query - the big block of code at the top, starts with just the first column, and ends with the 2nd column being added:

See below to put that code in your Query editor to play with.

1) In Power Query, select New Source, then Blank Query
2) On the Home ribbon, select "Advanced Editor" button
3) Remove everything you see, then paste the M code I've given you in that box.
4) Press Done
5) See this article if you need help using this M code in your model.

[edhans]

@HotChilli - you might be able to clean this up, or @ImkeF if you could assist. My solution works, but it goes the long way.
This code:

                    List.Accumulate(
                        {0..Table.RowCount(Text)-1},
                        [#"Full-text search"],
                        (state, current) => 
                            let
                                varCurrentName = Text[Name]{current},
                                varCurrentNumber = "~" & Text[Code]{current} 
                            in
                            if Text.Contains(state, varCurrentName) = true
                                then Text.Combine({state & varCurrentNumber})
                                else state
                    )

What it does is takes the source text column, so "Tina is Leaving" for the second record (where there is a match), and using Text.Combine() appends the relevant code number with a comma for easy parsing later. "Tina is Leaving, 6" For more matches it just keeps adding a comma and the code. Then I remove the original text with the Text.RemoveRange function.

What I wanted to do was start with nothing, and just accumulate the codes, but if Text.Combine() didn't have the state in it, nothing would accumulate. It returned either 6 or null.

So, how would I accumulate just the codes without having to have the initial text (state) as I've defined it?

[HotChilli]

Hello @edhans , good stuff (as always, you're a great contributor to the forum).

Personally, I will always try to persuade people to leave data in a 'relational' form when they want to put lists within columns.  They can always use CONCATENATEX to display the list in a visual.  

Here's the M (copied from an old Imke post) for anyone who is interested

Table.AddColumn(#"Changed Type", "NewCol",  (Other) => Table.SelectRows(TableQ, each Text.Contains(Other[#"Full-text search"],[Name])))

@asas27 Do these posts help with your question?

[HotChilli]

I was able to do it with a 'Merge as New' on the 2 tables.

Select Name and Full Text Search column and use Fuzzy Match to perform the join.

Options (Threshold 0.5 and Ignore case)-> Others may work, these were just the second ones i tried

I think there's probably a solution without the merge and maybe using Text.Contains function.  If I get time tomorrow, I might try it.