cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
cerebro
Frequent Visitor

Check if column text contains values from another column and return the text

Dear everybody,

 

I have two different sources and i would like to search for the project number from the second source in the account from the first source and return the project in the custom column:

powerquery.JPG

Based on the solutions to similar problems i've tried to add a custom column like this:

 

= Table.AddColumn(#"PreviousStep", "ProjectNumber", each List.Contains(Text.Split([Account], "-"), Source2 [Project])))

But the part Source2 [Project]))) is not working

Can someone please correct my formula or suggest any other ?

Thank you in advance.

 

1 ACCEPTED SOLUTION

Hi @cerebro 

 

Download sample XLSX file

 

Yes, you can use Text.BetweenDelimiters to extract just the 4 digits between the - -

 

= Text.Select(Text.BetweenDelimiters([Account], "-", "-"), {"0".."9"})

 

 

txtsel.png

 

Regards

 

Phil



Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.


Blog :: YouTube Channel :: Connect on Linkedin


Proud to be a Super User!


View solution in original post

7 REPLIES 7
PhilipTreacy
Super User
Super User

Hi @cerebro 

 

Download example XLSX file

 

You'll need to take a different approach for this but this line will do it (in a new custom column)

 

= let _account = List.Select(Text.Split([Account], "-"), each Text.Length(_) = 4) in

if List.Count(_account) > 0 then _account{0} else null

 

acc12.png

 

Regards

 

Phil



Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.


Blog :: YouTube Channel :: Connect on Linkedin


Proud to be a Super User!


v-cgao-msft
Community Support
Community Support

Hi @cerebro ,

Please try like:

= Table.AddColumn(#"Changed Type", "ProjectNumber", each Table.SelectRows(Source2,(x)=>Text.Contains([Account],x[Project]))[Project]{0})

vcgaomsft_0-1669099584485.png

and then repalce error.

 

Best Regards,
Gao

Community Support Team

 

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly -- How to provide sample data

thanks a lot, your formula works (but only without the {0}).

I have 200-300 k lines and power query takes a while to finish it, but it works 😊

PhilipTreacy
Super User
Super User

Hi @cerebro 

 

Download example XLSX file

 

If your Account in Source1 always contains only one 4 digit project code (or none), then you can extract it using this.  No lookup to Source2 needed.

 

= Text.Select([Account], {"0".."9"})

 

 

If the Account in Source1 might also contain numbers that are not Project Codes, you could use this which does check against Source2

= if List.Contains(Source2[Project],Text.Select([Account], {"0".."9"})) then Text.Select([Account], {"0".."9"}) else null 

 

 

regards

 

Phil

 



Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.


Blog :: YouTube Channel :: Connect on Linkedin


Proud to be a Super User!


thank you for the elegant solution.

Yes in fact, there are some other numbers that do not represent a project number and if you replace any letter with a number, the second formula doesnt work anymore:

Cerebro1.JPG

Can you fix it? Thanks again!

Hi @cerebro 

 

Download sample XLSX file

 

Yes, you can use Text.BetweenDelimiters to extract just the 4 digits between the - -

 

= Text.Select(Text.BetweenDelimiters([Account], "-", "-"), {"0".."9"})

 

 

txtsel.png

 

Regards

 

Phil



Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.


Blog :: YouTube Channel :: Connect on Linkedin


Proud to be a Super User!


sorry to bother you again, but could you please edit the formula that it returns a match even when the project number is not in the middle of the text string ? thanks again for your help!

Cerebro2.JPG

 

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