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 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
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 .
5 Replies
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 .
