Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

multiply columns by one column, problem with data type changing

Hi All   I am transforming a bunch of columns by multiplying them on row level by values in a column: Ex in tabel below I multiply Column2, Column3 & Column4 by Column1 (all columns 1-4 are data...
Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi Anonymous ,

     

    You could use the Table.TransformColumnTypes function to change all the data types to decimal number.

    = let
output=Table.FromRecords(Table.TransformRows(#"Changed Type", (x) => Record.TransformFields(x, List.Transform({"Column2", "Column3", "Column4"}, (y) => {y, each x[Column1] * _}))))
in 
Table.TransformColumnTypes(output,List.Transform(Table.ColumnNames(output), each {_, type number}))

    Table.TransformColumnTypes - PowerQuery M | Microsoft Learn

    List.Transform - PowerQuery M | Microsoft Learn

     

    Best Regards,

    Stephen Tao

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.           

slorin's avatar
slorin
Super User
3 years ago

Hi, 

Try this solution with Table.ReplaceValue

 

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("HYyxDQAgDMN+6ZyhNEDKLYj/36DqZCm2cq8JyTQYsTmKC6FZ3NhOe7hlOFYbnsKEnygOLKqDgKsGQVI/eXYXSnvvAw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t]),
Number_Type = Table.TransformColumnTypes( Source, {{"Column1", type number}, {"Column2", type number}, {"Column3", type number}, {"Column4", type number}}),
Replace_Value = Table.ReplaceValue( Number_Type, each [Column1], "", (x,y,z) => x*y, {"Column2", "Column3", "Column4"}),
Restore_Type = Value.ReplaceType( Replace_Value, Value.Type( Number_Type ))
in
Restore_Type

 Stéphane