Is doing a right join to filter bad practice?

[akhaliq7]

Hi I have found a useful method in filtering rows. If you right join merge a fact table with a lookup table then do a right join the lookup table being the first table then the lookup table is filtered to just contain the rows that are in the fact table (second table). This is quite beneficial as it stops the loading of 100,000s of rows. But my question is, is this good practice to follow or should I avoid using this way to filter tables.

[Anonymous]

My opinion is that if both tables are in the same database, and there are key columns or it's at least decently indexed, it should be pretty efficient. It will still fold to the database. An inner join would be even more efficient, although that may not suit your needs. If your join columns are sorted ascending, you can use Table.Join and add the JoinAlgorithm.SortMerge, and it'll load in a flash.

Table.SelectRows using a distinct, buffered list is lighting fast as well. I say do whichever gets you there. But with a well maintained database, nothing is faster than SQL at joining data.

--Nate

[BA_Pete · Accepted solution]

Hi @akhaliq7 ,

I would strongly advise against using merges for anything that can be avoided.

For your scenario, i.e. creating dimension tables from fact tables, I would just create a new blank query something like this:

Table.Distinct(
    Table.SelectColumns(
        factQueryName,
        {"ID column name", "desciption column name"}
    )
)

If you just want to cut down a table based on values in another table, I would use a buffered list comparison, something like this:

Table.SelectRows(
    Table/QueryName,
    each List.Contains(
	    List.Buffer(referenceTable[columnWithFilterValues]),
	    tableToFilter[columnName]
    )
)

Pete