none
Conditional Merge RRS feed

  • Question

  • Hi,

    I'd like to do the following in PowerQuery/M :

    JOIN TABLE1 ON (TABLE1.COLUMN1 = TABLE2.COLUMN1 AND TABLE2 = "MyFilterValue")

    or

    JOIN TABLE1 ON (TABLE1.COLUMN1 = TABLE2.COLUMN1)

    WHERE  TABLE2 = "MyFilterValue"

    Is it possible ?

    Thanks for your help !


    Bertrandr

    Tuesday, June 7, 2016 9:51 AM

Answers

  • Yes, there are two ways of doing this. The easiest way is to use the "nested table" approach described here:

    https://blog.crossjoin.co.uk/2014/06/02/join-conditions-in-power-query-part-1/

    ...then apply your filter. However this might not perform well on large data sources, so the slightly more complex solution is to create a custom column containing the value 1 on each table, do a merge to join the two tables on this column, and then apply your filter, as shown here:

    https://blog.crossjoin.co.uk/2014/06/04/join-conditions-in-power-query-part-2-events-in-progress-performance-and-query-folding/

    HTH,

    Chris


    Check out my MS BI blog I also do SSAS, PowerPivot, MDX and DAX consultancy and run public SQL Server and BI training courses in the UK

    Tuesday, June 7, 2016 11:39 AM

All replies

  • Yes, there are two ways of doing this. The easiest way is to use the "nested table" approach described here:

    https://blog.crossjoin.co.uk/2014/06/02/join-conditions-in-power-query-part-1/

    ...then apply your filter. However this might not perform well on large data sources, so the slightly more complex solution is to create a custom column containing the value 1 on each table, do a merge to join the two tables on this column, and then apply your filter, as shown here:

    https://blog.crossjoin.co.uk/2014/06/04/join-conditions-in-power-query-part-2-events-in-progress-performance-and-query-folding/

    HTH,

    Chris


    Check out my MS BI blog I also do SSAS, PowerPivot, MDX and DAX consultancy and run public SQL Server and BI training courses in the UK

    Tuesday, June 7, 2016 11:39 AM
  • Hi Christopher,

    First of all, thanks for your answer and tips provided by your links.

    Finally, what I did:

    I created 3 tables from References on my "Table2".

    On each referenced table I filtered as needed and named them appropriately (I had only 3 values possible for this filter).

    On my main table, "Table1" I merged the referenced tables.

    Don't know if it's the sexiest workaround but at least it's answering my needs.

    Thanks again for your help.

    Regards,


    Bertrandr

    Wednesday, June 8, 2016 12:41 PM