Forum Discussion
Helper IIMultiply multiple columns at once based on list of columns
Hi Friends of Power Query,
As per some new data I worked on, I wanted to develop some standard codes for batch data type conversion. First I wanted to convert text numbers to decimal numbers only in specific named columns. Therefore I use the result of a List.Select and it works well.
Secondly I want to multiply the values. I found a way with a statement that multiplies multiple columns at once, but I must hardcode the names.
So I thought of using the buffered list as well, like in the data type conversion. But here I fail and do not find a way around. The error I get is 'The operator * cannot be applied to types text and number'
MulitplyAdvanced = Table.TransformColumns(DataTypeAuto, List.Transform(caLst, each {_ * 10, type number}))
Are there any insights from the community how to achieve a batch multiplication of multiple columns based on a list of column names from List.Select ?
I am aware that a possible solutions is to transpose the columns, multiply and transpose back. But I want to achieve it without that step, as this is in some cases better to implement.
A file with sample data is posted here: https://c.gmx.net/@324888734501700174/va6nzTjX1B-Qas59R5ZBVw
Thank you for your thoughts and directions.
Best regards, Andreas
Table.TransformColumns( DataTypeAuto, List.Transform(caLst, (x) => {x, (w) as number => Number.From(w) * 10}) )Hi Goodkat
This is an interesting question and can be done by List.Accumulate using the following formula
= List.Accumulate({"2025 - 1", "2025 - 2"},DataTypeAuto,(a,b)=>
Table.TransformColumns(a,{b, each _ * 10, type number}))Please find your solved file in the attached and dor further details, se my video as bellow
https://www.youtube.com/watch?v=G8PRbWuDcmQ&t=98s&pp=ygUWb21pZGJpIGxpc3QuYWNjdW11bGF0ZQ%3D%3D
6 Replies
- AlienSx
Super User
Table.TransformColumns( DataTypeAuto, List.Transform(caLst, (x) => {x, (w) as number => Number.From(w) * 10}) ) - Omid_Motamedise
Hi Goodkat
This is an interesting question and can be done by List.Accumulate using the following formula
= List.Accumulate({"2025 - 1", "2025 - 2"},DataTypeAuto,(a,b)=>
Table.TransformColumns(a,{b, each _ * 10, type number}))Please find your solved file in the attached and dor further details, se my video as bellow
https://www.youtube.com/watch?v=G8PRbWuDcmQ&t=98s&pp=ygUWb21pZGJpIGxpc3QuYWNjdW11bGF0ZQ%3D%3D
GoodkatDear Omid,
absolutely cool! Thankfully AlienSx and you provided cutting edge solutions. Short, precise & effective. I put your solution also into my knowledge base and I read now more about List.Accumulate to fully leverage this new PQ command from your proposal. It seems powerful.
Thank you & Best regards, Andreas
- Goodkat
Dear AlienSx,
thank you so much for your reply! It worked after a little change. The (w) as number declaration throws 'error' when facing 'null' values. So I tried; do not declare (w) or use (w) as any. Both works. Furthermore with the 'Number.From' I could also skip any previous conversion step!
Your solution is so cool! I will put it to my list of standard solutions!Stay safe & healthy!
Best regards, Andreas (Brunswick, Germany)
- AlienSx
Hello, Andreas
well, nulls are not a problem: use (w) as nullable number and error disappears.
- Goodkat
Dear AlienSx,
Super! Thank you for that clarification. I now read about the possible variable declarations and their add-ons in Power Query. The 'as nullable' addition I will put to my notes to never step into that trap again. A super swift solution has been found!
Best regards, Andreas