Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Niels_NL
Helper I
Helper I

Merge two tables into one

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

 

7 REPLIES 7
ankitpatira
Community Champion
Community Champion

@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.

 

2016-05-23_22-39-21.png

 

 

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:

aGENxe7

Customer-table:

iRGj8Ft

 

 

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

nJ2wpDH

@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.

@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:

gd71wsH

@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.

@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.

@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.

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.