Forum Discussion
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:
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.
Hi cerebro
Yes, you can use Text.BetweenDelimiters to extract just the 4 digits between the - -
= Text.Select(Text.BetweenDelimiters([Account], "-", "-"), {"0".."9"})Regards
Phil
7 Replies
Hi cerebro
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 nullregards
Phil
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:
Can you fix it? Thanks again!
Hi cerebro
Yes, you can use Text.BetweenDelimiters to extract just the 4 digits between the - -
= Text.Select(Text.BetweenDelimiters([Account], "-", "-"), {"0".."9"})Regards
Phil
Hi cerebro ,
Please try like:= Table.AddColumn(#"Changed Type", "ProjectNumber", each Table.SelectRows(Source2,(x)=>Text.Contains([Account],x[Project]))[Project]{0})and then repalce error.
Best Regards,
Gao
Community Support TeamIf 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 😊
Hi cerebro
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 nullRegards
Phil
