Forum Discussion
Find and replace one specific value in a row
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 .
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 |
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 .
This should work in both cases:
Table.FromRecords(Table.TransformRows(yourTab, each if ([name]="nome3" and [amount]=5787) then Record.TransformFields(_,{"SKU", (_)=> newValue}) else _))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