Forum Discussion

sparvez's avatar
sparvez
Icon for Helper I rankHelper I
3 years ago
Solved

If a column contains its value in another column as string

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.

 

A1A2 A2_evolution
banana apple1212213 apple
orange orangesdfsdf orange
avocado  banana3453453 banana
apple avocado34234SFSFSDF avocado
 pear343434 pear343434
Need Help
  • m_dekorte's avatar
    m_dekorte
    3 years ago

    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!

8 Replies

  • m_dekorte's avatar
    m_dekorte
    Icon for Resident Rockstar rankResident Rockstar
    3 years ago

    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!

  • sparvez's avatar
    sparvez
    Icon for Helper I rankHelper I
    3 years ago

    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's avatar
      m_dekorte
      Icon for Resident Rockstar rankResident Rockstar
      3 years ago

      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. 

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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.