Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.
Hello,
i have a column with long text. The string within that text I'm looking for has either 001-005 at the end, like that:
Material001, Material 002, Material 003, Material.....
That string is always at another position, in the text. But if its there my calculated column should display that text
Calculated Column
Material001
Material002
"N/A"
etc.
The function Search and Find has not the features for that purpose.
Thank you very much in advance.
Best.
Solved! Go to Solution.
Hi,
This calculated column formula works
Column = coalesce(FIRSTNONBLANK(FILTER(VALUES('Search strings'[Strings]),SEARCH('Search strings'[Strings],Data[OriginalTextColumn],1,0)),1),"Not found")
Hope this helps.
here is a sample table:
OriginalTextColumn | CalclatedColumn | |
***Productionkey***Additional Information for Assingement*** Material001 | Material001 | |
***Productionkey***Material002*** Additional Information for Assingement | Material002 | |
Additional Information for Assingement | No Material found |
As you can see the word MaterialXXX is always at another position.
Hope that clarifies.
Best regards.
Hi,
This calculated column formula works
Column = coalesce(FIRSTNONBLANK(FILTER(VALUES('Search strings'[Strings]),SEARCH('Search strings'[Strings],Data[OriginalTextColumn],1,0)),1),"Not found")
Hope this helps.
Hi,
Your expected result is not clear. Show the actual data and expected result very clearly.
Hi @Applicable88 ,
Is it not possible for you to this transformation upstream in Power Query?
Did I help you today? Please accept my solution and hit the Kudos button.
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
61 | |
52 | |
50 | |
36 | |
33 |
User | Count |
---|---|
84 | |
71 | |
55 | |
45 | |
43 |