Merging Queries with OR

[bolaughlin]

Hello,

I am attempting to merge to queries together but I need to do so not simply with a TableA.Key1 = TableB.Key1 but instead with TableA.key1 = TableB.Key1 OR TableB.Key2

I have found a way to acheive what I needed with a calculated table outside of query editor but it is understandably performing poorly.  The DAX used for this new calculated table is as follows:

New Merged Table = FILTER(CROSSJOIN('Item Rank + Geo Class','RMA & CRMA'), OR('Item Rank + Geo Class'[Geography] = 'RMA & CRMA'[SPINS RMA], 'Item Rank + Geo Class'[Geography] = 'RMA & CRMA'[SPINS CRMA]))

[bolaughlin]

Thank you to everyone for the responses!

@edhans I ended up following your advice on the double-merge in Power Query.  For reference, there were four additional columns from the other table that I needed to bring in. 

Once I completed both merges, I was left with eight extra columns and because of the nature of the merges four of those columns were always null and four had all the values I wanted.  I then created four additional conditional columns with the logic of, if this column is not null then use that value, however, if it is null then take the value out of this other column.  That resulted in four columns that are always populated with the information I will need to build out my report.

Thank you so much for the guidance up to this point but I was curious to know what you thought of the strategy I used on this.  Was this in line with the logic you were suggesting with the if/then/else statement?

[AlexisOlson]

@edhans @bolaughlin I wonder if it might be better to an inner join on B.key1 and an inner join on B.key2 separately and then append (union) the result of each of these joins instead of doing two left joins and coalescing the two sets of columns.

[edhans]

yes, glad I was able to help @bolaughlin - I think what you have described is the path I would have walked down myself to ultimately get the result.

If you could mark my answer as the solution I'd appreciate it - and it would show this as solved in the forums. Need any other help, always come back and ask away!

[wdx223_Daniel]

if the key1 and key2 in Table B is unique, you can try LOOKUPVALUE

[edhans]

In Power Query?

I definitely would not do this kind of shaping in the data model using DAX. If it is a few hundred or thousand rows it might be ok, but would not scale well. Power Query or further upstream is where this belongs for sustainable performance.

[edhans · Accepted solution]

In Power Query you would need to do two merges, one with each. Then keep the valid data you want with an if/then/else statement. You can do a conditional merge based on records as shown here but I think trying to do that with tables would be painfully slow.