Muutke küpsiste eelistusi

E-raamat: Pro Data Mashup for Power BI: Powering Up with Power Query and the M Language to Find, Load, and Transform Data

  • Formaat: EPUB+DRM
  • Ilmumisaeg: 25-Aug-2022
  • Kirjastus: APress
  • Keel: eng
  • ISBN-13: 9781484285787
Teised raamatud teemal:
  • Formaat - EPUB+DRM
  • Hind: 67,91 €*
  • * hind on lõplik, st. muud allahindlused enam ei rakendu
  • Lisa ostukorvi
  • Lisa soovinimekirja
  • See e-raamat on mõeldud ainult isiklikuks kasutamiseks. E-raamatuid ei saa tagastada.
Pro Data Mashup for Power BI: Powering Up with Power Query and the M Language to Find, Load, and Transform DataSuurem pilt
  • Formaat: EPUB+DRM
  • Ilmumisaeg: 25-Aug-2022
  • Kirjastus: APress
  • Keel: eng
  • ISBN-13: 9781484285787
Teised raamatud teemal:

DRM piirangud

  • Kopeerimine (copy/paste):

    ei ole lubatud

  • Printimine:

    ei ole lubatud

  • Kasutamine:

    Digitaalõiguste kaitse (DRM)
    Kirjastus on väljastanud selle e-raamatu krüpteeritud kujul, mis tähendab, et selle lugemiseks peate installeerima spetsiaalse tarkvara. Samuti peate looma endale  Adobe ID Rohkem infot siin. E-raamatut saab lugeda 1 kasutaja ning alla laadida kuni 6'de seadmesse (kõik autoriseeritud sama Adobe ID-ga).

    Vajalik tarkvara
    Mobiilsetes seadmetes (telefon või tahvelarvuti) lugemiseks peate installeerima selle tasuta rakenduse: PocketBook Reader (iOS / Android)

    PC või Mac seadmes lugemiseks peate installima Adobe Digital Editionsi (Seeon tasuta rakendus spetsiaalselt e-raamatute lugemiseks. Seda ei tohi segamini ajada Adober Reader'iga, mis tõenäoliselt on juba teie arvutisse installeeritud )

    Seda e-raamatut ei saa lugeda Amazon Kindle's. 

This book provides all you need to find data from external sources and load and transform that data into Power BI where you can mine it for business insights and a competitive edge. This ranges from connecting to corporate databases such as Azure SQL and SQL Server to file-based data sources, and cloud- and web-based data sources. The book also explains the use of Direct Query and Live Connect to establish instant connections to databases and data warehouses and avoid loading data. 

The book provides detailed guidance on techniques for transforming inbound data into normalized data sets that are easy to query and analyze. This covers data cleansing, data modification, and standardization as well as merging source data into robust data structures that can feed into your data model. You will learn how to pivot and transpose data and extrapolate missing values as well as harness external programs such as R and Python into a Power Query data flow. You also will see how to handle errors in source data and extend basic data ingestion to create robust and parameterized data load and transformation processes. 

Everything in this book is aimed at helping you deliver compelling and interactive insight with remarkable ease using Power BI’s built-in data load and transformation tools.


What You Will Learn
  • Connect Power BI to a range of external data sources
  • Prepare data from external sources for easy analysis in Power BI 
  • Cleanse data from duplicates, outliers, and other bad values
  • Make live connections from which to refresh data quickly and easily
  • Apply advanced techniques to interpolate missing data


Who This Book Is For

All Power BI users from beginners to super users. Any user of the world’s leading dashboarding tool can leverage the techniques explained in this book to turbo-charge their data preparation skills and learn how a wide range of external data sources can be harnessed and loaded into Power BI to drive their analytics. No previous knowledge of working with data, databases, or external data sources is required—merely the need to find, transform, and load data into Power BI..



Intermediate-Advanced user level
About the Author xiii
About the Technical Reviewer xv
Acknowledgments xvii
Introduction xix
Chapter 1 Discovering and Loading Data with Power BI Desktop
1(20)
The Data Load Process
2(5)
Understanding Data Load
7(1)
The Navigator Dialog
8(2)
Searching for Usable Data
10(1)
Display Options
11(1)
Refresh
11(1)
The Navigator Data Preview
12(1)
Modifying Data
13(1)
Data Sources
14(2)
The Power B1 Desktop Screen
16(1)
The Get Data Dialog
16(3)
Conclusion
19(2)
Chapter 2 Discovering and Loading File-Based Data with Power BI Desktop
21(22)
File Sources
21(2)
Loading Data from Files
23(1)
CSV Files
23(6)
Text Files
29(2)
Text and CSV Options
31(1)
Simple XML Files
32(2)
Excel Files
34(3)
Microsoft Access Databases
37(2)
PDF Files
39(2)
JSON Files
41(1)
Conclusion
41(2)
Chapter 3 Loading Data from Databases and Data Warehouses
43(40)
Relational Databases
44(3)
SQL Server
47(5)
Automatically Loading Related Tables
52(1)
Database Options
52(4)
Searching for Tables
56(7)
Oracle Databases
63(5)
Other Relational Databases
68(3)
Microsoft SQL Server Analysis Services Data Sources
71(3)
Analysis Services Cube Tools
74(3)
SSAS Tabular Data Warehouses
77(3)
Import or Connect Live/DirectQuery
80(1)
Other Database Connections
80(1)
Conclusion
80(3)
Chapter 4 DirectQuery and Connect Live
83(26)
DirectQuery and Connect Live
83(3)
Microsoft SQL Server Data
86(7)
SQL Server Analysis Services Dimensional Data
93(3)
Microsoft SQL Server Analysis Services Tabular Data Sources
96(3)
DirectQuery with Non-Microsoft Databases
99(1)
DirectQuery and In-Memory Tables
100(1)
DirectQuery and Refreshing the Data
101(1)
DirectQuery Optimization
101(1)
Modifying Connections
102(3)
Changing Permissions
105(3)
Conclusion
108(1)
Chapter 5 Loading Data from the Web and the Cloud
109(46)
Web and Cloud Services
110(1)
Web Pages
110(1)
Online Services
110(2)
Microsoft Azure
112(1)
Web Pages
113(3)
Advanced Web Options
116(1)
Table View or Web View
117(1)
Salesforce
118(1)
Loading Data from Salesforce Objects
118(7)
Salesforce Reports
125(1)
Microsoft Dynamics 365
126(5)
Google Analytics
131(3)
OData Feeds
134(1)
OData Options
135(1)
Azure SQL Database
136(6)
Azure Synapse Analytics
142(3)
Connecting to SQL Server on an Azure Virtual Machine
145(2)
Azure Blob Storage
147(4)
Azure Databricks
151(3)
Azure Security
154(1)
Conclusion
154(1)
Chapter 6 Loading Data from Other Data Sources
155(32)
Other Sources
155(1)
Power BI Datasets
156(2)
Power BI Dataflows
158(3)
R Scripts
161(4)
R Options
165(1)
Python Scripts to Load Data
166(3)
Python Options
169(1)
Dataverse
170(1)
ODBC Sources
171(10)
Refreshing Data
181(1)
Refreshing the Entire Data in the Power BI Desktop In-Memory Model
181(1)
Refreshing an Individual Table
182(1)
Adding Your Own Data
183(2)
Conclusion
185(2)
Chapter 7 Power Query
187(26)
Power Bl Desktop Queries
188(1)
Editing Data After a Data Load
189(3)
Transforming Data Before Loading
192(2)
Transform or Load?
194(1)
Power Query
195(2)
The Applied Steps List
197(1)
Power Query Ribbons
197(8)
The View Ribbon
205(2)
The Tools Ribbon
207(1)
The Help Ribbon
208(1)
Viewing a Full Record
208(1)
Power Query Context Menus
209(2)
Conclusion
211(2)
Chapter 8 Structuring Data
213(42)
Dataset Shaping
214(1)
Renaming Columns
215(1)
Reordering Columns
215(2)
Removing Columns
217(1)
Choosing Columns
218(2)
Merging Columns
220(3)
Going to a Specific Column
223(2)
Removing Records
225(1)
Rows
226(4)
Removing Duplicate Records
230(1)
Sorting Data
231(2)
Reversing the Row Order
233(1)
Undoing a Sort Operation
233(1)
Filtering Data
234(1)
Selecting Specific Values
235(1)
Finding Elements in the Filter List
236(2)
Filtering Text Ranges
238(1)
Filtering Numeric Ranges
238(1)
Filtering Date and Time Ranges
239(2)
Filtering Data
241(2)
Applying Advanced Filters
243(2)
Excluding Rows Where a Value Is Missing
245(1)
Grouping Records
246(1)
Simple Groups
247(2)
Complex Groups
249(3)
Saving Changes in Power Query
252(1)
Exiting Power Query
252(1)
Conclusion
253(2)
Chapter 9 Shaping Data
255(38)
Merging Data
256(1)
Adding Data
256(5)
Aggregating Data During a Merge Operation
261(4)
Types of Join
265(1)
Joining on Multiple Columns
266(2)
Fuzzy Matching
268(1)
Fuzzy Matching Options
269(1)
Merge As New Query
270(1)
Preparing Datasets for Joins
271(1)
Correct and Incorrect Joins
271(1)
Examining Joined Data
272(2)
Appending Data
274(1)
Adding the Contents of One Query to Another
274(3)
Appending the Contents of Multiple Queries
277(2)
Changing the Data Structure
279(1)
Unpivoting Tables
279(3)
Pivoting Tables
282(2)
Transposing Rows and Columns
284(1)
Data Quality Analysis
284(1)
Column Quality
285(1)
Column Distribution
285(1)
Column Profile
286(2)
Profiling the Entire Dataset
288(1)
Correcting Anomalies
289(2)
Data Transformation Approaches
291(1)
Conclusion
291(2)
Chapter 10 Data Cleansing
293(28)
Using the First Row As Headers
294(1)
Changing Data Type
295(3)
Detecting Data Types
298(1)
Data Type Indicators
298(2)
Switching Data Types
300(1)
Data Type Using Locale
301(1)
Replacing Values
302(3)
Transforming Column Contents
305(1)
Text Transformation
305(2)
Adding a Prefix or a Suffix
307(1)
Removing Leading and Trailing Spaces
307(1)
Removing Nonprinting Characters
308(1)
Number Transformations
309(10)
Conclusion
319(2)
Chapter 11 Data Transformation
321(26)
Filling Down Empty Cells
322(4)
Extracting Part of a Column's Contents
326(1)
Advanced Extract Options
327(3)
Duplicating Columns
330(1)
Splitting Columns
330(1)
Splitting Column by a Delimiter
331(3)
Advanced Options for Delimiter Split
334(1)
Splitting Columns by Number of Characters
335(2)
Splitting Columns by Character Switch
337(1)
Merging Columns
337(2)
Creating Columns from Examples
339(2)
Adding Conditional Columns
341(3)
Index Columns
344(1)
Conclusion
345(2)
Chapter 12 Complex Data Structures
347(40)
Adding Multiple Files from a Source Folder
348(3)
Filtering Source Files in a Folder
351(4)
Displaying and Filtering File Attributes
355(2)
The List Tools Transform Ribbon
357(1)
Parsing XML Data from a Column
358(3)
Parsing JSON Data from a Column
361(2)
Complex JSON Files
363(2)
Complex XML Files
365(4)
Python and R Scripts
369(1)
Using Python Scripts to Modify Data
370(3)
Using R Scripts to Modify Data
373(2)
Convert a Column to a List
375(1)
Query Folding
376(4)
Reusing Data Sources
380(3)
Pinning a Data Source
383(1)
Copying Data from Power Query
384(1)
Conclusion
385(2)
Chapter 13 Organizing, Managing, and Parameterizing Queries
387(46)
Managing the Transformation Process
388(1)
Modifying a Step
389(1)
Renaming a Step
390(1)
Deleting a Step or a Series of Steps
390(2)
Modifying an Existing Step
392(3)
Adding a Step
395(1)
Altering Process Step Sequencing
395(1)
An Approach to Sequencing
395(2)
Error Records
397(1)
Removing Errors
397(1)
Managing Queries
398(1)
Organizing Queries
398(1)
Grouping Queries
399(3)
Duplicating Queries
402(1)
Referencing Queries
402(3)
Documenting Queries
405(1)
Adding a Column As a New Query
406(2)
Enabling Data Load
408(1)
Enabling Report Refresh
409(1)
Pending Changes
410(1)
Parameterizing Queries
410(1)
Creating a Simple Parameter
411(2)
Creating a Set of Parameter Values
413(3)
Creating a Query-Based Parameter
416(3)
Modifying a Parameter
419(1)
Applying a Parameter When Filtering Records
420(2)
Modifying the Current Value of a Parameter
422(1)
Applying a Parameter in a Search and Replace
423(1)
Applying a Parameter to a Data Source
424(3)
Other Uses for Parameters
427(1)
Using Parameters in the Data Source Step
428(1)
Applying a Parameter to a SQL Query
429(2)
Query Icons
431(1)
Power BI Templates with Parameters
431(1)
Conclusion
432(1)
Chapter 14 The M Language
433(42)
What Is the M Language?
434(1)
M and the Power Query Editor
435(1)
Modifying the Code for a Step
436(2)
M Expressions
438(1)
Writing M by Adding Custom Columns
439(2)
The Advanced Editor
441(1)
Expressions in the Advanced Editor
442(1)
The Let Statement
443(1)
Modifying M in the Advanced Editor
444(1)
Syntax Checking
445(1)
Advanced Editor Options
446(1)
Basic M Functions
447(1)
Text Functions
448(2)
M or DAX?
450(1)
Number Functions
450(2)
Date Functions
452(2)
Time Functions
454(1)
Duration Functions
454(1)
M Concepts
455(1)
M Data Types
456(2)
M Values
458(1)
Defining Your Own Variables in M
458(1)
Writing M Queries
459(1)
M Autocomplete
460(2)
Lists
462(1)
Creating Lists Manually
462(2)
Generating Sequences Using Lists
464(1)
Accessing Values from a List
465(1)
List Functions
465(1)
Records
466(2)
Tables
468(2)
Other Function Areas
470(1)
Custom Functions in M
470(2)
Adding Comments to M Code
472(1)
Single-Line Comments
473(1)
Multiline Comments
473(1)
Conclusion
473(2)
Appendix A Sample Data 475(1)
Sample Data 475(1)
Downloading the Sample Data 475(2)
Index 477
Adam Aspin is an independent Business Intelligence consultant based in the United Kingdom. He has worked with SQL Server for over 25 years. During this time, he has developed several dozen reporting and analytical systems based on the Microsoft Data Platform. 





A graduate of Oxford University, Adam began his career in publishing before moving into IT. Databases soon became a passion, and his experience in this arena ranges from dBase to Oracle, and Access to MySQL, with occasional sorties into the world of DB2. He is, however, most at home in the Microsoft universe when using SQL Server Analysis Services, SQL Server Reporting Services, SQL Server Integration Services, Azure Data Factory, Azure Synapse and, of course, Power BI. 





Business Intelligence has been Adam's principal focus for 20 years. He has applied his skills for a range of clients in a range of industry sectors. He is the author of SQL Server Data Integration Recipes; Pro Power BI Desktop (now in its third edition); Business Intelligence with SQL Server Reporting Services; High Impact Data Visualization; Data Mashup using Microsoft Excel using Power Query and M, and Pro Power BI Theme Creationall with Apress. 





A fluent French speaker, Adam has worked in France and Switzerland for many years.
Lisainfo e-raamatute kohta Lisainfo e-raamatute kohta
Püsilink: https://www.kriso.ee/db/97814842857876e.html
Märksõnad: