cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
lemarcfj
Helper I
Helper I

Merge or Join Two Tables On Matching Dimensions

Hi,

 

This is a very common question/issue that I'm sure has many posts but I always struggle with following those because no example provided seems to be exactly alike what I am looking for. I am looking to merge two tables on two different columns that share some dimension values. Specifically, the two tables below with orange highlight shows the two different columns that I'd like merged on matching values - the final output that I'm looking for is the last table with green highlight. Note that the two tables have the same structure except the highlighted columns which are technically different dimensions, but will share SOME of the same values. Can anyone help with how I should merge this in Power BI? Should this be a join instead? Thanks!

 

sampletable 1.PNGsampletable 2.PNGsamplefinal.PNG

3 REPLIES 3
Zubair_Muhammad
Super User
Super User

HI @lemarcfj

 

Instead of a merge query.. do an append query

 

Then select the 2 columns "Creative Elements" and "campaigns Columns" in new append query and merge them using merge columns

Then select the merged column and select from the Home Tab>>>Keep Rows>>Keep Duplicates

 

See the attached file it will give you an idea how to do it

 

 

 


Regards
Zubair

Please try my custom visuals

Thanks. I believe your suggest has gotten me closer, however the final appended table that I'm getting isn't showing accurate total values. Also, In your example you have relationships between metrics (visits) from the parent tables to the appended table - this doens't work for me and gives me a error saying that one must have unique values. Considering they are metric/measure values, this column will always have duplicates. 

 

Ultimately, I need to be able to have a sum value of visits from both tables in the appended table, at daily granularity. 

@lemarcfj

 

Could you paste some data in copiable format with expected results?

 

I will try to get you the desired result in a pbix file


Regards
Zubair

Please try my custom visuals

Helpful resources

Announcements
Microsoft Build 768x460.png

Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!

May UG Leader Call Carousel 768x460.png

What difference can a User Group make for you?

At the monthly call, connect with other leaders and find out how community makes your experience even better.

Power BI Dev Camp Session 22 768x460.jpg

Check it out!

Mark your calendars and join us on Thursday, May 26 at 11a PDT for a great session with Ted Pattison!