Book excerpt: Basic text analytics using Power BI and Power Query in Excel

Learn how to harness Power Query to gain fundamental insights into textual feeds with this excerpt from Gil Raviv’s upcoming book, Collect, Transform and Combine Data using Power BI and Power Query in Excel. This content covers basic text analytics and a technique to detect multiple keywords in textual feeds using Cartesian Product.

The techniques in this chapter are extremely useful in analyzing tables with textual input, such as client feedback in surveys, operational comments, or social network feeds. You will learn how to apply common transformations to extract meaning from words. Start the journey with basic techniques to detect keywords, and gradually improve your toolset to solve common text-related analytical challenges.

This book is currently scheduled for release in August 2018. Pre-order it now on Amazon.


Basic text analytics using Power BI and Power Query in Excel

Words empty as the wind are best left unsaid.
–Homer

IN THIS CHAPTER, YOU WILL:

  • Learn how to search for keywords in textual columns and apply this technique as a simple topic detection method.
  • Learn how to load external list of keywords and apply them dynamically in the search.
  • Use Cartesian Product or custom function to search for keywords.
  • Use Table.Buffer and List.Buffer to improve keyword detection performance.
  • Learn several methods to split words, ignore common words, and count word occurrences in your analysis.
  • Learn how to prepare your textual data and load it in Word Cloud custom visual in Power BI.

In this chapter, you will harness Power Query to gain fundamental insights into textual feeds. Many tables in your reports may already contain abundant textual columns that are left barren, untapped, and devoid of meaning. As a paraphrase to Homer’s saying that empty words are best left unsaid, you may find many words in your reports that are “empty as the wind.” These textual columns are most likely ignored in your report, or are kept in just for some context. Fortunately, in next two chapters, you will learn very useful methods to take leverage of textual columns and tap into new insights as you unravel these hidden treasure troves.

The techniques in this chapter will be extremely useful in analyzing tables with textual input, such as client feedback in surveys, operational comments, or social network feeds. You will learn how to apply common transformations to extract meaning from words. We will start the journey with basic techniques to detect keywords, and gradually improve our toolset to solve common text-related analytical challenges. Our text analytics journey will continue in the next chapter with advanced text analytics on Azure—including text translation, sentiment analysis, and detection of key phrases—with no help from data scientists or software developers.

Read the rest of this excerpt on Gil’s blog.