Joining Scripts together creates inefficient multiple script workflows

[BugmanJ]

Hello,

If, in power query, I join Table A and Table B together using Column Y on the Table A side, there are rows left over from Table B, however there is easy way to take these and use them, you have to create the same script and instead look to save these rows in another script which is EXACTLY the same but capturing this side.

Altreyx handles this very easily but Power Query appears to need a seperate script for each join like this.

Is there a better way to handle it?

Thanks

[ToddChitt]

So you are doing a LEFT OUTER JOIN between two tables, and you expect that there will be un-matched records, resulting in data from Table A that does not match data from Table B. And you want to have this result set broken out into two different tables: one where the data matches, one where there are no matches. 

Yes, in Power Query that would be two separate queries.

Have you tried REFERENCING the MERGE result and then filtering on a column in table B being NULL or NOT NULL to generate two additional queries? How does that perform? I suspect that may not meet your needs.

You could also just do the MERGE in Power Query and bring that table into the model. From there, do two additional DAX tables with FILTER and SELECTEDCOLUMNS statements to generate the respective tables. Hint: DAX operations are probably going to be faster than Power Query.

Hope that helps.