Forum Discussion

lisago1978's avatar
lisago1978
Helper III
6 years ago
Solved

Columns to Rows in Summary Table

I am trying to take a series of dichotomous colum ns (y/n) and create a summary table where these become rows in either Power BI or Power Query. The table should be dynamic in that the totals will...
Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    Hi lisago1978 ,

    If you don't want to break the original structure of table, you can create category dimension table first. Then create a measure to get the count of per category. The specific details as follow screen shot:

    1. Create category table

    2. Create a measure with the below formula

    CountofCategory = 
CALCULATE (
    DISTINCTCOUNT ( 'inquiry'[Inquiry] ),
    FILTER (
        ALL ( 'inquiry' ),
        SEARCH ( MAX ( 'Category'[Category] ), 'inquiry'[Category], 1, 0 ) > 0
    )
)

    Best Regards

    Rena

lisago1978's avatar
lisago1978
Helper III
6 years ago

I need to take the first table called data table and create the second table (summary table) in Power Query or Power BI. I think I likely need to create a calculated table perhaps using the summarize function but in addition, I need to transpose the columns into rows.

lisago1978's avatar
lisago1978
Helper III
6 years ago
InquiryCategoryIsolationShelterFoodMask
1Isolation, Foodyesnoyesno
2Isolationyesnonono
3Shelter, Food, Masknoyesyesyes
4Isolation, Shelter, Maskyesyesnoyes
5Food, Masknonoyesyes
6Isolation, Shelter, Maskyesyesnono
7Shelter, Foodnoyesyesno
8Isolation Maskyesnonoyes
9Masknononoyes

 

That is the data table and this is the calculated summary table that I want in Power Query

 

CategoryCount
Isolation5
Shelter4
Food4
Mask5
  • edhans's avatar
    edhans
    Community Champion
    6 years ago

    Here you go.

    This is the end result.

     

    See the M code below. I did it 100% using the UI buttons vs any special M code so you can easily follow the steps as it works.

     

     

     

    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

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUfIszs9JLMnMz9NRcMvPTwGKVKYWA8m8fGRmrE60khGyalR1MAKkzBjIDM5IzSlJLYIYqaPgm1icjWokggRpMUF1B1w3VB+yFrgZIH2mQDamDWjWgBSakWgB1Cvm6F7B5gmoWgtkK1ANRnUVSLElkI3hZISKWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Inquiry = _t, Category = _t, Isolation = _t, Shelter = _t, Food = _t, Mask = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Inquiry", Int64.Type}, {"Category", type text}, {"Isolation", type text}, {"Shelter", type text}, {"Food", type text}, {"Mask", type text}}),
    #"Removed Other Columns" = Table.SelectColumns(#"Changed Type",{"Isolation", "Shelter", "Food", "Mask"}),
    #"Demoted Headers" = Table.DemoteHeaders(#"Removed Other Columns"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Demoted Headers",{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}}),
    #"Transposed Table" = Table.Transpose(#"Changed Type1"),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Transposed Table", {"Column1"}, "Attribute", "Value"),
    #"Filtered Rows" = Table.SelectRows(#"Unpivoted Other Columns", each ([Value] = "yes")),
    #"Grouped Rows" = Table.Group(#"Filtered Rows", {"Column1"}, {{"Count", each Table.RowCount(_), type number}}),
    #"Renamed Columns" = Table.RenameColumns(#"Grouped Rows",{{"Column1", "Category"}}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Renamed Columns",{{"Category", type text}})
in
    #"Changed Type2"

     

    • lisago1978's avatar
      lisago1978
      Helper III
      6 years ago

      Thank for for your help. I don't understand the M code. My table has many more data and each row currently has a unique ID. Can you explain what this code does or break it down into steps because my data is much more complicated than this and I will need to replicate the steps.

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi lisago1978 ,

    You can follow the below steps to achieve your requirement:

    1. Unpivot the columns Isolation, Shelter, Food and Mask

    Unpivot columnsafter unpivot

    2. Create a measure to get the count of status with "yes" per category, then drag the field Attribute and Value onto Table visual

    Measure = CALCULATE(COUNT('inquiry'[Value]),FILTER('inquiry','inquiry'[Value]="Yes"))

    Best Regards

    Rena

    • lisago1978's avatar
      lisago1978
      Helper III
      6 years ago

      If I unpivot the columns in my data table I lose all of the other important fields. Is there a summarize feature that does not change the parameters of the table, each record has a unique ID that I don't want to  lose rather just have a separate table that summarizes these yeses from the columsn and has the columns as rows in a separate summary table.

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        Hi lisago1978 ,

        If you don't want to break the original structure of table, you can create category dimension table first. Then create a measure to get the count of per category. The specific details as follow screen shot:

        1. Create category table

        2. Create a measure with the below formula

        CountofCategory = 
CALCULATE (
    DISTINCTCOUNT ( 'inquiry'[Inquiry] ),
    FILTER (
        ALL ( 'inquiry' ),
        SEARCH ( MAX ( 'Category'[Category] ), 'inquiry'[Category], 1, 0 ) > 0
    )
)

        Best Regards

        Rena