Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello ,
I am trying to do this. If A1 column value exist in A2 column as string then, give me result in 'A2_evolution' column as the value of A1. If no, then just keep A2 as it is.
Can anyone help pls ? Thanks in advance.
A1 | A2 | A2_evolution | |
banana | apple1212213 | apple | |
orange | orangesdfsdf | orange | |
avocado | banana3453453 | banana | |
apple | avocado34234SFSFSDF | avocado | |
pear343434 | pear343434 |
Solved! Go to Solution.
Hi @sparvez,
Give this a go.
let
lookFor = List.Buffer( Source[A1]),
Source = Table.FromColumns(
{
{"banana", "orange", "apple" },
{ "apple15468", "pearpaihga", "banana0294576" }
}, type table[ A1 = text, A2 = text]
),
AddCol = Table.AddColumn(Source, "A2_evolution", each List.First(
List.Select( lookFor, (x)=> x = Text.Start( [A2], Text.Length(x) ))
) ?? [A2], type text
)
in
AddCol
with this result.
Ps. If this helps you solve your query, please mark it as solution. Thanks!
Hi @sparvez ,
You can add a A1 list and then expand it for comparing.
Add a custom column to compare.
Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi m dekorte
thanks, but the columns have thousand of value. I cannot write the data itself into code. Do you see a way where I can just indicate columln name ?
{"banana", "orange", "apple" }, { "apple15468", "pearpaihga", "banana0294576" }
Thanks again for your time
Hi @sparvez
You don't have to write a list by hand. Examine the lookFor variable, it returns a list by referencing tableName[columnName]
The User Interface can create the syntax for you, just right click the column header and select drill down. That will add a step to your query, you can copy the code from the formula bar to use it elsewhere and delete that step once you're done.
HI,
Can u pls post the entire code pls ?
Hi @sparvez ,
The full code was shared in the initial response, you can copy it all into a new blank query
Hi, the proposed table is just a sample. The originale file , i have over 10 thousand rows. So, a generic solution would be nice pls.
The only values that are hard coded are used to build the Source table.
Used column reference to create the lookFor list with values:
lookFor = List.Buffer( Source[A1])
and referenced that in the AddCol step
What's not generic about this solution, in your view?
Hi @sparvez,
Give this a go.
let
lookFor = List.Buffer( Source[A1]),
Source = Table.FromColumns(
{
{"banana", "orange", "apple" },
{ "apple15468", "pearpaihga", "banana0294576" }
}, type table[ A1 = text, A2 = text]
),
AddCol = Table.AddColumn(Source, "A2_evolution", each List.First(
List.Select( lookFor, (x)=> x = Text.Start( [A2], Text.Length(x) ))
) ?? [A2], type text
)
in
AddCol
with this result.
Ps. If this helps you solve your query, please mark it as solution. Thanks!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.