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
Super User III
Super User III

Re: Merge or Join Two Tables On Matching Dimensions

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

 

 

 

Try my new Power BI game Cross the River
lemarcfj Helper I
Helper I

Re: Merge or Join Two Tables On Matching Dimensions

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. 

Super User III
Super User III

Re: Merge or Join Two Tables On Matching Dimensions

@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

Try my new Power BI game Cross the River

Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Join THE global Microsoft Power Platform event series.

Join THE global Power Platform event series.

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors