Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 D01Leave Type or D02Leave Type or D03Leave Type contains the text “USL” I want to sum the appropriate number of hours from the D01Leave Hours and D02Leave Hours and D03Leave Hours columns. Which would result in the output below. Perhaps this would be better dealt with by a measure and I am open to this. Any help is appreciated. Thanks very much.
D01Leave Hours | D01Leave Type | D02Leave Hours | D02Leave Type | D03Leave Hours | D03Leave Type | Calculated Column total USL |
7 | USL | 3.5 | TIL | 7 | USL | 14 |
0 | NNN | 0 | NNN | 0 | NNN | 0 |
0 | NNN | 0 | NNN | 0 | NNN | 0 |
0 | NNN | 0 | NNN | 0 | NNN | 0 |
0 | NNN | 0 | NNN | 0 | NNN | 0 |
0 | NNN | 0 | NNN | 0 | NNN | 0 |
0 | NNN | 7.8 | USL | 7.8 | USL | 15.6 |
0 | NNN | 0 | NNN | 0 | NNN | 0 |
0 | NNN | 0 | NNN | 0 | NNN | 0 |
0 | NNN | 0 | NNN | 0 | NNN | 0 |
0 | NNN | 0 | NNN | 7.5 | PXD | 0 |
Solved! Go to Solution.
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!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.