Forum Discussion

akhaliq7's avatar
akhaliq7
Post Prodigy
4 years ago
Solved

Is doing a right join to filter bad practice?

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.

Need Help
  • BA_Pete's avatar
    BA_Pete
    4 years ago

    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

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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's avatar
    BA_Pete
    Super User
    4 years ago

    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