Replacing values in one column with values from another table

Sometimes it's easier to break this down into multiple steps rather than trying to do it all at once.

See attached PBIX file for a very basic example of what I mean.

It essentially merges the "lookup" table (called mapping in my case) with a left join and then uses the new value if not null otherwise uses the original.

You can then just remove the columns you don't need. 

Don't forget, Power Query is case sensitive, so you will need to account for that if this is an issue.

I hope this helps.

Anonymous this is possible. Can you post some sample data with desired output.

Hi thanks for the reply. I can't post the data as it is location sensitive, but I am looking to replace one string of text in its entirety with another string of text. There are likely to be multiple instances as I move forwards where I will need to replace items and so want to be able to add to a table of replacements to feed into the main data set rather than creating separate replace values for each entry.

Hi smpa01 , I can't figure out how to attached a pbix here. What I have found is that 

= Table.ReplaceValue ( #"Changed Type",each [Stop Location], each List.Accumulate (List.Buffer(Table.ToRecords(Amendments)),[Stop Location], (valueToReplace, replaceOldNewRecord)=>Text.Replace (valueToReplace, replaceOldNewRecord[Current], replaceOldNewRecord[Replace])),Replacer.ReplaceText,{"Stop Location"})

Does actually work, the issue was where I had the step. I moved it so that it was directly after promoting first row to headers and prior to a data change and it worked. I added a second step, the same as above to amend the start locations also.

For some reason I had to refresh about 3 times for everything to 'stick' I have no idea why this might be.

Hi, Sorry for the long delays in replying. The first table shows and example of the main data, the 2nd the replacement data. As mentioned I have been able to get the results I wanted using: 

= Table.ReplaceValue ( #"Changed Type",each [Stop Location], each List.Accumulate (List.Buffer(Table.ToRecords(Amendments)),[Stop Location], (valueToReplace, replaceOldNewRecord)=>Text.Replace (valueToReplace, replaceOldNewRecord[Current], replaceOldNewRecord[Replace])),Replacer.ReplaceText,{"Stop Location"})

as a step in  power query, but it took a few reloads for it to really stick. I had to duplicate the step to replace values in both start and stop location.

Hi guys, 
As I had the same issue I stumbled across this topic. It took me another 3 hours with ChatGPT to come up with the following code:

let
    // Load TableA
    SourceA = ..., // Enter the source for TableA

    // Load TableB
    TableB = ..., // Enter the source for TableB

    // Define a function to replace Total Amount
    ReplaceTotalAmount = (invoiceNum, amountA) =>
        let
            matchingRow = Table.SelectRows(TableB, each [Invoicenumber] = invoiceNum),
            correctedAmount = if Table.RowCount(matchingRow) > 0 then matchingRow{0}[Total Amount] else amountA
        in
            correctedAmount,

    // Add a new column with corrected Total Amount values
    UpdatedTableA = Table.AddColumn(
        SourceA,
        "Corrected Total Amount",
        each ReplaceTotalAmount([Invoicenumber], [Total Amount]),
        type number
    ),

    // Remove the original "Total Amount" column
    RemovedOriginalTotalAmount = Table.RemoveColumns(UpdatedTableA, {"Total Amount"}),

    // Rename the new "Corrected Total Amount" column to "Total Amount"
    RenamedColumn = Table.RenameColumns(RemovedOriginalTotalAmount, {{"Corrected Total Amount", "Total Amount"}}),

    // Reorder the columns back to original order
    ReorderedColumns = Table.ReorderColumns(RenamedColumn, {"Invoicenumber", ..., "Total Amount"}) // Replace ... with the other column names

in
    ReorderedColumns

Table A is your source file, TableB has some corrections.
TableB has 2 fields (in my case, but you can easily change this to your situation): invoicenumber and total amount. 
You must have understood by now, that TableA has many fields with invoice data and TableB can make a correction to the invoice amount based on a matching invoice number.

I didn't want the outcome to produce a new table, so merging was out of the option. ChatGPT tried many times to update the field with the corrected amount but that took me an hour to get around.
So the solution is:
- add a column that has either the original amount unless based on a matching invoicenumber the corrected amount of table b is placed there.
- then remove/delete the original column (that as a wrong entry)
- rename the added column to the column name you just removed/deleted
- to tidy things up, like nothing happened, I reordered the columns to the original order as the newly added column was added as last

Hope this helps someone, it took me 6 hours to figure it out, it may save you some time.
Cheers R