Two custom M functions

[JGordon11]

I have written two functions below to do seemingly simple tasks. One, TableReplaceItem, replaces a single element in a table identified by row and column number. The other, RowMultiply, multiplies each element in a table row by a number. I think there must be simpler and more efficient ways to code these for those purposes. Also the functions below would fail in the off chance that the input table has columns named the same as the interim added index and custom columns. If someone knows how to code these better, I would like to learn.

(tblInput as table, numRow as number, numCol as number, anyReplaceWith) as table =>
let
    lstTCN = Table.ColumnNames(tblInput),
    strCol = lstTCN{numCol},
    tbl1 = Table.AddIndexColumn(tblInput,"myIndex123456",0,1,Int64.Type),
    tbl2 = Table.AddColumn(tbl1, "myCustom1234", each if [myIndex123456] = numRow then anyReplaceWith else Table.Column(tbl1,strCol){[myIndex123456]}),
    tbl3 = Table.RemoveColumns(tbl2,{strCol, "myIndex123456"}),
    tbl4 = Table.RenameColumns(tbl3,{{"myCustom1234", strCol}}),
    tblResult = Table.ReorderColumns(tbl4,lstTCN)
in
    tblResult

So for the blue table, Data, TableReplaceItem(Data,2,2,9999) results in:

Pivot and Split.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Column1	Column2	Column3	Column4	Column5	Column6		Column1	Column2	Column3	Column4	Column5	Column6
2	1	7	13	19	25	31		1	7	13	19	25	31
3	2	8	14	20	26	32		2	8	14	20	26	32
4	3	9	15	21	27	33		3	9	9999	21	27	33
5	4	10	16	22	28	34		4	10	16	22	28	34
6	5	11	17	23	29	35		5	11	17	23	29	35
7	6	12	18	24	30	36		6	12	18	24	30	36
Sheet5

and

(tblInput as table, numRow as number, numMultiplier as number) as table =>
let
    tbl1 = Table.AddIndexColumn(tblInput,"myIndex12345",0,1,Int64.Type),
    lstListToMultiply = Record.ToList(tbl1{numRow}),
    rcdRecordToFind = Record.FromList(lstListToMultiply,Table.ColumnNames(tbl1)),
    tblConvertToTable = Table.FromList(lstListToMultiply, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    tblMultiply = Table.AddColumn(tblConvertToTable, "Multiplication", each [Column1] * numMultiplier, type number)[Multiplication],
    rcdRecordToOutput = Record.FromList(tblMultiply,Table.ColumnNames(tbl1)),
    tbl2 = Table.ReplaceMatchingRows(tbl1, {rcdRecordToFind,rcdRecordToOutput}),
    tblResult = Table.RemoveColumns(tbl2,{"myIndex12345"})
in
    tblResult

RowMultiply(Data,3,.01) results in the green table

Pivot and Split.xlsm
	A	B	C	D	E	F								N	O	P	Q	R	S	T
1	Column1	Column2	Column3	Column4	Column5	Column6									Column1	Column2	Column3	Column4	Column5	Column6
2	1	7	13	19	25	31									1	7	13	19	25	31
3	2	8	14	20	26	32									2	8	14	20	26	32
4	3	9	15	21	27	33									3	9	15	21	27	33
5	4	10	16	22	28	34									0.04	0.1	0.16	0.22	0.28	0.34
6	5	11	17	23	29	35									5	11	17	23	29	35
7	6	12	18	24	30	36									6	12	18	24	30	36
Sheet5

 

[Bo_Ry]

Please try

(tblInput as table, numRow as number, numCol as number, anyReplaceWith) as table =>
let
    ListCol = Table.ToColumns(tblInput),
    Col = List.FirstN(ListCol{numCol},numRow ) &{anyReplaceWith} & List.Skip(ListCol{numCol},numRow+1),
    NewListCol = List.FirstN(ListCol,numCol) & {Col} & List.Skip(ListCol,numCol+1),
    tblResult = Table.FromColumns(NewListCol,Table.ColumnNames(tblInput))
in
    tblResult

and

(tblInput as table, numRow as number, numMultiplier as number) as table =>
let
    torow = Table.ToRows(tblInput),
    rows = List.FirstN(torow,numRow) & {List.Transform(torow{numRow},each _*numMultiplier)}  & List.Skip(torow,numRow+1),
    tblResult = Table.FromRows(rows,Table.ColumnNames(tblInput))
in
    tblResult

 

[JGordon11]

Thanks - much improved!