Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
Dear altruists,
I have a column with many values which can be increased, such as;
Column 1
Apple
Pear
Kaki
Avocado
etc...
These values I want to find in another column, for multiple values add a sign , such as "/". Ther power query I am looking for is Result column
Column 2--------Column Result
Apple is red--------Apple
Orange, Apple is orange--------Orange/Apple
Avocado, kaki goes bad--------Avocado/kaki
Kaki is yellow--------Kaki
Can any one help pls?
regards
Solved! Go to Solution.
I have taken your sample set and produced the below output
I created TBL1 which has Columns 1 and TBL2 which has the Column 2 containing text within which Column 1 is to searched.
The Custom column was created using the below Transformation step
If the solution matches your requirement, please let me know I will upload the PBIX file
Hi Manjo, it worked , thanks a lot. Have a good day.
I have taken your sample set and produced the below output
I created TBL1 which has Columns 1 and TBL2 which has the Column 2 containing text within which Column 1 is to searched.
The Custom column was created using the below Transformation step
If the solution matches your requirement, please let me know I will upload the PBIX file
Hi Manoj,
Sorry, its not working, i add here the formula. Can u pls have a look?
= Table.AddColumn(#"Changed Type", "Custom", each List.Accumulate(Table.ToList(#"Tbl1","",(state,current)=>if Text.Contains([Column 2], current)
then current&"/"&state
else state
)))
User | Count |
---|---|
11 | |
8 | |
5 | |
5 | |
4 |
User | Count |
---|---|
16 | |
14 | |
8 | |
6 | |
6 |