Columns to Rows in Summary Table

[lisago1978]

I am trying to take a series of dichotomous columns (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 change based on filtered values (not shown). Can some one help me with this. I don't know if it is a transpose or a new calculated table. Thanks

[edhans]

In Power Query, select the Inquiry and Category Columns. Then on the Transformation Ribbon, select "Unpivot Other Columns"

[lisago1978]

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]

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

[Anonymous]

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]

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 · Accepted solution]

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

[amitchandak]

@lisago1978 , better you split the column into rows

https://www.tutorialgateway.org/how-to-split-columns-in-power-bi/

[edhans]

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]

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.

[edhans]

This is what Power Query was designed for. Please provide sample data using the instructions in the links below, then let me know how you want it summarized before it loads in to DAX. Power Query will do all of the grouping and summarizing you need.

You can include a screenshot of what you want the expected result to look like, but don't provide a screenshot of the source data. Use the "sample data" link below.

How to get good help fast. Help us help you.
How to Get Your Question Answered Quickly
How to provide sample data in the Power BI Forum

[lisago1978]

I want to just create a summary table not include every inquiry as a separate column. Would this still work?