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.           

m_dekorte's avatar
m_dekorte
Icon for Resident Rockstar rankResident Rockstar
3 years ago

Hi Anonymous,

 

Alternatively, you could give something like this a go.

Copy this code into a new blank query

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlLSUTIEYggdqxOtZAwVAWFjpdhYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t]),
    ChType = Table.TransformColumnTypes(Source,{{"Column1", Int64.Type}, {"Column2", Int64.Type}, {"Column3", Int64.Type}, {"Column4", Int64.Type}}),
    ReplaceValue = List.Accumulate( 
        List.Skip( Table.ColumnNames(Source), 1),
        ChType, 
        (s, a) => Table.ReplaceValue( s, 
            each Record.Field(_, a), 
            each Record.Field(_, a) * Record.Field(_, "Column1"),
            Replacer.ReplaceValue, {a}
        )
    ),
    RestoreType = Value.ReplaceType( ReplaceValue, Value.Type( ChType ))
in
    RestoreType

 

There are a number of transformations that can't retain type, above type is restored as it was before.

Here's a link to a good post that deals with rounding in M 

 

Ps. Please mark this answer as solution when it helped you to resolve your question, thanks!