Find/Replace in Bulk in 3 steps Using Table.TransformColumns() & List.ReplaceMatchingItems() P.Query

Опубликовано: 28 Июнь 2026
на канале: Josh_Excel

434

#powerquery #power_query

1. Create/import two tables:
• T1 for the Data to be modified
• T2 for the Find/Replace list
2. Use Table.ToRows() function on T2 to make a list of lists for each Find/Replace pair (F_R)
3. Use Table.TransformColumns() and List.ReplaceMatchingItems() functions
• Values need to be put into a list by using curly braces { } to work with list functions
• To get the single value out of the list, use {0}

Advanced Editor:

let
Source = null,
T1 = #table({"Col1", "Col2", "Col3"},{{"A", "B", "C"}, {"D", "E", "F"}, {"G", "H", "I"}}),
T2 = #table({"Find", "Replace"},{{"A", "Apple"}, {"B", "Banana"}, {"C", "Carrot"}, {"D", "Donut"}, {"E", "Éclair"}, {"F", "Fudge"}, {"G", "Grape"}, {"H", "Ham"}, {"I", "Ice Cream"}}),
F_R = Table.ToRows(T2),
Custom1 = Table.TransformColumns(T1,{}, each List.ReplaceMatchingItems({_}, F_R){0})
in
Custom1