Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe 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.
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
EngId | Fname | Lname | ACol | Bcol | |
1 | A | Nother | ANother@email.com | Data | More data |
2 | B | Alpha | BAlpha@email.com | Data | More data |
3 | C | beta | Cbeta@email.com | Data | More data |
4 | D | Alpha | DAlpha@email.com | Data | More data |
5 | E | beta | Ebeta@email.com | Data | More data |
6 | F | Alpha | FAlpha@email.com | Data | More data |
7 | G | beta | Gbeta@email.com | Data | More data |
8 | H | Alpha | HAlpha@email.com | Data | More data |
9 | I | beta | Ibeta@email.com | Data | More data |
The correction table looks like this
EngId | Fname | Lname | |
4 | NewF | NewS | NewFNewS@email.com |
6 | B | Alpha | BAlpha@email.com |
8 | B | Alpha | BAlpha@email.com |
9 | A | Nother | ANother@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
Solved! Go to Solution.
Hi @MyWeeLola
You can use the Merge option on the Home tab
Click the two columns that overlap, you can even join on multiple columns.
Choose the Expand option and tick the columns to bring through
Finally add a column that returns the correct value if there is one.
Then delete the unneeded columns:
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.
Seems simple when you put it like that:) Thank you very much and I appreciate you taking the time to show me.
Lola
Hi @MyWeeLola
You can use the Merge option on the Home tab
Click the two columns that overlap, you can even join on multiple columns.
Choose the Expand option and tick the columns to bring through
Finally add a column that returns the correct value if there is one.
Then delete the unneeded columns:
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.
User | Count |
---|---|
16 | |
10 | |
9 | |
9 | |
8 |
User | Count |
---|---|
23 | |
17 | |
12 | |
12 | |
12 |