Forum Discussion

akhaliq7's avatar
akhaliq7
Icon for Post Prodigy rankPost 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 filter...
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

BA_Pete's avatar
BA_Pete
Icon for Super User rankSuper 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