Forum Discussion
track frequencies of keywords in Excel sheet
AnaStudent If you are open to a Power Query (PQ) solution, please find a small example attached. The key is to split the Keywords column (B) from the data table into separate keywords, then unpivot the table. The result is shown in columns D and E. This could then be the source for a regular pivot table, if you so prefer, or you perform that step within PQ. See output in G1:M7.
AnaStudent If you are open to a Power Query (PQ) solution, please find a small example attached. The key is to split the Keywords column (B) from the data table into separate keywords, then unpivot the table. The result is shown in columns D and E. This could then be the source for a regular pivot table, if you so prefer, or you perform that step within PQ. See output in G1:M7.
- Thank you for your answer.
Can you tell me how did you get output G1:M7?
More specifically, how do I aggregate columns by year and get frequencies?
I used Pivot Column and when I choose "Don't Aggregate" in the advanced options I get table filled with Error in every cell. Or I get only two rows without sorting columns by year.
Thank you very much!AnaStudent If you go to the DATA ribbon and choose "Queries & Connections". Double click the "Keywords" query. It has only three "applied steps". The source is the output from the "UnpivotTrimmed" query. Then I added a column called "Custom" filled with numbers 1, and then I pivoted the "Year" column, summing the values from the "Custom" column. Then, I load the result back to Excel. Hope this helps.
