Forum Discussion

annetoal's avatar
annetoal
Icon for Helper II rankHelper II
6 years ago
Solved

Using 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 wild...
Need Help
Tutorial Requests
  • edhans's avatar
    edhans
    6 years ago

    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:

    1. Select the columns you want to keep, then select Remove Other Coluimns from the Home Menu.
    2. Select the Response column, then on the Transform ribbon, Unpivot Other Columns
    3. 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.
    4. Right-click on the Attribute column and transform to Proper Case.
    5. 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.

  • v-kelly-msft's avatar
    v-kelly-msft
    6 years ago

    Hi annetoal ,

     

    So your issue is solved,right?

    Could you pls mark the reply as answered to close it?

    Much appreciated.

     

    Best Regards,
    Kelly
    Did I answer your question? Mark my post as a solution!
Greg_Deckler's avatar
Greg_Deckler
Icon for Community Champion rankCommunity Champion
6 years ago

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.

annetoal's avatar
annetoal
Icon for Helper II rankHelper II
6 years ago

Greg,

 

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?
YesYesNoYes
YesYesYesYes
IDKIDKNoYes
NoNoNoYes
IDKNoIDKIDK

 

Result table:

 

ResponseCatsBirdsDogs
Yes214
No230
IDK111

 

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's avatar
    mahoneypat
    Icon for Microsoft Employee rankMicrosoft Employee
    6 years ago

    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

    • annetoal's avatar
      annetoal
      Icon for Helper II rankHelper II
      6 years ago

      Pat,

       

      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's avatar
        edhans
        Icon for Community Champion rankCommunity Champion
        6 years ago

        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:

        1. Select the columns you want to keep, then select Remove Other Coluimns from the Home Menu.
        2. Select the Response column, then on the Transform ribbon, Unpivot Other Columns
        3. 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.
        4. Right-click on the Attribute column and transform to Proper Case.
        5. 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.