Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

How to merge multiple columns

Hello All,

 

I have two files, In one file there are two columns which has same values, few of them are not matched.

I want to do vlookup to those columns values with values in another file but not able to do.

 

Normally to get the correct data, I am applying Vlookup in files1 for column 1 with file 2 column 2 values and for pending cells I am applying vlookup in files1 for cloumn 2 with file 2 column 2 values.

Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    Hi Anonymous ,

     

    One solution using DAX.

    Please kindly refer to this article. The main measure is like

     [Is this company a metals company] =

  =IF(
      SUMX(MatchList,
           FIND(
                UPPER(MatchList[Keyword]),
                UPPER(Companies[Company])
                ,,0
               )
          ) > 0,
      “YES!”,
      “Probably Not”
     )

     

     

    Another solution using Query Editor.

     

    1. Left Outer Join

    2. Conditional Column.

     

    See the attached screenshots.

    LEFT OUTER JOIN

    EXPAND COLUMN

    CONDITIONAL COLUMN

    FINAL OUTPUT

     

    Reference: MATCH between 2 tables

     

    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.

     

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    I would do a left join, which you can do right in the Power Query GUI, or you can paste this in into the formula editor (after changing it to your own table/column names):

     

    = Table.Join(PriorStepNameOrCurrentTableName, {"column 1'}, OtherTableName, {"column 2"}, JoinKind.LeftOuter)

     

    Same for your "pending" cells:

     

    = Table.Join(PriorStepNameOrCurrentTableName, {"column 2'}, OtherTableName, {"column 2"}, JoinKind.LeftOuter)

     

    --Nate

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi Anonymous ,

     

    One solution using DAX.

    Please kindly refer to this article. The main measure is like

     [Is this company a metals company] =

  =IF(
      SUMX(MatchList,
           FIND(
                UPPER(MatchList[Keyword]),
                UPPER(Companies[Company])
                ,,0
               )
          ) > 0,
      “YES!”,
      “Probably Not”
     )

     

     

    Another solution using Query Editor.

     

    1. Left Outer Join

    2. Conditional Column.

     

    See the attached screenshots.

    LEFT OUTER JOIN

    EXPAND COLUMN

    CONDITIONAL COLUMN

    FINAL OUTPUT

     

    Reference: MATCH between 2 tables

     

    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.