how to transform one table based on values in another table

[Anonymous]

I have a source table to "clean up".  In particular, there are some bad values in a certain column.  The correct values are stored in another table.  The source table is pretty small (~1k rows), and the data to clean up is even smaller (~20 rows).  In other BI tools, I simply join the 2 tables (left join dirty table to clean table, returning all columns), create a derived column to output clean data (e.g., if clean is populated, then clean, else dirty), then drop my original dirty column and rename my derived column.  I think this is the same approach to use in Power BI.

I have very little experience in Power BI.  Is it best to resolve this problem in the query editor (e.g., M)?  Or, is it best to resolve it in the data model (e.g., DAX)?  In either case, what would the code look like?

[v-chuncz-msft · Accepted solution]

@Anonymous,

You may use Merge Queries (Table.NestedJoin) in Query Editor or LOOKUPVALUE Function in DAX.