0
$\begingroup$

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

$\endgroup$
1
$\begingroup$

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.

$\endgroup$
2
  • $\begingroup$ Thank you. This worked, except every single one of my variables now appears twice in the chart. Is there something I'm doing incorrectly? $\endgroup$ Mar 4 '20 at 23:20
  • $\begingroup$ Hmm. Yeah, well. I can't see your screen, your data, your code. Hard to troubleshoot that without more info. If things appear twice, chances are the data has them twice. Remove the duplicates. Power Query has buttons for that. $\endgroup$
    – teylyn
    Mar 5 '20 at 19:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.