Forum Discussion
Columns to Rows in Summary Table
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
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
11 Replies
In Power Query, select the Inquiry and Category Columns. Then on the Transformation Ribbon, select "Unpivot Other Columns"
I want to just create a summary table not include every inquiry as a separate column. Would this still work?
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.
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
