Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
appsac1
Helper I
Helper I

Table.FindText returns more values than the only exact match

Hello,

 

couldn't find the solution, but probably it is something too easy. I'm using Table.FindText to merg a field from another table but in some instances it gives me more matches than I'd like. As an example I have one value 'apple' and I want to find the corresponding colours in another table. (apple > red, appletree > green). So I'm using:

 

Table.AddColumn(#"Previous step", "Colour of fruitthings", each Table.FindText(Fruitcolours, "apple") as a result i'm expecting only 'red' but i'd also get green because appletree. How do I solve this to only get 'red' and not green.

 

Kind regards,

 

 

1 ACCEPTED SOLUTION
v-stephen-msft
Community Support
Community Support

Hi @appsac1 ,

 

To get only the exact match, you can use the Word.Starts function instead of Table.FindText. Word.Starts returns true if the first word in the text matches the search string. Here's how you can modify your code:

Table.AddColumn(#"Previous step", "Colour of fruitthings", each 
    let fruit = [Fruit Column Name],
        match = Table.SelectRows(Fruitcolours, each Word.Starts(Text.Trim([Fruit Column Name]), fruit)),
        color = if Table.RowCount(match) > 0 then match{0}[Color Column Name] else null
    in color
)

 


This code uses Table.SelectRows to filter the rows in the Fruitcolours table where the first word in the Fruit Column Name matches the search string. Then, it checks if there is at least one row in the filtered table and returns the value in the Color Column Name column of the first row if there is a match, or null otherwise.


Let me know if this helps.

 

                                                                                                                                                         

Best Regards,

Stephen Tao

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.           

View solution in original post

3 REPLIES 3
v-stephen-msft
Community Support
Community Support

Hi @appsac1 ,

 

To get only the exact match, you can use the Word.Starts function instead of Table.FindText. Word.Starts returns true if the first word in the text matches the search string. Here's how you can modify your code:

Table.AddColumn(#"Previous step", "Colour of fruitthings", each 
    let fruit = [Fruit Column Name],
        match = Table.SelectRows(Fruitcolours, each Word.Starts(Text.Trim([Fruit Column Name]), fruit)),
        color = if Table.RowCount(match) > 0 then match{0}[Color Column Name] else null
    in color
)

 


This code uses Table.SelectRows to filter the rows in the Fruitcolours table where the first word in the Fruit Column Name matches the search string. Then, it checks if there is at least one row in the filtered table and returns the value in the Color Column Name column of the first row if there is a match, or null otherwise.


Let me know if this helps.

 

                                                                                                                                                         

Best Regards,

Stephen Tao

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.           

m_dekorte
Super User
Super User

Hi @appsac1,

 

Table.FindText returns a table containing only the rows that have the specified text within one of their cells or any part thereof. If you require an exact match use Table.SelectRows instead.

 

Table.AddColumn(#"Previous step", "Colour of fruitthings", each Table.SelectRows( Fruitcolours, (x)=> List.ContainsAny( Record.FieldValues(x), {"apple"} )))

 

There are many variations possible within Table.SelectRows 2nd argument but the important thing is that only rows that evaluate to true are retained.

 

Ps. Please mark this answer as solution when it helped you to resolve your question, thanks!

Hi m_dekorte, 

I had a similar problem and used your solution to solve it. It works, but I do have a hard understanding of how it works. Is it possible you could explain it in further detail, especially with the use of the variable.

 

Thanks in advance!

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors