Automating calculating row values based on header labels

[SaDev]

Hello, 

I have survey data I want to manipulate. There are 40 questions across 9 sections and each question has 3 repeating subsections. For example, Section 1 has 4 questions (Q1.1X, Q1.2X, Q1.3X, Q1.4X) and each question has 3 parts - A, B, C.  All data values are numeric. I want to add a custom column with sum of the values from parts A and B of each question.  And then based on the response in part C {0,1,2} return a value of 0, [sum A + B], or [sum A + B] -2.  Then repeat for each of the 40 questions.

Examples,

for Q1.1, where Q1.1A = 4, Q1.1B = 4, Q.1.1C =1

add custom column named Q1.1D = [Q1.1A] + [Q1.1B] = 4 + 4

because Q1.1C = 1, add custom column named Q1.1E = [Q1.1D]

for Q1.2, where Q1.2A = 4, Q1.2B = 5, Q1.2C =2

add custom column named Q1.2D = [Q1.2A] + [Q1.2B] = 4 + 5

because Q1.2C = 2, add custom column named Q1.2E = [Q1.2D] - 2

for Q2.1, where Q2.1A = 0, Q2.1B = 0, Q.2.1C =0

add custom column named Q2.1D = [Q2.1A] + [Q2.1B] = 0 + 0

because Q2.1C = 0, add custom column named Q2.1E = 0

Repeat for each question with corresponding column names.

Record	Q1.1A	Q1.1B	Q1.1C	Q1.2A	Q1.2B	Q1.2C	Q1.3A	Q1.3B	Q1.3C	Q2.1A	Q2.1B	Q2.1C
1	4	4	1	4	5	2	1	3	1	0	0	0
2	4	3	1	1	5	2	4	2	2	2	5	2
3	1	1	2	2	3	2	2	4	2	3	4	1
4	4	4	2	3	4	2	5	1	2	0	0	0
5	2	2	1	5	5	2	1	2	1	5	1	2
6	4	5	2	4	5	2	2	4	1	2	3	2
7	3	4	1	1	1	1	4	5	0	4	2	1
8	2	3	1	2	1	1	3	1	0	4	1	1
9	3	5	1	1	4	1	2	3	1	2	2	1
10	5	2	1	3	4	1	3	4	1	1	2	1

[Anonymous · Accepted solution]

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.