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

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
Mourad_Analyst
Regular Visitor

Check if substring from column exists inside string in another table column

I have two tables, firstone identifiants, which has id column as below :

image.png

second table informations, which has information id column as below:

image.png

 

I added a custom column check in identifiants table to check if id exists (TRUE) or not (FALSE) in information id column in informations tabe.

 

please what's the solution for that?

1 ACCEPTED SOLUTION
m_alireza
Solution Specialist
Solution Specialist

@Greg_Deckler , correct me if I am wrong but I dont believe Text.Contains will work on its own as it doesnt work with a list of values... ?

Perhaps something like this instead: 

=Text.Contains(Text.Combine(informations[informations id]),[id])



View solution in original post

6 REPLIES 6
ImkeF
Super User
Super User

Hi @Mourad_Analyst ,
to my understanding of the problem, it seems to work alright (as the fields in information id don't seem to match those of the id column.
So maybe my understanding of the problem is incorrect. 
Could you please upload sample data in a way so that it can be used to provide a correct answer:

https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...

Also, please indicate what result exactly you expect for reach row of the sample data.

 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

Agreed with this. Please provide some sample data and a sample output as to what you're expecting to accomplish. That will allow us to see things more clearly

ImkeF
Super User
Super User

Hi @Mourad_Analyst ,
could you merge the informations table on "information ID" and then instead of expanding the resulting table, add a column with the formula "not Table.IsEmpty[MergedColumnName]".

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

AntrikshSharma
Super User
Super User

@Mourad_Analyst Use this:

AddedCustom = 
    Table.AddColumn (
        ChangedType,
        "Custom",
        ( CurrentRow ) =>
            List.AnyTrue (
                List.Transform ( 
                    identifiants[ID], 
                    (ID)=> Text.Contains ( CurrentRow[information id], ID ) 
                )
            ),
        Logical.Type
    )

AntrikshSharma_0-1668848119374.png

 

m_alireza
Solution Specialist
Solution Specialist

@Greg_Deckler , correct me if I am wrong but I dont believe Text.Contains will work on its own as it doesnt work with a list of values... ?

Perhaps something like this instead: 

=Text.Contains(Text.Combine(informations[informations id]),[id])



Greg_Deckler
Super User
Super User

@Mourad_Analyst Use Text.Contains



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

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