Power Query and Power BI for Office 365

Contents About the Author ................................................................................................................ xv About the Technical Reviewer .......................................................................................... xvii Acknowledgments ............................................................................................................. xix Preface .............................................................................................................................. xxi NChapter 1: Introducing Power Query .................................................................................1 Power Query and Power BI............................................................................................................1 Power BI Components ........................................................................................................................................... 1 Power Query and Power BI Licensing.................................................................................................................... 5 Installing Power Query .......................................................................................................................................... 6 Power Query Concepts..................................................................................................................6 Power Query Walkthrough.............................................................................................................7 Creating a Simple Query........................................................................................................................................ 7 Editing an Existing Query....................................................................................................................................... 9 Why Use Power Query? ....................................................................................................................................... 13 Summary.....................................................................................................................................15 NChapter 2: Power Query Data Sources.............................................................................17 Querying Relational Databases ...................................................................................................17 Connecting to SQL Server.................................................................................................................................... 17 Navigating through a Database in the Query Editor ............................................................................................ 22 Connecting to Other Databases ........................................................................................................................... 26 Extracting Data from Files...........................................................................................................26 Working with CSV Files........................................................................................................................................ 27 Working with Text Files........................................................................................................................................ 28 vii

N CONTENTS Working with XML Files ....................................................................................................................................... 29 Working with JSON Files ..................................................................................................................................... 30 Working with Excel Files...................................................................................................................................... 31 Working with Folders and Multiple Files .....................................................................................32 Working with Data from the Windows File System.............................................................................................. 32 Combining Data from Multiple Text Files ............................................................................................................. 33 Working with Data from the Current Excel Workbook .................................................................34 Working with Data from the Web ................................................................................................35 Scraping Data from Web Pages ........................................................................................................................... 35 Calling a Web Service .......................................................................................................................................... 38 Finding Data Using Power Query Online Search.................................................................................................. 38 Using Other Queries as Data Sources .........................................................................................43 Referencing Entire Queries.................................................................................................................................. 43 Duplicating Queries ............................................................................................................................................. 45 Using Individual Values from Queries .................................................................................................................. 45 OData Data Sources ....................................................................................................................46 Working with Generic OData Web Services ......................................................................................................... 46 Working with Data from Excel Workbooks Stored in SharePoint......................................................................... 47 Working with Data from SharePoint Lists............................................................................................................ 48 Working with Data from the Windows Azure Marketplace .................................................................................. 49 Working with Data from Windows Azure Blob Storage and Table Storage .......................................................... 52 Working with Data from HDFS and HDInsight .............................................................................52 Working with Active Directory Data.............................................................................................53 Working with Data from Microsoft Exchange..............................................................................53 Working with Data from Facebook..............................................................................................54 Working with Data from SAP BusinessObjects............................................................................55 Reusing Recent Data Sources .....................................................................................................56 Managing Credentials .................................................................................................................57 The Importance of Locale............................................................................................................58 viii

N CONTENTS Setting a Default Locale ...................................................................................................................................... 59 CSV Files and Code Pages ................................................................................................................................... 60 Summary.....................................................................................................................................61 NChapter 3: Transforming Data with Power Query ............................................................63 Queries and Steps .......................................................................................................................63 Working with Columns ................................................................................................................65 Naming Columns ................................................................................................................................................. 65 Moving Columns .................................................................................................................................................. 66 Removing Columns.............................................................................................................................................. 66 Splitting Columns ................................................................................................................................................ 66 Merging Columns ................................................................................................................................................ 69 Setting the Data Type of a Column ...................................................................................................................... 70 Changing Data Types and Locales ....................................................................................................................... 72 Filtering Rows .............................................................................................................................72 Filtering Rows Using Auto-Filter .......................................................................................................................... 72 Filtering Rows Using Number, Text, and Date Filters ........................................................................................... 73 Filtering Rows by Range...................................................................................................................................... 76 Removing Duplicate Values ................................................................................................................................. 78 Filtering Out Rows with Errors............................................................................................................................. 79 Sorting a Table.............................................................................................................................79 Changing Values in a Table..........................................................................................................80 Replacing Values with Other Values .................................................................................................................... 80 Text Transforms ................................................................................................................................................... 81 Number Transforms ............................................................................................................................................. 83 Date/Time/Duration Transforms........................................................................................................................... 84 Filling Up and Down to Replace Missing Values .................................................................................................. 86 Aggregating Values .....................................................................................................................88 Unpivoting Columns to Rows.......................................................................................................91 Transposing a Table.....................................................................................................................93 ix

N CONTENTS Creating Custom Columns...........................................................................................................94 Built-in Custom Columns..................................................................................................................................... 94 Custom Columns with M Calculations ................................................................................................................. 97 Summary.....................................................................................................................................98 NChapter 4: Data Destinations...........................................................................................99 Choosing a Destination for Your Data..........................................................................................99 Loading Data to the Worksheet .................................................................................................100 Using the Default Excel Table Output................................................................................................................. 101 Loading Data to Your Own Excel Tables............................................................................................................. 101 Loading Data to the Excel Data Model ......................................................................................103 Viewing Tables in the Excel Data Model ............................................................................................................ 103 Advantages of Using the Excel Data Model ....................................................................................................... 106 Power Query and Table Relationships ............................................................................................................... 107 Breaking Changes ............................................................................................................................................. 110 Refreshing Queries....................................................................................................................110 Refreshing Queries Manually............................................................................................................................. 111 Automating Data Refresh................................................................................................................................... 112 Summary...................................................................................................................................114 NChapter 5: Introduction to M .........................................................................................115 Writing M in the Query Editor ....................................................................................................115 The Formula Bar ................................................................................................................................................ 115 The Advanced Editor Window ............................................................................................................................ 116 Creating a Blank Query...................................................................................................................................... 117 M Language Concepts...............................................................................................................118 Expressions, Values, and Let statements........................................................................................................ 118 Writing M ........................................................................................................................................................... 119 x

N CONTENTS Lists, Records, and Tables .........................................................................................................123 Lists ................................................................................................................................................................... 123 Records.............................................................................................................................................................. 126 Tables ................................................................................................................................................................ 127 Selections and Projections ................................................................................................................................ 135 Functions...................................................................................................................................138 Defining Functions Inside a Query..................................................................................................................... 138 each Expressions .............................................................................................................................................. 139 Queries As Functions ......................................................................................................................................... 140 let Expressions in Function Definitions ........................................................................................................... 142 Recursive Functions .......................................................................................................................................... 143 Functions Imported from Data Sources............................................................................................................. 143 Working with Web Services.......................................................................................................143 Query Folding ............................................................................................................................145 Monitoring Query Folding in SQL Server ........................................................................................................... 145 Preventing Query Folding in Code ..................................................................................................................... 146 Other Operations That May Prevent Query Folding............................................................................................ 147 Summary...................................................................................................................................148 NChapter 6: Working with Multiple Queries.....................................................................149 Using One Query as a Source for Another .................................................................................149 Referencing Queries in Code ............................................................................................................................. 149 Creating Parameterized Queries........................................................................................................................ 150 Working with Data from Different, External Data Sources ........................................................152 Data Privacy Settings......................................................................................................................................... 152 The Formula Firewall......................................................................................................................................... 154 The Fast Combine Option................................................................................................................................... 156 Appending Data from One Query onto Another .........................................................................157 Appending Queries in the User Interface........................................................................................................... 158 Appending in M.................................................................................................................................................. 160 Merging Two Queries.................................................................................................................161 xi

N CONTENTS Merging Queries in the User Interface............................................................................................................... 161 Merging in M ..................................................................................................................................................... 164 Summary...................................................................................................................................166 NChapter 7: Power Query and Power BI for Office 365....................................................167 Sharing and Using Shared Queries in Power Query ..................................................................167 Sharing queries ................................................................................................................................................. 167 Consuming Shared Queries ............................................................................................................................... 171 Updating Queries That Have Been Shared......................................................................................................... 172 Managing Shared Queries in the Power BI Data Catalog ..........................................................172 Finding Your My Power BI page ......................................................................................................................... 172 Viewing Shared Queries .................................................................................................................................... 173 Viewing Usage Analytics.................................................................................................................................... 174 Managing data sources ..................................................................................................................................... 175 The Data Steward......................................................................................................................177 Who Is the Data Steward? ................................................................................................................................. 177 Certifying Queries .............................................................................................................................................. 178 Which Queries Should Be Shared? .................................................................................................................... 179 Sharing Functions.............................................................................................................................................. 180 Power BI for Office 365 Data Refresh........................................................................................180 Supported Data Sources.................................................................................................................................... 180 Enabling Scheduled Refresh.............................................................................................................................. 181 Summary...................................................................................................................................187 NChapter 8: Power Query Recipes ...................................................................................189 Calculations...............................................................................................................................189 Percentage Share of Grand Total ....................................................................................................................... 189 Percentage Growth in Sales from the Previous Day .......................................................................................... 193 Tied Ranks......................................................................................................................................................... 199 Counting the Number of Distinct Customers ..................................................................................................... 202 xii

N CONTENTS Table Transformations ...............................................................................................................205 Converting a Single-Column Table to a Multiple-Column Table......................................................................... 205 Finding New, Lost, and Returning Customers.................................................................................................... 210 Generating a Date Table..................................................................................................................................... 217 How Long Was a Stock Price Above a Given Value? .......................................................................................... 223 Working with Data from the Web ..............................................................................................230 Web-Scraping Weather Forecast Data............................................................................................................... 230 Finding the Driving Distance Between Two Locations Using the Bing Maps Route Web Service...................... 238 Summary...................................................................................................................................242 Index.................................................................................................................................243 xiii

About the Author Chris Webb is an independent consultant and trainer based in the UK. He has over 15 years’ experience with the Microsoft BI stack and has worked in a variety of roles (including several years with Microsoft Consulting Services) across a wide range of industries. A regular speaker at user groups and conferences around the world, such as the PASS Summit and the PASS Business Analytics Conference, he is also one of the organizers of the SQLBits conference in the UK and has received Microsoft’s Most Valuable Professional award for nine years running. He is the co-author of several books such as MDX Solutions, Expert Cube Development with SQL Server Analysis Services, and SQL Server Analysis Services 2012: The BISM Tabular Model, and he blogs about Microsoft BI at http://cwebbbi.wordpress.com. More information about his consultancy can be found at www.crossjoin.co.uk and about his training courses at www.technitrain.com. xv

About the Technical Reviewer Jen Underwood has almost 20 years of hands-on experience in the data warehousing, business intelligence, reporting, and predictive analytics industry. Prior to launching Impact Analytix, she was a Microsoft Global Business Intelligence Technical Product Manager responsible for technical product marketing and field readiness for a $10+ billion market suite of analytics offerings spanning across Microsoft SQL Server, Office, and SharePoint. She also held roles as an Enterprise Data Platform Specialist, Tableau Technology Evangelist, and a Business Intelligence Consultant for Big 4 Systems Integration firms. Throughout most of her career she has been researching, designing, and implementing analytic solutions across a variety of open source, niche, and enterprise vendor landscapes including Microsoft, Oracle, IBM, and SAP. As a seasoned industry analyst, presenter, author, blogger, and trainer, Jen is quite active in the global technical community. Recently she was honored with a Boulder BI Brain Trust (BBBT) membership, a Tableau Zen Master (MVP) award, a Worldwide PASS Excel BI Chapter leadership role, and a Dun & Bradstreet MVP. She writes articles for BeyeNetwork, SQL Server Pro, and other industry media channels. Jen holds a Bachelor of Business Administration degree from the University of Wisconsin, Milwaukee and a postgraduate certificate in Computer Science—Data Mining from the University of California, San Diego. xvii

Acknowledgments I would like to thank the following people for their help (either directly or indirectly) in learning Power Query and writing this book: Jen Underwood, Melissa Coates, Andrea Uggetti, Faisal Mohamood, Miguel Llopis, Reza Rad, Douglas Day, Ron Pihlgren, Steve Wright, Matt Masson, Theresa Palmer-Boroski, Steven Peters, Jimmy Haley, Bob Phillips, John White, Lee Hawthorne, Curt Hagenlocher, Oliver Engels, Matthew Roche, Kasper de Jonge, Jamie Thomson, Andrew Fox, Zafar Abbas, and Marco Russo. xix

Preface It’s easy to create amazing demos with Microsoft’s new Power BI suite of tools. If you’ve bought this book you’ve probably already seen more than your fair share: See millions of values summed up in a second using Power Pivot! Watch bubbles bounce around a chart with Power View! Zoom over a 3D landscape with Power Map! Talk to your data with Q&A! Quite often Power Query is only shown for a few seconds, if it’s shown at all. And yet… anyone who tries to do any real work with Power BI soon realizes that Power Query is the real star of the stack. I fell in love with it as soon as I saw the first experimental versions, back when it was called Data Explorer, and I’ve watched it grow up into an immensely capable tool - one that is both easy-to-use for the novice analyst and extremely flexible for the experienced BI consultant. I expect that the millions of Excel users who have already embraced Power Pivot will find Power Query just as useful. One of the best things about Power Query, however, presents a big problem for this book. Over the last year development team have been releasing new versions of Power Query nearly every month: new functionality has been added, the user interface has been altered and improved, and bugs have been fixed. This book was written in the first few months of 2014 and uses the May 2014 release (version 2.11.3660.141) as its baseline. However by the time you read this it is certain that further versions of Power Query will have appeared and as a result some parts of this book will be out of date. This is the unavoidable consequence of Microsoft’s new policy of accelerated release cycles and I hope it does not spoil your enjoyment of this book. Even if new features have been added and the user interface has changed, I expect the core functionality of Power Query to stay the same. This book is structured as follows. Chapter 1 provides an overview of Power Query, what it does and how it fits into the wider Power BI stack; it also introduces the Power Query user interface. Chapters 2, 3 and 4 then go into more detail about the functionality available from the user interface, covering how to extract data from all of the supported data sources, transforming that data, and loading that data into the worksheet or the Excel Data Model. Put together these chapters deal with almost all of the functionality that the casual Power Query user will ever need. Chapter 5 has a more technical focus: it deals with the M language that underpins Power Query. Learning M is necessary if you want to go beyond the basics of Power Query but if you are not comfortable with programming then you may want to skim over this chapter. Chapter 6 looks at how to work with multiple queries, and while it has a certain amount of M code in it you do not need a deep understanding of M for many of the subjects it discusses. Chapter 7 shows how to share Power Query queries via a Power BI site and is only relevant if you have purchased a Power BI subscription. Finally, chapter 8 consists of a series of step-by-step examples showing how you can solve common business problems using Power Query and is recommended for all readers. xxi