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!
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
Community Championannetoal - 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!