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
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.