Cargando…
Power query for Power BI and Excel /
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...
| Clasificación: | Libro Electrónico |
|---|---|
| Autor principal: | |
| Formato: | Electrónico eBook |
| Idioma: | Inglés |
| Publicado: |
[Berkeley, CA] :
Apress,
2014.
|
| Colección: | Expert's voice in business intelligence.
|
| Temas: | |
| Acceso en línea: | Texto completo (Requiere registro previo con correo institucional) |
Tabla de Contenidos:
- 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.