Forum Discussion
Helper IIUsing SELECTCOLUMNS to build a table
I have a large table that has 10 columns containing raw data I want to summarize using COUNTA and put the results into a new table. The Columns all have the same prefix, hoping that some kind of wildcard can be used to include the whole group of columns into one selection. For example: PreCol1, PreCol2, PreCol3. Each column contains "YES," "NO," or "IDK." I want to summarize the number of YES, NO, and IDKs in each column and make that summary a column in a new table.
So I want to grab all the columns that start with "Pre" , COUNT the number of YES, NO, and IDKs in each column, and put the numbers into a column in the new table. Can I do this all in one statement, or do I have to do it in 10 separate steps? Could you please give me an example of how this statement should look?
Thank you,
Anne
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!
8 Replies
- Greg_Deckler
Community Champion
annetoal - Probably all one statement, but would need to see sample source data and expected output. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.
annetoalGreg,
Thanks so much for taking an interest in my question. I created a couple of tables to illustrate what I'm trying to do:
Raw data:
Q1.1 Do you have a pet? Q1.2 Do you have a cat? Q1.3 Do you have a bird? Q1.4 Do you have a dog? Yes Yes No Yes Yes Yes Yes Yes IDK IDK No Yes No No No Yes IDK No IDK IDK Result table:
Response Cats Birds Dogs Yes 2 1 4 No 2 3 0 IDK 1 1 1 In the real raw data table, there are hundreds of columns. I'm only going after 10. The 10 columns all have parts of their names in common, like in my example where everything starts with "Q1." So I want to extract everything starting with Q1. and count the numbers of each response, summarizing to a second table.
BTW, the first column "Do you have a pet?" will be used as the slicer for the PowerBI report. That's why it doesn't appear in the second table.
Thanking you in advance,
Anne
- mahoneypat
Microsoft Employee
The 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
