Forum Discussion
Columns to Rows in Summary Table
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
| Inquiry | Category | Isolation | Shelter | Food | Mask |
| 1 | Isolation, Food | yes | no | yes | no |
| 2 | Isolation | yes | no | no | no |
| 3 | Shelter, Food, Mask | no | yes | yes | yes |
| 4 | Isolation, Shelter, Mask | yes | yes | no | yes |
| 5 | Food, Mask | no | no | yes | yes |
| 6 | Isolation, Shelter, Mask | yes | yes | no | no |
| 7 | Shelter, Food | no | yes | yes | no |
| 8 | Isolation Mask | yes | no | no | yes |
| 9 | Mask | no | no | no | yes |
That is the data table and this is the calculated summary table that I want in Power Query
| Category | Count |
| Isolation | 5 |
| Shelter | 4 |
| Food | 4 |
| Mask | 5 |
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"
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.