| Description |
1 online resource (404 pages) |
| Contents |
Intro -- Table of Contents -- About the Author -- About the Technical Reviewer -- Acknowledgments -- Introduction -- What Is Power Query? -- Connecting to Source Data -- Data Transformation -- Loading into a Worksheet or the Data Model -- Integrating Power Query into Daily Workflows -- The Evolution of Power Query -- How to Use This Book -- On to Learning Power Query -- Chapter 1: Using Power Query to Discover and Load Data into Excel -- Power Query -- The Data Load Process -- Why Use Power Query? -- The Queries & Connections Pane -- Displaying the Queries & Connections Pane |
|
The Peek Window -- Peek Window Options -- View in Worksheet -- Deleting a Query -- Understanding Data Load -- The Navigator Dialog -- Select Multiple Source Tables -- Searching for Datasets -- Navigator Display Options -- Only Selected Items -- Enable Data Previews -- Refresh -- Source Data Refresh -- Data Preview Refresh -- Select Related Tables -- The Navigator Data Preview -- Modifying Data -- The Power Query Editor -- Data Sources -- Source Data Properties -- Query Properties -- Load Destinations -- Repurposing an Existing Connection -- Load to Excel -- Load to the Data Model -- Conclusion |
|
Chapter 2: Discovering and Loading File-Based Data with Power Query -- File Sources -- Loading Data from Files -- CSV Files -- What Is a CSV File? -- Text Files -- Text and CSV Options -- File Origin -- Delimiter -- Data Type Detection -- Fixed-Width Text Files -- Simple XML Files -- Excel Files -- Why Use Power Query to Connect to Excel -- From Table/Range -- Microsoft Access Databases -- JSON Files -- Conclusion -- Chapter 3: Loading Data from Databases and Data Warehouses -- Relational Databases -- SQL Server -- Automatically Loading Related Tables -- Database Options -- Server Connection |
|
Searching for Databases, Tables, and Views in Navigator -- Searching for Databases -- Searching for Tables -- Database Security -- Using a SQL Statement -- Stored Procedures in SQL Server -- Oracle Databases -- Other Relational Databases -- Microsoft SQL Server Analysis Services Data Sources -- From Analysis Services -- SSAS Tabular Data Warehouses -- Types of Credentials When Connecting -- Unable to Connect -- Other Database Connections -- Conclusion -- Chapter 4: Loading Data from the Web and the Cloud -- Web and Cloud Services -- Web Pages -- Online Services -- Microsoft Azure -- Web Pages |
|
Advanced Web Options -- Viewing the Source Web Page -- Salesforce -- Loading Data from Salesforce Objects -- Salesforce Reports -- Microsoft Dynamics 365 -- Azure SQL Database -- Azure SQL Data Warehouse (Azure Synapse Analytics) -- Connecting to SQL Server on an Azure Virtual Machine -- Azure Blob Storage -- Azure Security -- Other Source Types -- Conclusion -- Chapter 5: Generic Data Sources -- ODBC Sources -- OLE DB Data Sources -- OData Feeds -- OData Options -- Refreshing Data -- Refreshing the Entire Data in the Excel In-Memory Model -- Refreshing an Individual Query -- Conclusion |
| Summary |
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 Excels 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 worlds leading analytics tool. You will: Connect to a range of external data, from databases to Azure sources Ingest data directly into your spreadsheets, or into PowerPivot data models Cleanse and prepare external data so it can be used inside Excel Refresh data quickly and easily to always have the latest information Transform data into ready-to-use structures that fit the spreadsheet format Execute M language functions for complex data transformations |
| Notes |
Chapter 6: Structuring Imported Data |
|
Includes index |
|
Print version record |
| SUBJECT |
Microsoft Excel (Computer file) http://id.loc.gov/authorities/names/n86025775
|
|
Microsoft Excel (Computer file) fast |
| Subject |
Electronic data processing.
|
|
Statistical matching.
|
|
Microsoft programming.
|
|
Computers -- Programming -- Microsoft Programming.
|
|
Electronic data processing
|
|
Statistical matching
|
| Form |
Electronic book
|
| ISBN |
9781484260180 |
|
148426018X |
|
