Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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?
Solved! Go to Solution.
Just closing the loop. The solution is here: https://www.mrexcel.com/board/threads/power-query-to-transform-data-ready-for-pivot.1231281/
Just closing the loop. The solution is here: https://www.mrexcel.com/board/threads/power-query-to-transform-data-ready-for-pivot.1231281/
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?