Merge two tables into one

[Niels_NL]

Hey guys,

so I've got two tables now:

• Customers
  • CustomerID
  • CustomerName
  • Locationdata
  • Businessdata
• Marketingpreferences
  • CustomerID
  • CustomerName
  • Marketingpreference1
  • Marketingpreference2
  • Marketingpreference3
  • MarketingpreferenceX

I've got 6000 customers in the 'Customers'-table. In the 'Marketingpreferences'-table, only 3000 customers are used, because not every customer has been contacted.

What I would like to have is 1 table of customers with all their marketingpreferences.

So i.e.: all 6000 customers and their marketingpreferences; if a customer hasn't been contacted yet and thus has no marketingdata yet available, its value for the particular marketingpreference-column should be empty.

How can I make this happen?

Either with the Query Editor or via any other way is okay.

Thanks.

~ Niels

[ankitpatira]

@Niels_NL In power bi desktop, edit queries, merge queries, select your tables, choose customer id and left outer join. This will merge customers table with marketing where there is matching customer id.

[Niels_NL]

Thanks for your answer @ankitpatira, but this still leaves me with a Marketing-table consisting out of 3000 customers. Whereas I wanted a Marketing-table with 6000 customers, where customers with no marketing-information available would just have empty row-values, except for the customerID.

Example of my situation:

Marketing-table:

Customer-table:

And this is the table that I would like to have (*NULL* = empty values):

[ankitpatira]

@Niels_NL in that case you select marketing table then click merge queries and merge it with customers table using customer id and left join.

[Niels_NL]

@ankitpatira I tried that, but that still gives me the same results --> 3000 customers of whom marketing preferences are known; other 3000 with unknown marketing-data aren't inserted as extra rows.

I'm getting a new column named 'NewColumn' though, see image below:

[ankitpatira]

@Niels_NL for the rest 3000 it will give null rows right ? In NewColumn click on Table and you will be able to choose which columns to be merged.

[Niels_NL]

@ankitpatira No, the 3000 other customers without marketing-data just isn't inserted.

I'm still having a total amount of 3000 records in the table, instead of 6000 where 3000 would have 'null'-values.

[Eric_Zhang]

@Niels_NL

Merge query with "Left Outer" join does work, do note the join order. I doubt it is Market Left Outer Join Custmer in your case.


I've attached the test pbix for your reference. If you have any question, feel free to let me know.