Hi,
I have created a questionnaire in HarvestYourdata. The data output i get, is as listed in the first 5 rows in the attached excel file.
The questionnaire includes a loop of 3 questions, that continous in a new column each time.
I would like the answers for each question to appear under each other, instead of next to eachother. I have manually created the output I would like, below the first 5 rows in Excel. Is there anyway to do this quickly?
As I am interested in Power Query, which i am learning, I've been playing with this all day. See the Pivot tab in the attached file. The PQ code I used is below:
let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], #"Changed Type" = Table.TransformColumnTypes(Source,{{"Surveyed Date", type datetime}, {"Surveyed Time", type time}, {"Surveyed End Date", type datetime}, {"Hvilken butik står du i?", type text}, {"Hvilken vare står du med?", type text}, {"Hvad er prisen på varen?", type number}, {"Tag et billede af varen", type text}, {"Hvilken vare står du med?2", type text}, {"Hvad er prisen på varen?3", type number}, {"Tag et billede af varen4", type text}, {"Hvilken vare står du med?5", type text}, {"Hvad er prisen på varen?6", type number}, {"Tag et billede af varen7", type text}, {"Hvilken vare står du med?8", type text}, {"Hvad er prisen på varen?9", type number}, {"Tag et billede af varen10", type text}, {"Hvilken vare står du med?11", type text}, {"Hvad er prisen på varen?12", type number}, {"Tag et billede af varen13", type text}, {"Hvilken vare står du med?14", type text}, {"Hvad er prisen på varen?15", type number}, {"Tag et billede af varen16", type text}, {"Hvilken vare står du med?17", type text}, {"Hvad er prisen på varen?18", type number}, {"Tag et billede af varen19", type text}, {"Hvilken vare står du med?20", type text}, {"Hvad er prisen på varen?21", Int64.Type}, {"Tag et billede af varen22", type text}, {"Hvilken vare står du med?23", type text}, {"Hvad er prisen på varen?24", type number}, {"Tag et billede af varen25", type text}, {"Hvilken vare står du med?26", type text}, {"Hvad er prisen på varen?27", type number}, {"Tag et billede af varen28", type text}}), #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1), #"Reordered Columns" = Table.ReorderColumns(#"Added Index",{"Index", "Surveyed Date", "Surveyed Time", "Surveyed End Date", "Hvilken butik står du i?", "Hvilken vare står du med?", "Hvad er prisen på varen?", "Tag et billede af varen", "Hvilken vare står du med?2", "Hvad er prisen på varen?3", "Tag et billede af varen4", "Hvilken vare står du med?5", "Hvad er prisen på varen?6", "Tag et billede af varen7", "Hvilken vare står du med?8", "Hvad er prisen på varen?9", "Tag et billede af varen10", "Hvilken vare står du med?11", "Hvad er prisen på varen?12", "Tag et billede af varen13", "Hvilken vare står du med?14", "Hvad er prisen på varen?15", "Tag et billede af varen16", "Hvilken vare står du med?17", "Hvad er prisen på varen?18", "Tag et billede af varen19", "Hvilken vare står du med?20", "Hvad er prisen på varen?21", "Tag et billede af varen22", "Hvilken vare står du med?23", "Hvad er prisen på varen?24", "Tag et billede af varen25", "Hvilken vare står du med?26", "Hvad er prisen på varen?27", "Tag et billede af varen28"}), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Reordered Columns", {"Index", "Surveyed Date", "Surveyed Time", "Surveyed End Date", "Hvilken butik står du i?"}, "Attribute", "Value"), #"Added Custom" = Table.AddColumn(#"Unpivoted Other Columns", "Custom", each Text.Combine(List.RemoveItems(Text.ToList([Attribute]),{"0".."9"}))), #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Attribute"}), #"Reordered Columns1" = Table.ReorderColumns(#"Removed Columns",{"Index", "Surveyed Date", "Surveyed Time", "Surveyed End Date", "Hvilken butik står du i?", "Custom", "Value"}), #"Added Index1" = Table.AddIndexColumn(#"Reordered Columns1", "Index.1", 0, 1), #"Integer-Divided Column" = Table.TransformColumns(#"Added Index1", {{"Index.1", each Number.IntegerDivide(_, 3), Int64.Type}}), #"Pivoted Column" = Table.Pivot(#"Integer-Divided Column", List.Distinct(#"Integer-Divided Column"[Custom]), "Custom", "Value"), #"Removed Columns1" = Table.RemoveColumns(#"Pivoted Column",{"Index", "Index.1"}) in #"Removed Columns1"
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help. It's a universal courtesy.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
NB: as a Moderator, I never accept friendship requests.
Forum Rules (updated August 2023): please read them here.
Thank you AliGW, this worked wonders.
I have not worked with PQ before, but this seems to work. Am I right in assuming I can use this in a macro as well? Or can I create a PQ Masterfile?
As I said, I am only just beginning to learn myself how it works, so at the moment, I can't answer your added questions - sorry. What I would do is load my table into PQ, then copy the code into the code editor window and see if it works on the larger, real data set. If not, come back and we might be able to tweak it.
It is possible that someone with VBA skills could write you a macro that would do the same thing.
I just tried it with the real data set (63 columns). And it does not seem to work for me. Is there a way to include more columns in the PQ code?
Also, the dataset will probably vary from week to week. I cannot figure out if the code changes all data marked, or only a set number oc columns and rows?.
Again, thank you for your assistance.
I think, given your comment about the dataset changing, you are going to struggle with this, even with VBA, because any solution offered is going to rely on there being patterns in the data and fixed logic.
Shall I move this to the VBA section and see if anyone can help you there?
I am not sure if I explained myself proberly.
The data will always have the same layout, but the size will differ. It's basically the same 3 questions in a loop. Sometimes it will loop 20 times, other times it will loop more or less. It will always be 1 row of headlines, and 4 rows of data.
OK, well I'll move you to the VBA section and see if someone can help.
Thank you
I've put out a call for help - hopefully someone will pop along soon.
here's my hack and slash solutionOption Explicit Sub ParseData() Dim lRow As Long 'determining # of People surveyed Dim Prod As Long 'prod is # of products determined by # of columns Dim Count As Long 'loop counter for people Dim k As Long 'loop counter for Prod Dim j As Long 'counter for copy output Dim ws1 As Worksheet 'original worksheet Dim ws2 As Worksheet 'Output worksheet Set ws1 = ActiveSheet Set ws2 = Worksheets("Output") 'clear all output ws2.Cells.ClearContents 'copy headers ws1.Range("A1:G1").Copy ws2.Cells(1, 1) 'switch to original sheet ws1.Activate 'determine how many people? get surveyed lRow = ws1.Cells(Rows.Count, "A").End(xlUp).Row 'determine how many products are surveyed Prod = (ws1.Cells(1, Columns.Count).End(xlToLeft).Column - 4) / 3 '# of loops/products in survey Count = 2 'starts @ row 2 j = 2 'set row to start copying to For Count = 2 To lRow 'survey loop k = 1 'set/reset product loop ws1.Range(Cells(Count, 1), Cells(Count, 4)).Copy ws2.Cells(j, 1) 'copy the header line for each person For k = 1 To Prod 'loop for amount of products surveyed ws1.Range(Cells(Count, (k * 3) + 2), Cells(Count, (k * 3) + 4)).Copy ws2.Cells(j, 5) 'basic algebra to figure out what to copy j = j + 1 'increment output line Next Next 'last bit is just a bit a of tidy up ws2.Activate Cells.EntireColumn.AutoFit 'autofit columns ActiveWindow.Zoom = 85 'set zoom End Sub
created a worksheet to output your data
you can move it anywhere after the macro has run...or add this into code later should you want
so select original sheet (its a copy of your sheet minus your manual data entry at the bottom) and then run the code
i've commented as much of the code as possible so its hopefully easy to understand what i did where
If you are satisfied with the solution(s) provided, please mark your thread as Solved.
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.
Thank you for your help,
I ended up making it work on my own, with this piece of VBA
For dst1 = Range("A1048576").End(xlUp).Row To 11 Step -1 COLOFFSET = 5 For dst2 = 1 To 500 Cells(dst1, COLOFFSET).Select If ActiveCell <> "" Then ActiveSheet.Range(Cells(dst1, 1), Cells(dst1, 4)).Copy Sheets("ConvertedList").Select Range("A1048576").End(xlUp).Select ActiveCell.Offset(RowOffset:=1, ColumnOffset:=0).Activate Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Application.CutCopyMode = False Sheets("InputList").Select Cells(dst1, COLOFFSET).Select ActiveSheet.Range(Cells(dst1, COLOFFSET), Cells(dst1, COLOFFSET + 2)).Copy Sheets("ConvertedList").Select Range("E1048576").End(xlUp).Select ActiveCell.Offset(RowOffset:=1, ColumnOffset:=0).Activate Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Application.CutCopyMode = False Sheets("InputList").Select COLOFFSET = COLOFFSET + 3 Else Exit For End If Next dst2 Sheets("InputList").Select Range("A10").Select Next dst1
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks