Forum Discussion
Find Replace values only on first row of source table
I need to find and replace the headers of my Source Table in Power query I am able to do this with BulkReplace.
If I demote the headers and run BulkReplace then promote the headers, I get what I want.
But this Finds/Replaces on the entire table.
I know I can transpose the source file and then run BulkReplace on the first column then transpose again and this will give me what I want as well
But both these methods seem inefficient/resource heave if all I want to do is run BulkReplace on the first row
Is there a way to run BulkReplace on only the first row?
Or is there a different way to loop over the first row and find replace?
Thanks
sumAppHeaders (Find Replace Table)
BulkReplace
BulkReplaceStepHeaders = fBulkReplaceStep(#"Demoted Headers", sumAppHeaders, Table.ColumnNames(#"Demoted Headers"))let BulkReplace = (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.ReplaceValue,
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
in
BulkReplace
You can use a combination of Table.FirstN and Table.Skip to do this like:
BulkReplaceStepHeaders = fBulkReplaceStep(Table.FirstN(table, 1), ...) & Table.Skip(table, 1)Just replace table with the name of the previous step and ... with the other args to the bulk replace function
4 Replies
- artemus
Microsoft Employee
You can use a combination of Table.FirstN and Table.Skip to do this like:
BulkReplaceStepHeaders = fBulkReplaceStep(Table.FirstN(table, 1), ...) & Table.Skip(table, 1)Just replace table with the name of the previous step and ... with the other args to the bulk replace function
- AnonymousNot applicable
So this worked
BulkReplaceStepHeaders = fBulkReplaceWhole(Table.FirstN(#"Changed Type", 1),sumAppHeaders,Table.ColumnNames(#"Changed Type")) & Table.Skip(#"Changed Type", 1),But I can't quit following what is going on. Could you explain a bit as this technique will be useful in other contatexts
Thanks again
- artemus
Microsoft Employee
The table is cut into 2 parts. The first part is the just the first row via Table.FirstN function, the second is the remaining rows in the table via Table.Skip function. The Bulk replace function is called on just the first 1-row table. Finally the concat operdator & is used to glue the tables back together.