Visual that shows Count of text string occurrences across 2 or more columns in same table

[sscanlon]

Hi, I have searched for hours and cannot find the way to do this.

I am using MS Forms + Flow to get data into an Excel then trying to show totals for each dropdown "category"

On the Form I originally used checkboxes and people could select multiple options, but when it dumped into excel it showed as 

"Option A","Option B","Option C"

I had no idea how to deal with that in Power BI so I had to seperate it into 3 drop downs, only first dropdown is required (so you will see null for some cells)

The question: 

How can I create a visualization that is either a table or better yet horizontal clustered bar chart that shows the totals for each instance of text string in all 3 columns? 

I tried to create a separate XLS / Table that lists all possible OEM, and created a One to Many relationship with "both" type direction. But got stuck there, was not working. 

Any help would be GREATLY appreciated!!

[PhilipTreacy]

@sscanlon 

Just select all the columns and then right click a column header and select Unpivot Columns

Then click on both colum headers to select the columns, then right click on a col header and choose Group By then Group as shown here

Any new form submissions will be loaded and transformed in PQ by these steps, you won't need to make any changes - as long as the number of columns doesn't change.

No worries, post back if you get stuck.

Regards

Phil

[sscanlon]

Hi, the only issue I am running into is that when I unpivot it duplicates all my rows, so instead of 1 entry it is now 3 or 4. This then throws off my count in other columns. 

Is this expected? or am I doing something wrong? 

If so, is there another way to achieve my count of those 3 columns without unpivot? 

[sscanlon]

May be able to use "Reference" Query option in Editor, creates a new table based on original and I can do my changes there without effecting some of the original columns.

Let me know if this is a good idea? 

[PhilipTreacy]

@sscanlon 

If you can supply a sample of all your data (all columns) I'll do the transforms for you and then you can see what to do at your end.

regards

Phil

[PhilipTreacy]

Hi @sscanlon 

Download PBIX file with this sample data, code and visual.

You need to unpivot the data then group by the Category and OEM, and Count the instances of the OEM names.

The Power Query code is in the file linked to above.

regards

Phil

[sscanlon]

Ah!! OK was not familiar with unpivot, I actually have 3 other columns like the ones I showed, so I will unpivot those as well and use your method to count / create visuals. 

One last question - I am guessing any new additions to this XLS from the Form will be automatically "unpivoted" and show in the same way? I can experiment and see. 

It seems like a formula that is applied to the table in Power BI Editor so it should just unpivot anything added. 

Thank you! I will Accept as Solution.