Forum Discussion
Helper IIUsing SELECTCOLUMNS to build a table
annetoal - go back and look at what mahoneypat did. It seems to be to be 100% UI driven, so pretty easy to implement. The steps in summary:
- Select the columns you want to keep, then select Remove Other Coluimns from the Home Menu.
- Select the Response column, then on the Transform ribbon, Unpivot Other Columns
- For the Attribute column, (which has your old column names) extract all text between the "a " and "?" chars. Keep that and get rid of the other text.
- Right-click on the Attribute column and transform to Proper Case.
- Rename the Attribute column to Question and Value to Answer.
See these directions for implementing this code in your model, getting rid of the sample source step and replacing with yours. You really should try this. This is basic Power Query, not advanced, and can be very useful. Use DAX for your analysis, but Power Query for your modeling and data transformation.
1) In Power Query, select New Source, then Blank Query
2) On the Home ribbon, select "Advanced Editor" button
3) Remove everything you see, then paste the M code I've given you in that box.
4) Press Done
5) See this article if you need help using this M code in your model.Hi annetoal ,
So your issue is solved,right?
Could you pls mark the reply as answered to close it?
Much appreciated.
Best Regards,
KellyDid I answer your question? Mark my post as a solution!
Microsoft EmployeeThe best way to get rid of all those extra columns is to select the ones you want to keep with shift-click or cntrl-click (the 10 you mention plus any ResponseID column if present). Then right click and choose "Remove Other Columns". You should then unpivot your data for simplest analysis. Below is some M code with your example data to demonstrate if needed. To see how it works, just create a blank query, go to Advanced Editor, and replace the text there with the M code below.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WikwtVtKBkn75cKYhECcCcZJSrA6qImTSCEWVp4s3kA0hkYwyRlEElkAQECUmWMwBSyMbaYpQFAsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Q1.1 Do you have a pet?" = _t, #"Q1.2 Do you have a cat?" = _t, #"Q1.3 Do you have a bird?" = _t, #"Q1.4 Do you have a dog?" = _t, Response = _t, ExtraColumn1 = _t, ExtraColumn2 = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Q1.1 Do you have a pet?", type text}, {"Q1.2 Do you have a cat?", type text}, {"Q1.3 Do you have a bird?", type text}, {"Q1.4 Do you have a dog?", type text}, {"Response", Int64.Type}, {"ExtraColumn1", type text}, {"ExtraColumn2", type text}}),
#"Removed Other Columns" = Table.SelectColumns(#"Changed Type",{"Response", "Q1.4 Do you have a dog?", "Q1.3 Do you have a bird?", "Q1.2 Do you have a cat?", "Q1.1 Do you have a pet?"}),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Removed Other Columns", {"Response"}, "Attribute", "Value"),
#"Extracted Text Between Delimiters" = Table.TransformColumns(#"Unpivoted Other Columns", {{"Attribute", each Text.BetweenDelimiters(_, "a ", "?"), type text}}),
#"Capitalized Each Word" = Table.TransformColumns(#"Extracted Text Between Delimiters",{{"Attribute", Text.Proper, type text}}),
#"Renamed Columns" = Table.RenameColumns(#"Capitalized Each Word",{{"Attribute", "Question"}, {"Value", "Answer"}})
in
#"Renamed Columns"
With the table (I called it "Raw") in this format, you can make a matrix visual with Question on the columns, Answer on the rows and the count of responses in values, to get this result.
If this works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
Helper IIPat,
Since I don't have any experience using M code, this is way over my head. I had considered just removing all the columns I didn't need for this graph, but since I will be using the other columns in other graphs, I thought I better keep all the columns and just summarize the ones I wanted to a separate table that I could use to make graphs for a dashboard.
Being a rank amateur, I have been looking at using SELECTCOLUMNS and COUNTA, but I'm sure your solution is good. It's just a little too sophisticated for my skill level.
Thank you,
Anne
- edhans
Community Champion
annetoal - go back and look at what mahoneypat did. It seems to be to be 100% UI driven, so pretty easy to implement. The steps in summary:
- Select the columns you want to keep, then select Remove Other Coluimns from the Home Menu.
- Select the Response column, then on the Transform ribbon, Unpivot Other Columns
- For the Attribute column, (which has your old column names) extract all text between the "a " and "?" chars. Keep that and get rid of the other text.
- Right-click on the Attribute column and transform to Proper Case.
- Rename the Attribute column to Question and Value to Answer.
See these directions for implementing this code in your model, getting rid of the sample source step and replacing with yours. You really should try this. This is basic Power Query, not advanced, and can be very useful. Use DAX for your analysis, but Power Query for your modeling and data transformation.
1) In Power Query, select New Source, then Blank Query
2) On the Home ribbon, select "Advanced Editor" button
3) Remove everything you see, then paste the M code I've given you in that box.
4) Press Done
5) See this article if you need help using this M code in your model.- edhans
annetoal FYI - I added an animated GIF to show you the steps. Ping back with questions. I or mahoneypat can help you wrap it up if the solution provided isn't complete.
- annetoal
Thank you for your reply. It is pretty clear, and maybe even I can use it 🙂
Anne
v-kelly-msftCommunity Support
Hi annetoal ,
So your issue is solved,right?
Could you pls mark the reply as answered to close it?
Much appreciated.
Best Regards,
KellyDid I answer your question? Mark my post as a solution!