Forum Discussion
Sum Columns Based on Values in Other Columns
Hi folks, I want to add a calculated column, which sums up columns based on the value in adjacent columns. They are different types of leave over a fortnightly period. So for example if column D0...
Insert a custom column with following formula
= Number.From([D01Leave Type]="USL")*[D01Leave Hours]+Number.From([D02Leave Type]="USL")*[D02Leave Hours]+Number.From([D03Leave Type]="USL")*[D03Leave Hours]See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlfSUQoN9gGSxnqmQDLEE8SGicbqRCsZANl+fn5AEpM1uOTN9SzgvkGwB8qN5uDwDIhwUYqNBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"D01Leave Hours" = _t, #"D01Leave Type" = _t, #"D02Leave Hours" = _t, #"D02Leave Type" = _t, #"D03Leave Hours" = _t, #"D03Leave Type" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"D01Leave Hours", Int64.Type}, {"D01Leave Type", type text}, {"D02Leave Hours", type number}, {"D02Leave Type", type text}, {"D03Leave Hours", type number}, {"D03Leave Type", type text}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Total", each Number.From([D01Leave Type]="USL")*[D01Leave Hours]+Number.From([D02Leave Type]="USL")*[D02Leave Hours]+Number.From([D03Leave Type]="USL")*[D03Leave Hours], type number) in #"Added Custom"
Insert a custom column with following formula
= Number.From([D01Leave Type]="USL")*[D01Leave Hours]+Number.From([D02Leave Type]="USL")*[D02Leave Hours]+Number.From([D03Leave Type]="USL")*[D03Leave Hours]See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlfSUQoN9gGSxnqmQDLEE8SGicbqRCsZANl+fn5AEpM1uOTN9SzgvkGwB8qN5uDwDIhwUYqNBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"D01Leave Hours" = _t, #"D01Leave Type" = _t, #"D02Leave Hours" = _t, #"D02Leave Type" = _t, #"D03Leave Hours" = _t, #"D03Leave Type" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"D01Leave Hours", Int64.Type}, {"D01Leave Type", type text}, {"D02Leave Hours", type number}, {"D02Leave Type", type text}, {"D03Leave Hours", type number}, {"D03Leave Type", type text}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Total", each Number.From([D01Leave Type]="USL")*[D01Leave Hours]+Number.From([D02Leave Type]="USL")*[D02Leave Hours]+Number.From([D03Leave Type]="USL")*[D03Leave Hours], type number)
in
#"Added Custom"Hi Vijay_A_Verma,
Thank you so much for this it has really helped me! Am I correct in thinking that Number.From returns a logical 1 for true 0 for false. The documentation on the function is not very clear. Thanks so much.
Yes, it will return 1 and 0 for true and false.
Thanks again!