How to merge multiple columns

[bhupen14]

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.

[v-stephen-msft · Accepted solution]

Hi @bhupen14 ,

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.

[watkinnc]

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