Forum Discussion
Find and replace one specific value in a row
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 differen...
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