1

Below sample table has a Section with Questions and the possible answers that each questions can have, all in the single row.. Each question can have between 1 to 4 answers.

Sec Name Question Text Answer 1 Answe1 Autofail Answer 1 Score Answer 2 Answe2 Autofail Answer 2 Score Answer 3 Answer3 Autofail Answer3 score Answer 4 Answer4 Autofail Answer4 score
Customer Satisfaction Does the Bill The syllabus lists points FALSE 1 The syllabus lists FALSE 1 The wrong column FALSE 0 There is no total FALSE 0
Customer Satisfaction Do Invoicing Term Yes FALSE 5 No FALSE 0
Customer Satisfaction Have all appropriate Line There is an ISBN FALSE 1 There is no ISBN FALSE 0 The ISBN number is listed FALSE 0

I need to import the data in to Salesforce database and I need to normalize the structure like each row can have Section, Question and One Answer and its attribute like AutoFail and Score. Below is the example of the normalized table with each answers in the row

Sec Name Question Text Answer Answe Autofail Answer Score
Customer Satisfaction Does the Bill The syllabus lists points FALSE 1
Customer Satisfaction Does the Bill The syllabus lists FALSE 1
Customer Satisfaction Does the Bill The wrong column FALSE 0
Customer Satisfaction Does the Bill There is no total FALSE 0
Customer Satisfaction Do Invoicing Term Yes FALSE 5
Customer Satisfaction Do Invoicing Term No FALSE 0
Customer Satisfaction Have all appropriate Line There is an ISBN FALSE 1
Customer Satisfaction Have all appropriate Line There is no ISBN FALSE 0
Customer Satisfaction Have all appropriate Line The ISBN number is listed FALSE 0

I am not so familiar with Powerquery or pivot table. Can anyone please help me how can I normslize this.. Any help is greatly appreciated

user4912134
  • 1,003
  • 5
  • 18
  • 47

3 Answers3

3

Above answers work fine

The fastest method which also preserves row order that I've found is

let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
leading=2, groupsof=3,
#"Added Custom" = Table.AddColumn(Source, "Custom", each List.Split( List.RemoveFirstN(Record.ToList( _),leading), groupsof) ),
#"Added Custom0" = Table.AddColumn(#"Added Custom", "Custom0", each Text.Combine(List.FirstN(Record.ToList(_),leading),"|")),
#"Removed Other Columns" = Table.SelectColumns(#"Added Custom0",{"Custom0", "Custom"}),
#"Expanded Custom" = Table.ExpandListColumn( #"Removed Other Columns", "Custom"),
#"Extracted Values" = Table.TransformColumns(#"Expanded Custom", {"Custom", each Text.Combine(List.Transform(_, Text.From), "|"), type text}),
#"Merged Columns" = Table.CombineColumns(#"Extracted Values",{"Custom0", "Custom"},Combiner.CombineTextByDelimiter("|", QuoteStyle.None),"Custom"),
#"Split Column by Delimiter" = Table.SplitColumn(#"Merged Columns", "Custom", Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv), List.FirstN(Table.ColumnNames(Source),leading+groupsof))
in #"Split Column by Delimiter"
horseyride
  • 17,007
  • 2
  • 11
  • 22
1

Since you've tagged this question appropriately, one of the experts will probably provide shorter syntax

let
    TableOne = Table.AddIndexColumn(Table.UnpivotOtherColumns(Excel.CurrentWorkbook(){[Name="Table1"]}[Content], {"Sec Name", "Question Text"}, "Attribute", "Value"), "Index", 1, 1, Int64.Type),
    TableTwo = Table.AddIndexColumn(TableOne, "Index.1", 0, 1, Int64.Type),
    MergeOne = Table.AddIndexColumn(Table.ExpandTableColumn(Table.NestedJoin(TableOne, {"Index"}, TableTwo, {"Index.1"}, "Table2", JoinKind.Inner), "Table2", {"Value"}, {"Value.1"}), "Index.1", 3, 1, Int64.Type),
    Output = Table.RenameColumns(Table.RemoveColumns(Table.SelectRows(Table.ExpandTableColumn(Table.NestedJoin(MergeOne, {"Index.1"}, TableOne, {"Index"}, "Table2", JoinKind.LeftOuter), "Table2", {"Value"}, {"Value.2"}), each Number.Mod([Index],3) = 1),{"Attribute", "Index", "Index.1"}),{{"Value", "Answer"}, {"Value.1", "Answer Autofail"}, {"Value.2", "Answer Score"}})
in
    Output

From the UI perspective, this involves all the same steps as your last question, plus an additional step of merging (a left-outer join) of the first merge with the first table, and then filtering that result for values where the modulus of the current index and 3 is equal to 1

Spectral Instance
  • 1,971
  • 2
  • 3
  • 6
1

You can do it with Excel functions, assuming O365. Put the following formula in A7:

=LET(in,A2:N4, gr,3, q,4, seq,SEQUENCE(q,,1,0), h,{"Sec Name","Question Text",
 "Answer","Answe Autofail","Answer Score"}, PIVOT, LAMBDA(x,
  HSTACK(REPT(INDEX(x,{1,2}),seq),WRAPROWS(DROP(x,,2),gr))),
 y,REDUCE(h,SEQUENCE(ROWS(in)),LAMBDA(ac,i,VSTACK(ac,PIVOT(INDEX(in,i,))))),
 z, TAKE(TAKE(y,,-gr),,1), FILTER(y, z<>""))

Here is the output: output

We use REDUCE/VSTACK pattern. Check my answer to this question for more information: how to transform a table in Excel from vertical to horizontal but with different length. The user LAMBDA function PIVOT does the pivot for each row on each iteration of REDUCE. The names: gr (number of columns per answer's information) and q (number of questions we have), control how to organize the information. The last line is just to remove empty answers.

David Leal
  • 6,373
  • 4
  • 29
  • 56