Power Query Skills Apply to Excel, Power BI and SSAS Tabular

Did you know that if you learn to use Power Query to “Get” and transform data in one of these Microsoft Business Intelligence & data analysis tools, you actually have the skills to use any of them?  Power Query is an amazing technology for acquiring and wrangling data from a vast portfolio of data sources, and it can be used to perform simple and very advanced data transformations.  In my interview with Chris Webb at the 2017 PASS Summit, he said that Power Query is everywhere and being added to more and more Microsoft products.  We can expect to see Power Query in web-based tools for use with cloud services.  Today, it is the “Get Data experience” on the Data ribbon in Excel, Power BI Desktop and now in SQL Server Data Tools (SSDT) for SSAS 2017 Tabular projects.  The adoption rate  for Power Query has been fantastic and it is a tool that once you learn the basics, you just can’t go back and use older tools.  Power Query makes data wrangling and transformations a creative process rather than simply an exercise in connecting the dots (or tasks and transforms).  The Query Editor generates “M” Script, a powerful and flexible data mashup language that translates transformation steps into native query code.

In this tutorial, I use Excel 2016, Power BI Desktop and SSDT for Visual Studio 2017 to create three different sample projects.  Using a few of the lab data files from my Mastering Power BI Workshop course, I import a folder full of CSV files; using the same technique in each of the three tools.  This demonstration makes the point the Power Query generally works the same in workbook, desktop and enterprise BI and analytics solutions.  There are a few subtle differences to be aware of.  By default, Excel uses the Power Query output to populate worksheet tables in addition to the embedded Power Query data model.  In the new SSAS Tabular designer within SSDT, you define connections first, and then use the Tabular Model Explorer to import tables from established connections.  File, folder and data source connections are also managed a little differently in an SSAS project because the deployed databases is managed by the SSAS service account.

Advertisements

Spontaneous Interviews at PASS Summit 2017

Conversations with Julie Koesmarno, Olivier Matrat, Aaron Nelson, Seth Bauer and Robert Bruckner captured in video interviews below…

Continuing my video blog series of interviews from PASS Summit, I had the opportunity to catch-up with several Microsoft BI and Data Platform industry leaders amid the crowds and between sessions.  Stay tuned to this station for many more interviews and insider information about the Microsoft Business Intelligence and Data Platform.

I caught up with Julie Koesmarno in the Community Zone, a few days after travelling together on the SQL Train (aka “Oregon SQL Party Train to Seattle”) from Oregon SQL Saturday the weekend before Summit.  She’s been a non-stop community advocate for several years, and continues to speak at events all over.  Julie was an Business Intelligence consultant and user group leader in Australia and Southern California before joining Microsoft as technical evangelist.  You might recognize her from the executive demonstration during the opening keynote at PASS Summit last year.

Olivier Matrat, Principal Program Manager (that’s Microsoft job title code for “in charge of a lot of important stuff”), talks about how they are hard at work integrating several products in the “Power *” suite.  This is the first time I’d heard of all the “Power…” prefixed product names unofficially referred to as “Power Star”, but it makes perfect sense.  Olivier said that we can expect to see tighter integration between tools like Power BI, Flow and Power Apps with more embeddable features for developers and solution integrators.

Aaron Nelson, Data Platform MVP and hard-core PowerShell enthusiast, spoke about some new capabilities he presented in his session about PowerShell for Business Intelligence. The new REST API will let Power BI and report server admins orchestrate server migrations and task automation with PowerShell CmdLets.  He seized the opportunity to promote the PASS PowerShell virtual group that he helps manage, at SQLPS.IO.  I’ve promised Aaron a follow-up post to demonstrate how the REST API works with PowerShell and the new MSBuild integration, so please watch my blog for that in the next few days.

I chatted with Seth Bauer, BI consultant and Data Platform MVP, on the escalator in the Washington State Convention Center between sessions.  Seth has been on the front lines of the Power BI advisors community since the product launched.  He cites Q&A Natural Language and Explain the Difference as examples of the most compelling features.   He participates in PASS Summit for professional networking and to stay current with BI technologies.

Robert Bruckner is a Senior Architect on the Power BI team and long-time developer lead for Reporting Services.  He told me that there are many exciting capabilities on the horizon for Power BI and other integrated reporting technologies that are still under NDA.  He mentioned a recent announcement that the On-premises gateway will soon support single sign-on, delegation, load balancing and high-availability.  It is truly exciting to see such emphasis on enterprise-scale capabilities for these tools.

The Future of Power Query – Interviews with Chris Webb at PASS Summit 2017

In a series of interviews during PASS Summit 2017 in Seattle, the week of October 30 through November 3, I caught up with Chris Webb, who is an international BI community thought-leader and product team advisor for Microsoft.  We chatted about his love affair with Power Query, lessons learned from many years of expert Business Intelligence project work and his thoughts about the future of Power Query and other Microsoft tools.  Chris had just finished an all day preconference presentation the day before our interview and his experience was fresh in-mind.  He has a long history of deep expertise with SQL Server, Analysis Services multidimensional and tabular technologies, MDX and DAX.  His focus lately has been teaching, writing and using Power Query in practice.

He was so interesting and insightful that I honestly couldn’t edit anything out of the final recording, so I split it into three parts.

Chris Webb Interview Part 1 of 3

In the first installment, Chris talks about how he got started with Power Query and why he thought it was worth his investment of time and energy.  He hadn’t planned to bet his career on Power Query.  He said “this tool was cool!  I liked playing with it…”  He carried on playing with Power Query until he fell in love with the product and its awesome ability to transform data in ways that weren’t possible before with the same easy and elegance.  Maybe the long-term success and pervasive integration of Power Query into more products will be bolstered by the enthusiasm of Chris and others in the community who just love to use it.

Chris Webb Interview Part 2 of 3

We began with the question: “Do you ever foresee Power Query being used as an enterprise ETL tool in lieu of something like SSIS?”  Chris shares his thoughts about Power Query as a self-service, desktop data transformation tool and its juxtaposition with other with older, more complicated data tools.  He says it is “a universal query generator”, capable of performing “query folding” (translating and pushing  queries back to the data source engine for each data provider).  We discussed how Microsoft is committed to supporting and enhancing Power Query, not only for desktop analysis but to perform tasks partitioning in Analysis Services tabular, with many other possible scenarios.  His passion for this tool is so apparent in our discussion.

Chris Webb Interview Part 3 of 3

How can you get started with Power Query and where is the best place to go for expert advice?  Chris wrote one of the best books on the topic that, in my opinion, is very relevant today.  I gave him the bait and he wouldn’t take it when I asked: “can you recommend any good books on Power Query?”  In his usual, humble, fashion he tells me how difficult it is to write a good book about a product that changes so much; and goes on to recommend several online learning resources.

We conclude with Chris’ advice about how to get started and where to go for best practices, to develop expertise and advanced knowledge.

You can follow Chris Webb’s frequent posts on his blog, at: blog.crossjoin.co.uk
Thank you, Chris, for your time and willingness to share your thoughts and expertise.

Resources for Power Query Chris mentioned:

Microsoft Uses Predictive Analytics to Deliver Data Platform Product Documentation! Interview with Carl Rabeler

Carl Rabeler is a Content Strategist on the Microsoft Azure SQL team.  In this role, he is  much more that just a document writer. Carl works closely with the Customer Advisory Team (CAT), the MVP community and TIGER teams who support the most complex enterprise customer solutions.  In this 11 minute interview during the 2017 PASS Summit, Carl shares the teams’ strategies for analyzing content searches, reader and viewership analytics to move ahead of the curve and predict the kinds of learning and support documentation Microsoft users will find most valuable in the future.

He told me how product instrumentation, site search metadata and direct customer feedback allow them to target content that users need now, rather then waiting for comments and revisions.  The primary product documentation store is now hosted in Docs.Microsoft.com, which is an open Git repository where users can contribute and edit existing docs, which are then curated by the Microsoft product teams.  Between timely and aggressive technical content authoring, open source collaborative curation and advanced analytics performed on content search and usage, Microsoft is much better equipped to serve the masses and meet the needs of users who have very specific product usage requirements.

Carl and I have worked together over the past 10-12 years through several Microsoft product team interactions, MVP Summit sessions, and we worked on consulting client projects for a number of years through SolidQ – aka Solid Quality Mentors.  He was the guy we flew out to work on the big architecture configuration solutions when we needed the three-headed dog from Hell to play nicely with SharePoint and every conceivable service connecting through it.

Next time you go searching for how to encrypt a server connection, know that Carl and his team of content curators are looking for the best way to get you the answers you need in the most effective way possible.

Interview with Pinal Dave at PASS Summit 2017

While attending PASS Summit 2017 in Seattle, I had a chance to catch-up of several friends and industry experts, and I will be sharing these interviews in a series of posts.  On the 1st of November, Pinal Dave completed his 4200th post on SqlAuthority.com – a landmark by blogging once per day for eleven years.  Pinal shares this success story and humbly discusses his secrets about being the world-renound authority that we all know, along with some advice about tuning SQL Server.

Thank you, Pinal, for your time and all you do for the PASS community.

 

Look out, PASS Summit precons attendees

Just a little bit of last minute work to do… loading 196 USB drives with materials for the session. Looking forward to meeting you if you are attending the Tuesday precon.

It's not a requirement for the session to follow along, but if you would like, you may bring a laptop capable of running SQL Server 2017 and Reporting Services to follow along with demos. Some selected lab materials from my 2, 3 and 4-day classes will be provided.