Merge or Join Two Tables On Matching Dimensions

[lemarcfj]

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!

[Zubair_Muhammad]

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

[lemarcfj]

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. 

[Zubair_Muhammad]

@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