If a column contains its value in another column as string

[sparvez]

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

[v-stephen-msft]

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.           

[sparvez]

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

[m_dekorte]

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. 

[sparvez]

HI,

Can u pls post the entire code pls ?

[m_dekorte]

Hi @sparvez ,

The full code was shared in the initial response, you can copy it  all into a new blank query

[sparvez]

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.

[m_dekorte]

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?

[m_dekorte · Accepted 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!