I’m trying to merge the following 2 tables, using a Left-hand join. Objective is to have a single table for purposes of Gross Margin calculations. I’m joining the tables based on both Category and Month. But it results in duplicating a row from the ‘Revenue’ Table no 2. Any suggestions?
Hi @mdalton2100,
could you put the revenue gl account number and Conditional column (category x) in your revenue table? If you do that it would easier to merge your two table together.
Another option is pull all information directly from your accounting system all at once so you don’t have to merge the tables.
I hope this helps
Keith
@mdalton2100 That is a correct behaviour of LEFT JOIN, take everything from the table on LEFT and JOIN with matching rows from table on RIGHT
@AntrikshSharma beat me to it, but here’s a different version of the join graphic from Curbal that I find a bit more intuitive that also has the corresponding DAX functions used to achieve each type of join.
– Brian
Hi @mdalton2100
As mentioned above, this is the correct behavior of left join.
To get the proper results, I would group the transactions in “cost” and “revenue” tables by month and category before merging, by doing so it will be one-to-one.
If you need to keep the transactions in the original grain, another approach could be used, by appending the two tables, and in that case, you have to set a new query with the GL Account and the classification of each account (revenue, cost, expense, …), this new query will be merged to the appended data to get the classification.
Hi. Thanks for the tip! To get around the merge issue, as you suggested I’m opting instead to consolidate the data pull into one table. That has worked out just fine. I believe that using a Left outer join for merging two fact or transaction tables is not generally advised.
Indeed it was a Left join, which I deployed within the Power Query editor. As it was an unsuitable approach, I have instead expanded my original source data pull to be one single table of Cost and Revenue rows. Again, thanks for steering me in the right direction!
Your profile picture reminds me of Negan 
