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

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
MyWeeLola
Helper II
Helper II

How to use a seperate table to fix incorrect data

Good morning

 

I have a table which contains data from a read only sql database.

 

I have learned that some of the data is incorrect, however i cannot access the main database, therefore i wish to correct it using powerquery.

 

The main table data looks like this

 

 

EngIdFnameLnameEmailAColBcol
1ANotherANother@email.comDataMore data
2BAlphaBAlpha@email.comDataMore data
3CbetaCbeta@email.comDataMore data
4DAlphaDAlpha@email.comDataMore data
5EbetaEbeta@email.comDataMore data
6FAlphaFAlpha@email.comDataMore data
7GbetaGbeta@email.comDataMore data
8HAlphaHAlpha@email.comDataMore data
9IbetaIbeta@email.comDataMore data

 

The correction table looks like this

 

EngIdFnameLnameEmail
4NewFNewSNewFNewS@email.com
6BAlphaBAlpha@email.com
8BAlphaBAlpha@email.com
9ANotherANother@email.com

 

Using PowerQ i want to update the main table using the correction table. The correction table will reach about 30 entries, so not horrendous if i have to use code to replace each entry, however I suspect there is an easier way using relationships or code etc. The EngId only contains unique values.

 

In the example, the original values for EngID 4 are D,Alpha, DAlpha@email.com. i want this replaced with the correction value of NewF,News,NewFNewS@gmail.com 

 

I appreciate any help.

 

Lola

1 ACCEPTED SOLUTION
SamWiseOwl
Super User
Super User

Hi @MyWeeLola 

 

You can use the Merge option on the Home tab

SamWiseOwl_0-1736764564356.png

 

 

Click the two columns that overlap, you can even join on multiple columns.

SamWiseOwl_1-1736764596668.png

 

Choose the Expand option and tick the columns to bring through

SamWiseOwl_2-1736764625169.png

Finally add a column that returns the correct value if there is one.

SamWiseOwl_3-1736764777249.png

Then delete the unneeded columns:

SamWiseOwl_4-1736764803118.png

 


If you are happy with this answer please mark as a solution for others to find !

Kudos are always appreciated! Check out our free Power BI video courses.

View solution in original post

2 REPLIES 2
MyWeeLola
Helper II
Helper II

@SamWiseOwl 

 

Seems simple when you put it like that:) Thank you very much and I appreciate you taking the time to show me.

 

Lola

SamWiseOwl
Super User
Super User

Hi @MyWeeLola 

 

You can use the Merge option on the Home tab

SamWiseOwl_0-1736764564356.png

 

 

Click the two columns that overlap, you can even join on multiple columns.

SamWiseOwl_1-1736764596668.png

 

Choose the Expand option and tick the columns to bring through

SamWiseOwl_2-1736764625169.png

Finally add a column that returns the correct value if there is one.

SamWiseOwl_3-1736764777249.png

Then delete the unneeded columns:

SamWiseOwl_4-1736764803118.png

 


If you are happy with this answer please mark as a solution for others to find !

Kudos are always appreciated! Check out our free Power BI video courses.

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

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