Forum Discussion
Transform data for pivot
Hello, would greatly appreciate some help with power query that transforms my data from this:
| User | Q1Question | Q1Category | Q1Answer | Q2Question | Q2Category | Q2Answer | Q3Question | Q3Category | 3Answer | Q4Question | Q4Category | Q4Answer | Q58Question | Q58Category | Q58Answer |
| User 1 | Age? | Physical | High | Height? | Physical | Low | Shoe Size? | Physical | Medium | Preferred food? | Preference | High | Preferred Colour? | Preference | High |
| User 2 | Age? | Physical | High | Height? | Physical | High | Shoe Size? | Physical | Medium | Preferred food? | Preference | Low | Preferred Colour? | Preference | Low |
| User 3 | Age? | Physical | Medium | Height? | Physical | High | Shoe Size? | Physical | Medium | Preferred food? | Preference | Low | Preferred Colour? | Preference | Low |
| User 4 | Age? | Physical | Medium | Height? | Physical | High | Shoe Size? | Physical | High | Preferred food? | Preference | Low | Preferred Colour? | Preference | Medium |
| User 5 | Age? | Physical | Medium | Height? | Physical | High | Shoe Size? | Physical | High | Preferred food? | Preference | Medium | Preferred Colour? | Preference | Medium |
| User 6 | Age? | Physical | Low | Height? | Physical | Medium | Shoe Size? | Physical | Low | Preferred food? | Preference | Low | Preferred Colour? | Preference | Medium |
...into a format that will allow me to create a pivot table in Excel that will alllow my pivot table to look like this, counting the answsers of each type:
| High | Medium | Low | |
| Physical | |||
| Age? | 2 | 3 | 1 |
| Height? | 4 | 1 | 1 |
| Shoe Size? | 2 | 3 | 1 |
| Preference | |||
| Preferred food? | 1 | 1 | 4 |
| Preferred Colour? | 1 | 3 | 2 |
i.e. I want to group by 'Category' and then by 'Answer'
I have thousands of rows and 58x Question, Question Category and Answer and the fields arenot contiguous
Here is a mock-up of the starting table:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("zZE9C4MwEIb/imR2qR9dRVw6tNAincRB9PwAayAqYn994ykR7JW2KLTLe5rnwj1JgoBdaxBMZ5cdhuZFDWRc9PLPreoOhIbAwCAoAhODoAgsDIIisDEIarNQH/VQwc3AkeWc93URR6X8PBRZPhSQtVmwI+9k+jkHzS/uy50nSIr2NqwJSEEISLSU88RRK1DFME+Yuzxe8lbQfcrW+Np2Ymt1x0O/tR3alKxJyqqJ/6Zrba/79MhrZCcT5Wv/ypd4hA+V96TyeH7SV416Zby8uy0uOHwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [User = _t, Q1Question = _t, Q1Category = _t, Q1Answer = _t, Q2Question = _t, Q2Category = _t, Q2Answer = _t, Q3Question = _t, Q3Category = _t, Q3Answer = _t, Q4Question = _t, Q4Category = _t, Q4Answer = _t, Q58Question = _t, Q58Category = _t, Q58Answer = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"User", type text}, {"Q1Question", type text}, {"Q1Category", type text}, {"Q1Answer", type text}, {"Q2Question", type text}, {"Q2Category", type text}, {"Q2Answer", type text}, {"Q3Question", type text}, {"Q3Category", type text}, {"Q3Answer", type text}, {"Q4Question", type text}, {"Q4Category", type text}, {"Q4Answer", type text}, {"Q58Question", type text}, {"Q58Category", type text}, {"Q58Answer", type text}})
in
#"Changed Type"
It seems to me that I need to find a way to first stack the entries in all the fields ending 'Category', and 'Answer', but I'm struggling to know how to start?
Just closing the loop. The solution is here: https://www.mrexcel.com/board/threads/power-query-to-transform-data-ready-for-pivot.1231281/
2 Replies
So... inspired by this video from howtoexcel.org, I managed to get a solution by first merging the columns for each question, and then selecting all the merged columns, unpivoting, then splitting the columns again by the delimiter.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCi1OLVIwVNJRckxPtQdSARmVxZnJiTlApkdmegaISgXSJWhyPvnlQDI4Iz9VITizCl2nb2pKZmkuSKwoNS21qCg1RSEtPz/FHi6SmpecirABoco5Pye/tAi7ulgdqGuNSHYtVI5S50I8TdC1IGVwxxpjdSzcxsHmXBPqOxcjkilxLNQlcPeaDpR7sUQCkU42w+pkiP+xuhduFS4Xo4cdNQI4FgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [User = _t, Q1Question = _t, Q1Category = _t, Q1Answer = _t, Q2Question = _t, Q2Category = _t, Q2Answer = _t, Q3Question = _t, Q3Category = _t, Q3Answer = _t, Q4Question = _t, Q4Category = _t, Q4Answer = _t, Q58Question = _t, Q58Category = _t, Q58Answer = _t]), #"Merged Columns" = Table.CombineColumns(Source,{"Q1Question", "Q1Category", "Q1Answer"},Combiner.CombineTextByDelimiter(";", QuoteStyle.None),"Q1"), #"Merged Columns1" = Table.CombineColumns(#"Merged Columns",{"Q2Question", "Q2Category", "Q2Answer"},Combiner.CombineTextByDelimiter(";", QuoteStyle.None),"Q2"), #"Merged Columns2" = Table.CombineColumns(#"Merged Columns1",{"Q3Question", "Q3Category", "Q3Answer"},Combiner.CombineTextByDelimiter(";", QuoteStyle.None),"Q3"), #"Merged Columns3" = Table.CombineColumns(#"Merged Columns2",{"Q4Question", "Q4Category", "Q4Answer"},Combiner.CombineTextByDelimiter(";", QuoteStyle.None),"Q4"), #"Merged Columns4" = Table.CombineColumns(#"Merged Columns3",{"Q58Question", "Q58Category", "Q58Answer"},Combiner.CombineTextByDelimiter(";", QuoteStyle.None),"Q58"), #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Merged Columns4", {"User"}, "Attribute", "Value"), #"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Columns", "Value", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), {"Value.1", "Value.2", "Value.3"}), #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Value.1", type text}, {"Value.2", type text}, {"Value.3", type text}}), #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Value.1", "Question"}, {"Value.2", "Category"}, {"Value.3", "Answer"}}) in #"Renamed Columns"...But, that will be very cumbersome for 60-odd columns. Does anyone know how to do that in M / Power Query so that it will merge all columns beginning with Qsomething, where Qsomething can be a single or double-digit number?
Just closing the loop. The solution is here: https://www.mrexcel.com/board/threads/power-query-to-transform-data-ready-for-pivot.1231281/