Transform multiple equal columns (branched Forms) into rows with values and corresponding attributes

Hi Anonymous ,

you can try this code.

Asusmption is, that each question ends with "Mx?", for example "What is working well in M1?". There is a split of last 3 characters.

Basicaly, each question is changed from: "What is working well in M1?" to "What is working well in the module?"

Code should work with random number of questions.

let
    Source = Excel.Workbook(File.Contents("path to excel file"), null, true),
    Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Promoted Headers", {"ID", "Department"}, "Attribute", "Value"),
    #"Split Column by Position" = Table.SplitColumn(#"Unpivoted Other Columns", "Attribute", Splitter.SplitTextByPositions({0, 3}, true), {"Question", "Module"}),
    #"Add question mark" = Table.TransformColumns(#"Split Column by Position",{{"Question", each _ & "the module?", type text}}),
    #"Delete question mark" = Table.ReplaceValue(#"Add question mark","?","",Replacer.ReplaceText,{"Module"}),
    #"Pivoted Column" = Table.Pivot(#"Delete question mark", List.Distinct(#"Delete question mark"[Question]), "Question", "Value"),
    #"Replace null with empty" = Table.ReplaceValue(#"Pivoted Column",null,"",Replacer.ReplaceValue,List.Difference(Table.ColumnNames(#"Pivoted Column"),{"ID","Department","Module"}))
in
    #"Replace null with empty"

Hi, Anonymous 

Try this:

// output
let
    Source = Table.FromRecords(Json.Document(Binary.Decompress(Binary.FromText("rZJBSwMxEIX/SthzCybRtfXiwUXYw1oEQUQ8xO60DU2TkM22iPS/u1lLnYGy7cHje3kD37zJ+3f24Ey7sTy7y8oiGx2k6GQBXoW4ARuPtky2Y1+uZSu1BbYAqD/VfM0CLFWotV2yit8f49dd/HWlItMN27mwTu87MIZpS3I3KGddHMzmXXbmvQuxtTpqaNjCBVbDFozzCbbXeOL2AmjxF58MQaPc9Bw0yvKry6i7kf0InYTje5Qv5A5PDvdsW2NQnVjmVKY63qAh+1Zi/MzHZcHJdskVxO33SLbsbcIqMOujtsrO4d+A0eyEvk6pTIRJEzSJ0WYegora2YbQ4UrSv634byWS/NLkCuLmB1cSd6hkebJkebpkme0/fgA=", BinaryEncoding.Base64),Compression.Deflate))),
    pmhd = Table.PromoteHeaders(Source),
    toList = Table.ToList(pmhd, each Table.FromRows(List.Transform(fx(List.Split(List.Skip(_,2),4)),(lst)=> List.FirstN(_,2)&lst ))),
    cmb = Table.Combine(toList)
in
    cmb

// fx
(lsts)=>
let
    //lsts = toList{0},
    pos = List.Split(List.Positions(lsts),1),
    zip = List.Zip({lsts,pos}),
    trans = List.Transform(
                zip, 
                each let nlst= List.Combine(_) 
                     in List.RemoveLastN(
                             List.ReplaceValue(
                                 nlst,
                                 "Yes", 
                                 Number.ToText(List.Last(nlst)+1,"Module 0"),
                                 Replacer.ReplaceValue
                             )
                        )
            )
in
    trans