Adding new columns from another table based on conditions

[Mary_789]

Hello,

I have two sets of data that is being imported in from two different sources. Dataset#1 contains the following columns (and additional columns not needed to match) : Brand, Vehicle, Type, Part. Dataset#2 contains the following columns: Brand, Vehicle, Type, Part, Level type, and status.

I would like to merge in the two columns to dataset #1 with the following conditions:

if dataset#1(Brand) = dataset#2(Brand) AND dataset#1(Vehicle) = dataset#2(Vehicle) AND dataset#1(Type) = dataset#2(Type) AND dataset#1(Part) = dataset#2(Part),

then duplicate the level type and status values. And if there are rows that exist in dataset#1 and not in dataset #2, then the values should equal 0 or null for those rows.

How can I achieve this in power query?

Thanks.

[v-yalanwu-msft · Accepted solution]

Hi, @Mary_789 ;

You could merge it by Multiple columns.

Expand it.

The final output is shown below:

Or can you share the example data and the result you want to output?

Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[johnt75]

Select dataset #1 then choose Merge Queries. Select the Brand Column then use ctrl-click to select the other needed columns. Choose dataset #2 and select the same columns in the same order and that will merge against all the columns

[Mary_789]

@johnt75 I have tried that and does not work since the level type column and status column do not exist in dataset#1. It requires the same amount of columns to merge two tables.

I am trying to create those columns in dataset#1 if all the other columns are equal.

[johnt75]

Maybe I was thinking of Append Queries then. There's definitely a way to join 2 tables on common columns, and I'm sure its either Merge or Append

[Mary_789]

@johnt75 Yup I am able to join the tables by appending the data.

The problem is now that there are duplicate rows, one with status type and status column with values and another with status type and status column with null as the value. 

I want to be able to combine those rows into one.