Find and replace one specific value in a row

[ebeauchamp]

Hi everyone,

In Power BI, I am currently using a table with about 8M transactions from an SQL query. For one very specific transaction, I want to change the SKU name and replace it with a different SKU name. With Power Query Editor, I can achieve it by filtering the transactions to identify the row in question (filter by customer name, and the specific amount), and then use the "Replace Values" option, with the SKU column selected. But then I can't go back and unfilter the table, so this method does not work.

What other methods are available to find a specific row, and then replace the original value in that row only? With the constraint that this same original value is present on thousands of other rows where it should stay as is and not be impacted by the replacement. 

Thanks for your help,
Erik

[Mariusz]

Hi @ebeauchamp 

Can you provide a data sample so the scenario can be recreated?

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

[ebeauchamp]

This is very simplified example from a table with 30 columns and Ms of rows. But basically, I need to apply a transformation after the query where I can find the row with the amount "5787", and replace the SKU "ProductABC", for another SKU, eg "ProductXYZ", just for that row - without impacting the other rows. Thanks

Snapshot	Client	SKU	Amount
2020-06-09	Client1	ProductABC	100
2020-06-09	Client2	ProductABC	120
2020-06-09	Client3	ProductABC	90
2020-06-08	Client1	ProductABC	130
2020-06-08	Client2	ProductABC	5787
2020-06-08	Client3	ProductABC	100
2020-06-07	Client1	ProductABC	392
2020-06-07	Client2	ProductABC	400
2020-06-07	Client3	ProductABC	110

[Anonymous · Accepted solution]

If you are sure there is only one record matching criteria, you can use this line of code:

= Table.ReplaceMatchingRows(yourTab, {Table.SelectRows(yourTab, each [name]="Client3" and [amount]=610){0},[name="Client3",amount=610,SKU=newValue]})

Otherwise, in case of multiple rows matching the criteria, try this:

Table.ReplaceMatchingRows(yourTab, List.Zip({Table.ToRecords(Table.SelectRows(yourTab, each [name]="Client3" and [amount]=610)), List.Repeat({[name="Client3",amount=610,SKU=newValue]}, List.Count(Table.ToRecords(Table.SelectRows(yourTab, each [name]="Client3" and [amount]=610))))}))

PS

In case of duplicate rows, seems there is some problems. But I didn't investigate furter .

[Anonymous]

This should work in both cases:

Table.FromRecords(Table.TransformRows(yourTab, each if ([name]="nome3" and [amount]=5787) then Record.TransformFields(_,{"SKU", (_)=> newValue}) else _))

[Anonymous]

and yet another way to set a field to a specific value:

Table.FromRecords(Table.TransformRows(yourTab, each if ([name]="nome3" and [amount]=5787) then Record.Combine({_,[SKU=newValue]}) else _))

Also this solution seems valid  both in case of single, multiple or null matching