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

     

     

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.