Skip to main content
main-content
Top

About this book

Master the art of loading external data into Excel for use in reporting, charting, dashboarding, and business intelligence. This book provides a complete and thorough explanation of Microsoft Excel’s Get and Transform feature set, showing you how to connect to a range of external databases and other data sources to find data and pull that data into your local spreadsheet for further analysis. Leading databases are covered, including Microsoft Azure data sources and web sources, and you will learn how to access those sources from your Microsoft Excel spreadsheets.
Getting data into Excel is a prerequisite for using Excel's analytics capabilities. This book takes you beyond copying and pasting by showing you how to connect to your corporate databases that are hosted in the Azure cloud, and how to pull data from Oracle Database and SQL Server, and other sources.
Accessing data is only half the problem, and the other half involves cleansing and rearranging your data to make it useful in spreadsheet form. Author Adam Aspin shows you how to create datasets and transformations. For advanced problems, there is help on the M language that is built into Excel, specifically to support mashing up data in support of business intelligence and analysis. If you are an Excel user, you won't want to be without this book that teaches you to extract and prepare external data ready for use in what is arguably the world’s leading analytics tool.

What You Will LearnConnect to a range of external data, from databases to Azure sourcesIngest data directly into your spreadsheets, or into PowerPivot data modelsCleanse and prepare external data so it can be used inside ExcelRefresh data quickly and easily to always have the latest informationTransform data into ready-to-use structures that fit the spreadsheet formatExecute M language functions for complex data transformations
Who This Book Is For
Excel users who want to access data from external sources—including the Microsoft Azure platform—in order to create business intelligence reporting, dashboards, and visualizations. For Excel users needing to cleanse and rearrange such data to meet their own, specific needs.

Table of Contents

Frontmatter

Chapter 1. Using Power Query to Discover and Load Data into Excel

Abstract
If you are reading this book, it is most likely because you need to use data. More specifically, it may be that you need to take a journey from data to insight in which you have to take quantities of facts and figures, shape them into comprehensible information, and provide the analysis that delivers clear meaning. More to the point, you want to do all this using the spreadsheet that you know and trust—Microsoft Excel.
Adam Aspin

Chapter 2. Discovering and Loading File-Based Data with Power Query

Abstract
Sending files across networks and over the Internet or via email has become second nature to most of us. As long as the files that you have obtained conform to some of the widely recognized standards currently in use (of which you will learn more later), you should have little difficulty loading them into Power Query.
Adam Aspin

Chapter 3. Loading Data from Databases and Data Warehouses

Abstract
Much of the world’s corporate data currently resides in relational databases, data warehouses, and data warehouse appliances either on-premises or in the cloud. Excel—via Power Query—can connect to many of the world’s leading commercial and open source databases and data warehouses. This chapter will show you how to extract data from several of these data sources to drive your Excel-based analytics using Power Query. Indeed, you will discover that once you have learned how to connect to one or two databases, you have learned how to use nearly all of them, thanks to the standardized interface and approach that Power Query brings to data extraction.
Adam Aspin

Chapter 4. Loading Data from the Web and the Cloud

Abstract
In this chapter, we will take a look at a subset of the fast-growing and wide-ranging set of data sources available over the Internet that you can use as a source of analytical data for Excel. While the data sources that you will see in the following pages may be extremely diverse, they all have one thing in common: they are stored outside the enterprise and are available using an Internet connection.
Adam Aspin

Chapter 5. Generic Data Sources

Abstract
If you take a good look at the Get Data options, you will find that there are currently 40 data sources for which Power Query connectors are available. However, even this range of connectors pales into insignificance when faced with the vast array of potential source data repositories. So what can you do when faced with a source of external data that is not among those currently available?
Adam Aspin

Chapter 6. Structuring Imported Data

Abstract
In the previous five chapters, you saw some of the ways in which you can find and load (or connect to) data into either Excel worksheets or the Excel data model. Inevitably, this is the first part of any process that you follow to extract, transform, and load data. Yet it is quite definitely only a first step. Once the data is accessed using Power Query, you need to know how to adapt it to suit your requirements in a multitude of ways. This is because not all data is ready to be used immediately. Quite often, you have to do some initial work on the data to make it more easily usable. Tweaking source data in Power Query is generally referred to as data transformation, which is the subject of this chapter as well as the next two.
Adam Aspin

Chapter 7. Data Transformation

Abstract
Once a dataset has been filtered and shaped (as covered in the previous chapter), it probably still needs a good few modifications to make it ready for consumption. Many of these modifications are, at their heart, a series of fairly simple yet necessary techniques that you apply to make the data cleaner and more standardized. I have chosen to group these approaches under the heading data transformation.
Adam Aspin

Chapter 8. Restructuring Data

Abstract
In the previous two chapters, you saw how to hone your dataset in Power Query so that you defined only the rows and columns of data that you really need as the basis for your analysis. Then you learned how to cleanse and complete the data that they contain. In this chapter, you will learn how to build on these foundations to deliver data that is ready to be molded into a structured and usable data model.
Adam Aspin

Chapter 9. Complex Data Loads

Abstract
Not all data loads are a matter of simply establishing a connection and applying transformations to the source data that is, fortunately, already laid out in neatly structured tables. Sometimes you may want to “push the envelope” when loading data and prepare more complex source data structures for use in your Excel analytics. By this, I mean that the source data is not initially in a ready-to-use tabular format and that some restructuring of the data is required to prepare a clean table of data for use.
Adam Aspin

Chapter 10. Organizing and Managing Queries

Abstract
Producing a robust and efficient data query is not just about finding the appropriate load and transform functions and placing them in the correct sequence. It is also about extending, maintaining, and updating the process. This can be either to correct an error once the query is being tested or to adapt a query to new requirements. This chapter will introduce you to some of the techniques that you can apply to handle the various stages of the query life cycle.
Adam Aspin

Chapter 11. Parameterizing Queries

Abstract
Not all data flows are rigid and predictable. There will, inevitably, be cases where you also want to shape the data ingestion process depending on aspects of the source data. This can mean parameterizing your queries to allow user interaction or adjusting the data flow dynamically. Adding parameters to queries enables you to define and apply specific criteria to certain aspects of query processing.
Adam Aspin

Chapter 12. The M Language

Abstract
Data ingestion and modification are not only interface-driven in Power Query. In fact, the entire process is underpinned and powered by a highly specific programming language. Called “M,” this language underlies everything that you have learned to do in the last 11 chapters.
Adam Aspin

Backmatter

Additional information

Premium Partner

    Image Credits