| Description |
1 online resource (xv, 150 pages) : illustrations |
| Series |
Expert's voice in business intelligence |
|
Expert's voice in business intelligence.
|
| Summary |
This book is a practical guide to Power Query (part of the Microsoft Power BI suite) which automates the process of getting data into Excel. Topics covered include: importing data from sources including relational databases, text files, web pages, Excel workbooks, Facebook, Windows Azure Marketplace, Wikipedia, and OData feeds; building repeatable processes to filter, clean, aggregate, and transform data; loading data into an Excel table or directly into the Excel 2013 Data Model; writing complex expressions in Power Query's M expression language; sharing queries and their data with other users; and positioning data for further analysis using Power View, Power Map, Power Pivot, and the rest of Microsoft's Power BI suite. This book is for readers interested in business intelligence from data analysis. |
| Contents |
Machine generated contents note: ch. 1 Introducing Power Query -- Power Query and Power BI -- Power BI Components -- Power Query and Power BI Licensing -- Installing Power Query -- Power Query Concepts -- Power Query Walkthrough -- Creating a Simple Query -- Editing an Existing Query -- Why Use Power Query? -- Summary -- ch. 2 Power Query Data Sources -- Querying Relational Databases -- Connecting to SQL Server -- Navigating through a Database in the Query Editor -- Connecting to Other Databases -- Extracting Data from Files -- Working with CSV Files -- Working with Text Files -- Working with XML Files -- Working with JSON Files -- Working with Excel Files -- Working with Folders and Multiple Files -- Working with Data from the Windows File System -- Combining Data from Multiple Text Files -- Working with Data from the Current Excel Workbook -- Working with Data from the Web -- Scraping Data from Web Pages -- Calling a Web Service -- Finding Data Using Power Query Online Search -- Using Other Queries as Data Sources -- Referencing Entire Queries -- Duplicating Queries -- Using Individual Values from Queries -- OData Data Sources -- Working with Generic OData Web Services -- Working with Data from Excel Workbooks Stored in SharePoint -- Working with Data from SharePoint Lists -- Working with Data from the Windows Azure Marketplace -- Working with Data from Windows Azure Blob Storage and Table Storage -- Working with Data from HDFS and HDInsight -- Working with Active Directory Data -- Working with Data from Microsoft Exchange -- Working with Data from Facebook -- Working with Data from SAP BusinessObjects -- Reusing Recent Data Sources -- Managing Credentials -- Importance of Locale -- Setting a Default Locale -- CSV Files and Code Pages -- Summary -- ch. 3 Transforming Data with Power Query -- Queries and Steps -- Working with Columns -- Naming Columns -- Moving Columns -- Removing Columns -- Splitting Columns -- Merging Columns -- Setting the Data Type of a Column -- Changing Data Types and Locales -- Filtering Rows -- Filtering Rows Using Auto-Filter -- Filtering Rows Using Number, Text, and Date Filters -- Filtering Rows by Range -- Removing Duplicate Values -- Filtering Out Rows with Errors -- Sorting a Table -- Changing Values in a Table -- Replacing Values with Other Values -- Text Transforms -- Number Transforms -- Date/Time/Duration Transforms -- Filling Up and Down to Replace Missing Values -- Aggregating Values -- Unpivoting Columns to Rows -- Transposing a Table -- Creating Custom Columns -- Built-in Custom Columns -- Custom Columns with M Calculations -- Summary -- ch. 4 Data Destinations -- Choosing a Destination for Your Data -- Loading Data to the Worksheet -- Using the Default Excel Table Output -- Loading Data to Your Own Excel Tables -- Loading Data to the Excel Data Model -- Viewing Tables in the Excel Data Model -- Advantages of Using the Excel Data Model -- Power Query and Table Relationships -- Breaking Changes -- Refreshing Queries -- Refreshing Queries Manually -- Automating Data Refresh -- Summary -- ch. 5 Introduction to M -- Writing M in the Query Editor -- Formula Bar -- Advanced Editor Window -- Creating a Blank Query -- M Language Concepts -- Expressions, Values, and Let statements -- Writing M -- Lists, Records, and Tables -- Lists -- Records -- Tables -- Selections and Projections -- Functions -- Defining Functions Inside a Query -- each Expressions -- Queries As Functions -- let Expressions in Function Definitions -- Recursive Functions -- Functions Imported from Data Sources -- Working with Web Services -- Query Folding -- Monitoring Query Folding in SQL Server -- Preventing Query Folding in Code -- Other Operations That May Prevent Query Folding -- Summary -- ch. 6 Working with Multiple Queries -- Using One Query as a Source for Another -- Referencing Queries in Code -- Creating Parameterized Queries -- Working with Data from Different, External Data Sources -- Data Privacy Settings -- Formula Firewall -- Fast Combine Option -- Appending Data from One Query onto Another -- Appending Queries in the User Interface -- Appending in M -- Merging Two Queries -- Merging Queries in the User Interface -- Merging in M -- Summary -- ch. 7 Power Query and Power BI for Office 365 -- Sharing and Using Shared Queries in Power Query -- Sharing queries -- Consuming Shared Queries -- Updating Queries That Have Been Shared -- Managing Shared Queries in the Power BI Data Catalog -- Finding Your My Power BI page -- Viewing Shared Queries -- Viewing Usage Analytics -- Managing data sources -- Data Steward -- Who Is the Data Steward? -- Certifying Queries -- Which Queries Should Be Shared? -- Sharing Functions -- Power BI for Office 365 Data Refresh -- Supported Data Sources -- Enabling Scheduled Refresh -- Summary -- ch. 8 Power Query Recipes -- Calculations -- Percentage Share of Grand Total -- Percentage Growth in Sales from the Previous Day -- Tied Ranks -- Counting the Number of Distinct Customers -- Table Transformations -- Converting a Single-Column Table to a Multiple-Column Table -- Finding New, Lost, and Returning Customers -- Generating a Date Table -- How Long Was a Stock Price Above a Given Value? -- Working with Data from the Web -- Web-Scraping Weather Forecast Data -- Finding the Driving Distance Between Two Locations Using the Bing Maps Route Web Service -- Summary. |
| Subject |
Microsoft Excel (Computer file)
|
|
Microsoft Excel (Computer file) |
|
Microsoft Excel (Computer file) |
|
Querying (Computer science)
|
|
Database searching.
|
|
Bases de données -- Interrogation. |
|
online searching. |
|
Database searching |
|
Querying (Computer science) |
| Indexed Term |
computerwetenschappen |
|
computer sciences |
|
computertechnieken |
|
computer techniques |
|
Information and Communication Technology (General) |
|
Informatie- en communicatietechnologie (algemeen) |
| ISBN |
9781430266921 (electronic bk.) |
|
1430266929 (electronic bk.) |
| Standard No. |
10.1007/978-1-4302-6692-1 doi |
|
More Resources