Chapter 4Searching and Combining Data with Power Query

After making all the decisions regarding the data warehouse, end users can begin consuming the data using one or several types of technologies but some users may realize that the technology does not contain all the data they need to solve, analyze, or answer particular questions. Typically, this means the end user enters a request asking the business intelligence team to modify the existing data warehouse structure to accommodate the change. This change request enters into a queue, and after a period of time, it makes its way into both the data warehouse and the hands of the end user.

Between the time of the request and the actually delivery, the end user may have solved the problem using several different technologies and steps, for example, they may have:

  1. Copied, emailed, or downloaded the data from the source
  2. Shaped the data into a format that meets their needs
  3. Performed a data validation step
  4. Figured out how to combine new data with existing data in the data warehouse

Imagine repeating these steps for each dataset that does not exist in the data warehouse. This may not only frustrate the end users when they acquire new data, but it may also shift the user's perception of the warehouse from one of a single centralized source to that of an incomplete dataset and project.

To mitigate this, Microsoft introduced the Power Query add-in for Excel.

Get Applied Microsoft Business Intelligence now with the O’Reilly learning platform.

O’Reilly members experience live online training, plus books, videos, and digital content from nearly 200 publishers.