Slow transformation - List.Buffer or Table.Buffer as possible solution?

If not already, I would first confirm that it is not the merge step slowing things down (e.g., try it without those steps). In any case, here is an example of how to incorporate List.Buffer to speed things up (more buffering may be possible inside the function too, but try this first). It uses a different function to do the conversion. You can use it or adapt your code with a similar approach. Note that the List is buffered and the function is defined within the same query. List.Buffer and Table.Buffer only help when an object is used many times within the same query.

Just create a blank query, open the Advanced Editor, and replace the code there with the below, to see how it works. I tested it on 3 million rows and it took <1 min.

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnNTitWJVjIwAFNuIABmWZibOTo5uyrFxgIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Hex = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Hex", type text}}),
ReplaceList = List.Buffer(List.Zip({{"0".."9"}&{"A".."F"}, {0..15}})),
fxHexDec = (inputtext as text)=>
let
inputlist = List.Reverse(List.ReplaceMatchingItems(Text.ToList(inputtext), ReplaceList)),
Result =
let
b = List.Reverse(List.Positions(inputlist)),
c = List.Sum(List.Transform(b, each inputlist{_} * Number.Power(16, _)))
in
c
in
Result,
AddColumn = Table.AddColumn(#"Changed Type", "Dec", each fxHexDec([Hex]), Int64.Type)
in
AddColumn

Pat