- •Contributors
- •Table of Contents
- •Preface
- •Technical requirements
- •Installing a Power BI gateway
- •Getting ready
- •How it works
- •Authentication to data sources
- •Getting ready
- •How it works
- •Main challenges that Power Query solves
- •Getting ready
- •Technical requirements
- •Getting data and connector navigation
- •Getting ready
- •Creating a query from files
- •Getting ready
- •How it works...
- •Creating a query from a folder
- •Getting ready
- •How it works...
- •Creating a query from a database
- •Getting ready
- •How it works...
- •Creating a query from a website
- •Getting ready
- •How it works...
- •Technical requirements
- •Exploring Power Query Editor
- •Getting ready
- •Managing columns
- •Getting ready
- •Using data profiling tools
- •Getting ready
- •Using Queries pane shortcuts
- •Getting ready
- •Using Query Settings pane shortcuts
- •Getting ready
- •Using Schema view and Diagram view
- •Getting ready
- •Technical requirements
- •Formatting data types
- •Getting ready
- •Using first rows as headers
- •Getting ready
- •Grouping data
- •Getting ready
- •Unpivoting and pivoting columns
- •Getting ready
- •Filling empty rows
- •Getting ready
- •Splitting columns
- •Getting ready
- •Extracting data
- •Getting ready
- •Parsing JSON or XML
- •Getting ready
- •Exploring artificial intelligence insights
- •Getting ready
- •Technical requirements
- •Merging queries
- •Getting ready
- •Joining methods
- •Getting ready
- •Appending queries
- •Getting ready
- •Combining multiple files
- •Getting ready
- •Using the Query Dependencies view
- •Getting ready
- •Technical requirements
- •Setting up parameters
- •Getting ready
- •Filtering with parameters
- •Getting ready
- •Folding queries
- •Getting ready
- •Leveraging incremental refresh and folding
- •Getting ready
- •Disabling query load
- •Getting ready
- •Technical requirements
- •Using M syntax and the Advanced Editor
- •Getting ready
- •Using M and DAX – differences
- •Getting ready
- •Using M on existing queries
- •Getting ready
- •Writing queries with M
- •Getting ready
- •Creating tables in M
- •Getting ready
- •Leveraging M – tips and tricks
- •Getting ready
- •Technical requirements
- •Adding columns from examples
- •Getting ready
- •Adding conditional columns
- •Getting ready
- •Adding custom columns
- •Getting ready
- •Invoking custom functions
- •Getting ready
- •Clustering values
- •Getting ready
- •Technical requirements
- •Using Power BI dataflows
- •Getting ready
- •Centralizing ETL with dataflows
- •Getting ready
- •Building dataflows with Power BI Premium capabilities
- •Getting ready
- •Understanding dataflow best practices
- •Getting ready
- •Technical requirements
- •Exploring diagnostics options
- •Getting ready
- •Managing a diagnostics session
- •Getting ready
- •Designing a report with diagnostics results
- •Getting ready
- •There's more…
- •Using Diagnose as a Power Query step
- •Getting ready
- •Other Books You May Enjoy
- •Index
384 Other Books You May Enjoy
Other Books You
May Enjoy
If you enjoyed this book, you may be interested in these other books by Packt:
Learn Power BI
Greg Deckler
ISBN: 978-1-83864-448-2
•Explore the different features of Power BI to create interactive dashboards
•Use the Query Editor to import and transform data
•Perform simple and complex DAX calculations to enhance analysis
•Discover business insights and tell a story with your data using Power BI
•Explore data and learn to manage datasets, dataflows, and data gateways
•Use workspaces to collaborate with others and publish your reports
Other Books You May Enjoy 385
Expert Data Modeling with Power BI
Soheil Bakhshi
ISBN: 978-1-80020-569-7
•Implement virtual tables and time intelligence functionalities in DAX to build a powerful model
•Identify Dimension and Fact tables and implement them in Power Query Editor
•Deal with advanced data preparation scenarios while building Star Schema
•Explore best practices for data preparation and data modeling
•Discover different hierarchies and their common pitfalls
•Understand complex data models and how to decrease the level of model complexity with different data modeling approaches
386
Packt is searching for authors like you
If you're interested in becoming an author for Packt, please visit authors. packtpub.com and apply today. We have worked with thousands of developers and tech professionals, just like you, to help them share their insight with the global tech community. You can make a general application, apply for a specific hot topic that we are recruiting an author for, or submit your own idea.
Share Your Thoughts
Now you've finished Power Query Cookbook, we'd love to hear your thoughts! If you purchased the book from Amazon, please click here to go straight to the Amazon review page for this book and share your feedback or leave a review on the site that you purchased it from.
Your review is important to us and the tech community and will help us make sure we're delivering excellent quality content.
A
administrators adding 8 Advanced Editor using 232-237
AdventureWorks data 16 artificial intelligence (AI) 143 artificial intelligence insights
exploring 143-147 authentication
to data sources 15-20 types 15
Azure Data Lake Storage integration 347-351
Azure SQL Database 16, 173
C
centralized ETL
creating, with dataflows 314-326 cluster values 298-302
cognitive services 143 columns
managing 78 pivoting 124-129 removing 81
Index
selecting 79, 80 splitting 132-136 unpivoting 124-129
columns from examples adding 270-278
Common Data Model (CDM) 347-351 computed entity 334
conditional columns about 278
adding 278-283 connector
navigating 36-38 connectors, categories
Azure 39 databases 38 file 38
online services 39 other 39
Power platform 39 connector type
changing 23-25 custom columns adding 283-289 custom functions
about 289 invoking 289-297
388 Index
D
data
extracting 136-139 grouping 120-124 obtaining 36-38
Data Analysis Expression (DAX) about 242
versus M 238-242 database connector
query, creating from 56-62 data gateway
managing, on Power BI portal 8-14 data profiling tools
using 82-90
data source permissions changing 21-23, 25
data sources authentication 15-20
managing, with Power Query 29-33 data types
formatting 108-117 data volumes
managing, with Power Query 29-33 Diagnose
using, as Power Query step 379-382 Diagram view
using 96-106
E
empty rows filling 129-131
Extract Table Using Examples 49
F
file connectors
query, creating from 39-49 folder connector
query, creating from 49-55 function formula, parts
function definition 292 input values, defining 292 value returned 292
G
Gateway Cluster Settings 9
H
headers
rows, using as 118-120
I
incremental refresh leveraging 212-223
J
joining methods using 156-164 JSON or XML
parsing 139-143
L
licensing options, Power BI reference link 327
linked entities 335
Index 389
M
M
queries, writing 250-259 tables, creating 259-264 tips and tricks 264-267
using, on existing queries 243-250 versus DAX 238-242
machine learning (ML) 147 Microsoft O365 6
M IntelliSense 242 M syntax
using 232-237 multiple files
combining 169-173
O
Oracle Data Access Components (ODAC) 62
P
parameters
filtering with 189-203 setting up 182-188
personal mode gateway 3 Power Automate 346 Power BI
about 6 URL 8
Power BI dataflow, best practices about 341
Azure Data Lake Storage integration 347-351
Common Data Model 347-351 data refresh, structuring 344-346 organizing 342-344
Power BI dataflows about 31
building, with Power BI Premium capabilities 326-341
staging 342, 343
used, for centralizing ETL 314-326 using 304-314
Power BI gateway download link 3 installing 3-7, 15
Power BI portal
data gateway, managing on 8-14 Power BI Premium capabilities
used, for building dataflow 326-341 Power Query
about 36, 242 solving, challenges 25
Power Query Desktop 37 Power Query Editor
exploring 72-77
Power Query online 38, 304 Power Query, solving challenges
consistent experience, providing across platforms 33
data sources, managing 29-33 data volumes, managing 29-33 low code tool, using 26, 27
Power Query step Diagnose, using 379-382
Q
Queries pane shortcuts using 91, 92
query
appending 165-168
creating, from database connector 56-62 creating, from file connectors 39-49
390 Index
creating, from folder connector 49-55 creating, from web connector 62-69 merging 150-155
M, using on 243-250 writing, with M 250-259
Query Dependencies view using 173-180
Query Diagnostics option exploring 354-357
Query Diagnostics results
used, for designing report 363-379 Query Diagnostics session
managing 358-363 query folding
leveraging 212-223 performing 203-212
query load disabling 223-230
Query Settings pane shortcuts using 93-96
R
Recovery key 6 report
designing, with Query Diagnostics results 363-379
REST API 346 rows
using, as headers 118-120
S
Schema view using 96-105
standard/enterprise mode gateway 3
T
tables
creating, in M 259-264
retrieving, by writing SQL statements 56 selecting/viewing 56
transformation dataflows 343
W
web connector
query, creating from 62-69