Forum Discussion

gadao's avatar
gadao
Frequent Visitor
4 years ago
Solved

How to Divide multiple columns at once by another column

I'm creating a fact table based on another fact table and i need to do a simple thing but i'm not getting it.   I have something like 5 values columns, each columns bring up aspects of the value. B...
Need Help
  • jennratten's avatar
    jennratten
    4 years ago

    Hi - this is how it can be done...

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTICYmMgNjFVitWholAsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Installments = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", Int64.Type}, {"Column2", Int64.Type}, {"Column3", Int64.Type}, {"Installments", Int64.Type}}),
    ColumnsToDivide = {"Column1", "Column2", "Column3"},
    Test = Table.FromRecords(
        Table.TransformRows(
        #"Changed Type",
        (r) => 
        Record.TransformFields(
            r,
            List.Transform(
                ColumnsToDivide,
                each {_, each _ / r[Installments]}
            )
        )
    ),
    Value.Type(#"Changed Type")
)

in
    Test

     

     

jennratten's avatar
jennratten
Super User
4 years ago

Hi - this is how it can be done...

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTICYmMgNjFVitWholAsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Installments = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", Int64.Type}, {"Column2", Int64.Type}, {"Column3", Int64.Type}, {"Installments", Int64.Type}}),
    ColumnsToDivide = {"Column1", "Column2", "Column3"},
    Test = Table.FromRecords(
        Table.TransformRows(
        #"Changed Type",
        (r) => 
        Record.TransformFields(
            r,
            List.Transform(
                ColumnsToDivide,
                each {_, each _ / r[Installments]}
            )
        )
    ),
    Value.Type(#"Changed Type")
)

in
    Test