cancel
Showing results for 
Search instead for 
Did you mean: 
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
Community Champion
Community Champion

@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


@ 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!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

BizApps LATAM 2023

Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

Power Platform Bootcamp

Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.

Top Solution Authors
Top Kudoed Authors