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 .
Community ChampionHi Anonymous
Can you provide a data sample so the scenario can be recreated?
Mariusz
If this post helps, then please consider Accepting it as the solution.
Please feel free to connect with me.
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 _))