How to group data of similar survey answers with Power Query

0
votes

First of all I'm sorry if I do anything wrong! This is my first post here, but I try my best.

To my problem: I have a .CSV file from a survey tool, which I try to convert in a more concise form with the Power Query Tool from Excel.

After some basic editing I get to a form like this:

Starting form:

Starting form

The left column reflects the headings of the questions asked in the survey. Since some questions can have more than one answer, the headings may appear more than once. My goal now would be: To generate a column for each question and to arrange the given answers underneath. But so far I get e.g. for "Instrument(s)" also 3 columns, because 3 answers were given there, I would like to summarize these also in one cell for each participant. In addition, after the question "comment" the evaluation of the next participant begins, so here should also start a new row. The target format should look like this:

Final Format Final format

Do you have any advice or solution how to achieve this? Thank you for your help in advance, I appreciate any help!

3
excelcsvpowerquerycsv-import
This is not a free coding site (although some might do that). But we can help you with code you are trying to develop. This type of problem has been addressed on this site in the past. So please do some research and edit your question to present the code you have tried; along with an explanation of where you have run into problems. In general, you can use an Index column; and then an Integer/Divide column to create a column by which you can group the different responders. Then it's just a matter of splitting out the desired columns in the Table.Group operations. – Ron Rosenfeld

3 Answers

0
votes

In general, agree with Ron's comments

That said, some code samples were appreciated when I started, so here you go

let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Added Index" = Table.AddIndexColumn(Source, "Index", 0, 1),
// 20 based on number of repeating rows in each answer block
#"Divide" = Table.TransformColumns(#"Added Index",{{"Index", each Number.IntegerDivide(_, 20), type number}}),
// combine rows that have same title and more than one answer
#"Grouped Rows" = Table.Group(#"Divide", {"voteOptionText", "Index"}, {{"Data", each Text.Combine([voteAnswer]," "), type text}}),
// pivot to get into proper sideways format
#"Pivoted Column" = Table.Pivot(#"Grouped Rows", List.Distinct(#"Grouped Rows"[voteOptionText]), "voteOptionText", "Data")
in  #"Pivoted Column"
0
votes

sorry for the late reply, but I was prevented because of easter.

Thanks for your help, so far it has given me enough clues on how to proceed. I will test the suggestions and now I know exactly what I can still look for.

0
votes

I have now thanks to your help almost managed to get the .csv file in my desired format. However, I'm now facing the problem that I can't distinguish between the answers of the different people who answered the survey via pivot conversion, the number of answers can always be different, so dividing by a certain number doesn't get me to the desired format. An example answer1 can look like this:

Answer1

and answer2 like this:

Answer2

Combined it like this:

Combined

If I now try to use pivot column I lose the information of who gave the answer, because the similar Questions gets merged together:

Pivot

Code at this point:

let
    Quelle = Folder.Files("Z:\Report Form"),
    #"Z:\Report Form\example csv" = Quelle{[#"Folder  Path"="Z:\Report Form\",Name="example.csv"]}[Content],
    #"Importierte CSV" = Csv.Document(#"Z:\Report Form\example csv",[Delimiter=",", Columns=6, Encoding=1252, QuoteStyle=QuoteStyle.None]),
    #"Höher gestufte Header" = Table.PromoteHeaders(#"Importierte CSV", [PromoteAllScalars=true]),
    #"Geänderter Typ" = Table.TransformColumnTypes(#"Höher gestufte Header",{{"id", Int64.Type}, {"userId", type text}, {"voteOptionId", Int64.Type}, {"voteOptionText", type text}, {"voteAnswer", type text}, {"voteOptionType", type text}}),
    #"Entfernte Spalten" = Table.RemoveColumns(#"Geänderter Typ",{"userId", "id", "voteOptionId", "voteOptionType"}),
    #"Gruppierte Zeilen" = Table.Group(#"Entfernte Spalten", {"voteOptionText"}, {{"Anzahl", each _, type table [voteOptionText=nullable text, voteAnswer=nullable text]}}),
    #"Hinzugefügte benutzerdefinierte Spalte" = Table.AddColumn(#"Gruppierte Zeilen", "Custom", each Table.AddIndexColumn([Anzahl], "index", 1)),
    #"Erweiterte Custom" = Table.ExpandTableColumn(#"Hinzugefügte benutzerdefinierte Spalte", "Custom", {"voteAnswer", "index"}, {"voteAnswer", "index"}),
    #"Entfernte Spalten1" = Table.RemoveColumns(#"Erweiterte Custom",{"Anzahl"}),
    #"Pivotierte Spalte" = Table.Pivot(#"Entfernte Spalten1", List.Distinct(#"Entfernte Spalten1"[voteOptionText]), "voteOptionText", "voteAnswer")
in
    #"Pivotierte Spalte"

So what I tried to do was to merge the answers from same questions together, but I can't distinguish between the 2 diffrent persons who gave the answers. I also tried to implement a counter, which increases everytime QuestionA appears, since this question has only 1 answer possibility, but I also failed here. Do you have any further advices? I hope I was able to describe my problem understandable.