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. But something is related to all these columns, they all show values related to the total value of the installment program. I have another column that shows that number of installments, all i need to do is divide each of these 5 columns by the column that contains the number of installments.

 

I tried to use this: 

= Table.TransformColumns(NameOfPreviousStep, List.Transform({"Column1", "Column2"}, each {_, (inner) => inner / 45, type number}))

 

I changed this {"Column1", "Column2"} to the 5 columns that i need to divide

The problem occurs when a change the 45 to the column which contais the number of installment.

 

I think i have to do something like the "_" to represents each row for the Númber of Installment.

 

I can definily workaround this, but it seems so simple and usual that I would like to learn what i'm missing here.

 

I appreciate any help if possible.

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

     

     

4 Replies

  • 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

     

     

  • wdx223_Daniel's avatar
    wdx223_Daniel
    Community Champion
    4 years ago

    =Table.ReplaceValue(NameOfPreviousStep,each [NoOfInstallment],"",(x,y,z)=>x/y,{"Column1","Column2","Column3"})

    • shelzor's avatar
      shelzor
      New Member
      2 years ago

      I was looking for something similar and it works for me. But I don't know how it works. Could you explain this part: each [NoOfInstallment],"",(x,y,z)=>x/y?

       

      Also I would like to modify this that it only works when NoOfInstallment > 0, but when I modify to each [NoOfInstallment] > 0, the result is no division on any of the values.

  • JimDavis's avatar
    JimDavis
    New Member
    4 years ago

    Even simpler: unpivot the columns so the values you want to divide are all in the same column, then divide this column and re-pivot them.