Help with breaking up a column in PowerBI to make a bar chart

Question

My data set is a list of documents. I have a column in my data set - 'Independent Variable'. This column can contain nothing, i.e. 'NA', or one variable, i.e. 'var1', or multiple, i.e. 'var1;var2;var3' and is essentially metadata for the documents. I'm attempting to create a visual, like a bar chart, that would show how many documents in my data set are associated with each independent variable. I have roughly 30 independent variables, and they are all housed in the column 'Independent Variable', hence when trying to make a chart i get a ton of entries for that so each bar is 'var1', 'var2', 'var1;var2' etc. How can I break it down so that only var1, var2, var3 are my buckets and if those variables exist in the column that document is counted

Answer 1

You need to clean up the data. Load it into the Power Query editor.

• Split the column with the variables by the delimiter ;
• now you have a lot of individual columns, some have values, some are empty
• select all columns except the newly split ones and use Unpivot > Unpivot Other Columns on the transform ribbon.
• Now you have one row for each combination of document name and variable
• Rename the columns as you see fit, delete the Attribute column and load the data.

Create a chart with the variable column in the legend.