Automating calculating row values based on header labels

Hi SaDev 

You can create a blank query and input the following code to advanced editor in power query

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bZFNFsAQDITvYm0hFf05i+f+12hVhuFZzBNJZ3w0ZyfOOzWhTp8O20dbQ1fx+Z8qTYVcaivU+tXFX2MaqVbqNZ7qAt06RTbSZsJEuaDje3FfOuG5vADX2Itj8uq6JuZZSAhE3e51Uw4Tra8+UqvrsWlazpBN2jhLwua36qYeIKW8", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Record = _t, Q1.1A = _t, Q1.1B = _t, Q1.1C = _t, Q1.2A = _t, Q1.2B = _t, Q1.2C = _t, Q1.3A = _t, Q1.3B = _t, Q1.3C = _t, Q2.1A = _t, Q2.1B = _t, Q2.1C = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Record", Int64.Type}, {"Q1.1A", Int64.Type}, {"Q1.1B", Int64.Type}, {"Q1.1C", Int64.Type}, {"Q1.2A", Int64.Type}, {"Q1.2B", Int64.Type}, {"Q1.2C", Int64.Type}, {"Q1.3A", Int64.Type}, {"Q1.3B", Int64.Type}, {"Q1.3C", Int64.Type}, {"Q2.1A", Int64.Type}, {"Q2.1B", Int64.Type}, {"Q2.1C", Int64.Type}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Record"}, "Attribute", "Value"),
    #"Split Column by Position" = Table.SplitColumn(#"Unpivoted Other Columns", "Attribute", Splitter.SplitTextByPositions({0, 4}, false), {"Attribute.1", "Attribute.2"}),
    #"Renamed Columns" = Table.RenameColumns(#"Split Column by Position",{{"Attribute.1", "Question"}, {"Attribute.2", "sections"}}),
    #"Added Custom1" = Table.AddColumn(#"Renamed Columns", "Custom", each if [sections]="C" then {"C","D","E"} else null),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom1", "Custom"),
    #"Replaced Value" = Table.ReplaceValue(#"Expanded Custom",each [sections],each if [Custom]<>null and [Custom]<>[sections] then [Custom] else [sections],Replacer.ReplaceValue,{"sections"}),
    #"Removed Columns" = Table.RemoveColumns(#"Replaced Value",{"Custom"}),
    #"Added Custom" = Table.AddColumn(#"Removed Columns", "Custom", each let a=[Record],
b=[Question],
c= List.Sum(Table.SelectRows(#"Removed Columns",each [Record]=a and [Question]=b and ([sections]="A" or [sections]="B"))[Value]),
d=if [Value]=0 then 0 else  if [Value]=1 then c else c-2
in if [sections]="D" then c else if [sections]="E" then d else [Value]),
    #"Removed Columns1" = Table.RemoveColumns(#"Added Custom",{"Value"}),
    #"Merged Columns" = Table.CombineColumns(#"Removed Columns1",{"Question", "sections"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Merged"),
    #"Pivoted Column" = Table.Pivot(#"Merged Columns", List.Distinct(#"Merged Columns"[Merged]), "Merged", "Custom")
in
    #"Pivoted Column"

Output

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.