Replace values at the same time

[Krokolv]

Hi M experts,

I want to add a multiple columns replace values, following this example, into one single step:

= Table.ReplaceValue(#"Changed Type",each [ZT04],each [ZT04]*100 ,Replacer.ReplaceValue,{"ZT04"})

So basically I want to apply this massively to 7 columns in one step. Do you know a way?

Thanks in Advance.
Marco

 

[JGordon11]

= Table.TransformColumns(#"Changed Type", let cols = {"col2", "col4", "col6", "col7", "col9", "col10", "col11"} in List.Zip({cols, List.Repeat({each _*100},List.Count(cols))}))

change {"col2", "col4", "col6", "col7", "col9", "col10", "col11"} to the names of the columns that you want to transform

 

[Krokolv]

Woow, works like a charm I will save this code forever :D I see the potential here for other operations as well. I am also searching for something similar, but in this case I would have to each row in 2 different columns:

= Table.AddColumn(#"Multiplication", "Dif ZT04", each if [Sales Document] = "ZFUT" or "ZCQ" then [ZT04] - [Modeller.ZT04] else 1)
= Table.AddColumn(#"Added Conditional Column", "Dif ZT37", each  [ZT37] - [Modeller.ZT37])

I have like 10 steps like this, do you see a way of putting a lot of this custom columns into a single step? sorry for pushing on your expertise.

 

[JGordon11]

Here's an example that may get you started

let
    Source = Excel.CurrentWorkbook(){[Name="Table5"]}[Content],
    ZTs = {"ZT1","ZT2","ZT3","ZT4"},
    cols = List.Zip({ZTs, List.Transform(ZTs, each "Modeller." & _)}),
    Result = List.Accumulate(cols, Source, (s,c)=> Table.AddColumn(s, "Dif " & c{0}, each Record.Field(_, c{0}) - Record.Field(_,c{1})))
in
    Result

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V
1	ZT1	ZT2	ZT3	ZT4	Modeller.ZT1	Modeller.ZT2	Modeller.ZT3	Modeller.ZT4		ZT1	ZT2	ZT3	ZT4	Modeller.ZT1	Modeller.ZT2	Modeller.ZT3	Modeller.ZT4	Dif ZT1	Dif ZT2	Dif ZT3	Dif ZT4
2	45	17	13	26	14	18	3	24		45	17	13	26	14	18	3	24	31	-1	10	2
3	10	41	28	34	47	15	23	4		10	41	28	34	47	15	23	4	-37	26	5	30
4	53	16	37	5	56	22	12	38		53	16	37	5	56	22	12	38	-3	-6	25	-33
5	39	7	30	44	1	48	43	49		39	7	30	44	1	48	43	49	38	-41	-13	-5
6	54	27	19	51	52	20	21	6		54	27	19	51	52	20	21	6	2	7	-2	45
7	8	31	42	50	55	36	9	33		8	31	42	50	55	36	9	33	-47	-5	33	17
8	29	32	2	40	11	25	35	46		29	32	2	40	11	25	35	46	18	7	-33	-6
9
Sheet4

 

[Krokolv]

Thanks for the hint! Always a good help!

 

[XL Pro]

Hi M experts,

I want to add a multiple columns replace values, following this example, into one single step:

= Table.ReplaceValue(#"Changed Type",each [ZT04],each [ZT04]*100 ,Replacer.ReplaceValue,{"ZT04"})

So basically I want to apply this massively to 7 columns in one step. Do you know a way?

Thanks in Advance.
Marco

Although a bit odd in syntax, it can be done with the Table.ReplaceValue

= Table.ReplaceValue(#"Changed Type", each true, null , (x,y,z)=> if y then x * 100 else x, {"ZT04","ZT05"})

Also, as an alternative, you can replace the List.Zip functions with a single List.Transform:

= Table.TransformColumns(#"Changed Type", List.Transform({"ZT04", "ZT05"}, each {_, each _*100}))

= List.Transform(ZTs, each {_,"Modeller." & _})