ruthhacche
Board Regular
- Joined
- Sep 22, 2017
- Messages
- 84
I am using the below function to replace data in a table from one value to another and it works perfectly (not my code, I stole it). Do any of the amazing brains on this forum want to extend this function to add another parameter:
Such that my FindReplaceTable now has 2 Find columns (not just 1) with the 1 replace column and the function takes (DataTable, FindReplaceTable, DataTableColumn1, DataTableColumn2) where datatableColumn1 is the Find1 parameter and datatableColumn2 is the Find2 parameter. So if my code is 1234 && my name is BOB then I replace BOB with FRANK. The function as it stands can only replace all instances of BOB with FRANK, not just those instances of BOB where the code is 1234. Of course I can concatenate the code and name and do it that way but I would love to see how it can be done with an extended function - in doing so I might understand better how this function is actually working (and understanding is more important than knowing for me).
Such that my FindReplaceTable now has 2 Find columns (not just 1) with the 1 replace column and the function takes (DataTable, FindReplaceTable, DataTableColumn1, DataTableColumn2) where datatableColumn1 is the Find1 parameter and datatableColumn2 is the Find2 parameter. So if my code is 1234 && my name is BOB then I replace BOB with FRANK. The function as it stands can only replace all instances of BOB with FRANK, not just those instances of BOB where the code is 1234. Of course I can concatenate the code and name and do it that way but I would love to see how it can be done with an extended function - in doing so I might understand better how this function is actually working (and understanding is more important than knowing for me).
Power Query:
= (DataTable as table, FindReplaceTable as table, DataTableColumn as list) =>
let
//Convert the FindReplaceTable to a list using the Table.ToRows function
//so we can reference the list with an index number
FindReplaceList = Table.ToRows(FindReplaceTable),
//Count number of rows in the FindReplaceTable to determine
//how many iterations are needed
Counter = Table.RowCount(FindReplaceTable),
//Define a function to iterate over our list
//with the Table.ReplaceValue function
BulkReplaceValues = (DataTableTemp, n) =>
let
//Replace values using nth item in FindReplaceList
ReplaceTable = Table.ReplaceValue(
DataTableTemp,
//replace null with empty string in nth item
if FindReplaceList{n}{0} = null then "" else FindReplaceList{n}{0},
if FindReplaceList{n}{1} = null then "" else FindReplaceList{n}{1},
Replacer.ReplaceText,
DataTableColumn
)
in
//if we are not at the end of the FindReplaceList
//then iterate through Table.ReplaceValue again
if n = Counter - 1
then ReplaceTable
else @BulkReplaceValues(ReplaceTable, n + 1),
//Evaluate the sub-function at the first row
Output = BulkReplaceValues(DataTable, 0)
in
Output
Last edited by a moderator: