cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Regular Visitor

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

Hi all! I hope someone can help me out with best practice ways to deal with non-ideal data input from Microsoft Forms. 

The tricky part of the Forms data is that the survey is evaluating 7 different modules, and so the same identical questions (4 of them in this dummy data) are asked 7 times. The Forms is branched, so that the users only have to answer for those modules that they are using and are able to give feedback to. This is good for data collection, but not ideal for data analysis (at least with my current experience in power query and dax).

The resulting data from the forms (excel file) will look like the table below, except that it contains the questions 7 times rather than 2.

IDDepartmentDo you have feedback regarding M1?What is working well in M1?What is not working well in M1?Opportunities for development for M1?Do you have feedback regarding M2?What is working well in M2?What is not working well in M2?Opportunities for development for M2?
1ITNo   YesM2-Q1-ID1M2-Q2-ID1M2-Q3-ID1
2FinanceNo   No   
3

Operations

YesM1-Q1-ID3M1-Q2-ID3M1-Q3-ID3YesM2-Q1-ID3M2-Q2-ID3M2-Q3-ID3

 

I want the end-result to look like the table below. That means:
- The header "do you have feedback regarding module x?" will be replaced by "module number", and for those that have "Yes" as an answer, the input in the row is the actual module number. 
- The question headers that were specific for each module (there are 7 of them in total) are changed to general question headers.

- One row per respondent in forms (with a lot of similarly named columns that actually contain the same data 7 times), are displayed as 7 rows (with common question headers). 

IDDepartmentModule numberWhat is working well?What is NOT working well?What opportunities for development do you see?
1ITNo   
1ITModule 2M2-Q1-ID1M2-Q2-ID1M2-Q3-ID1
2FinanceNo   
2FinanceNo   
3OperationsModule 1M1-Q1-ID3M1-Q2-ID3M1-Q3-ID3
3OperationsModule 2M2-Q1-ID3M2-Q2-ID3M2-Q3-ID3

 

I know one way to get to my desired end result (which is probably far away from best practice). It involves the following steps:
- Duplicate the dataset 7 times

- Remove all rows, except for one module (for all 7 duplicates)
- Replacing values in "do you have feedback regarding M1?" from "yes" to the module number, for all 7 duplicates
- Generalise the other question headers for all 7 duplicates.
- Append all 7 queries to one

In example with adding new columns, all duplicates of the query imports the now columns, and so it requires more work than I hope is necessary.

I would much appreciate it if someone could assist on this issue. 

2 ACCEPTED SOLUTIONS
Helper II
Helper II

Hi @ahaBI ,

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"

 

View solution in original post

Impactful Individual
Impactful Individual

Hi, @ahaBI 

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

View solution in original post

4 REPLIES 4
Impactful Individual
Impactful Individual

Hi, @ahaBI 

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

View solution in original post

@ziying35 little bit confusing, but nice 🙂 

@ahaBI if you use this solution, you can adjust the code for actual number of questions in this step:

 

toList = Table.ToList(pmhd, each Table.FromRows(List.Transform(fx(List.Split(List.Skip(_,2),4)),(lst)=> List.FirstN(_,2)&lst )))

Yes, @Bohumil_Uhrin , with your special instructions, my solution is much more complete, and all that's left is for @ahaBI  to change the field titles.

 

Helper II
Helper II

Hi @ahaBI ,

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"

 

View solution in original post

Helpful resources

Announcements
secondImage

Happy New Year from Power BI

This is a must watch for a message from Power BI!

December Update

Check it Out!

Click here to read more about the December 2020 Updates!

Community Blog

Check it Out!

Click here to read the latest blog and learn more about contributing to the Power BI blog!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors