Forum Discussion
Need help to show Count from multiple columns.
Hi Everyone,
I need your help ,I am trying to show Distinct names and there count in Visualisation table .
Task is to show names and there count from three columns, It is possible name appears in all three column then count will be 3. If its blank then Ignore.
As shown in below example:
This is how my data looks like.
| Name 1 | Count Name 1 | Name 2 | Count Name 2 | Name 3 | Count Name 3 | |
| Abc | Def | 1 | Fgh | 1 | ||
| Def | Fgh | 1 | Abc | 1 | ||
| Abc | 1 | Abc | 1 | Abc | 1 | |
| def | 1 | Abc | Xyz | 1 | ||
| Ghi | 1 | Def | GhI | 1 |
I am trying to show names and there count of null as shown in below table.
| Name | Count |
| Abc | 4 |
| Def | 2 |
| Ghi | 2 |
| Xyz | 1 |
I tried Unpivot but it didnt pick value from other columns.
Thanks for your help.
1 Reply
To show the count of distinct names from three columns, you can use the Power Query Editor in Excel. Here are the steps to achieve this:
- Select the three columns that contain the names and their corresponding counts.
- Go to the "Transform" tab in the Power Query Editor and select "Unpivot Columns".
- In the "Unpivot Columns" dialog box, select only the columns that contain the names and their corresponding counts, and click "OK".
- Rename the "Attribute" column to "Name" and the "Value" column to "Count".
- Remove any unnecessary columns.
- In the "Home" tab, click on "Close & Load To".
- In the "Import Data" dialog box, choose "Only Create Connection" and then click "OK".
- In the Excel worksheet, go to the "Data" tab and click on "Queries & Connections" to open the "Workbook Connections" dialog box.
- Right-click on the connection that you just created and select "Load To...".
- In the "Load To" dialog box, choose "PivotTable Report" and then click "OK".
- In the "Create PivotTable" dialog box, select the range of the data and choose where you want the PivotTable to be located, and then click "OK".
- In the PivotTable Fields pane, drag the "Name" field to the Rows area and the "Count" field to the Values area.
- In the "Value Field Settings" dialog box, select "Distinct Count" as the calculation type, and then click "OK".
By following these steps, you should be able to show the distinct names and their counts from the three columns.
