Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

how to transform one table based on values in another table

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?

1 ACCEPTED SOLUTION
v-chuncz-msft
Community Support
Community Support

@Anonymous,

 

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

Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

1 REPLY 1
v-chuncz-msft
Community Support
Community Support

@Anonymous,

 

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

Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.