collect, combine, and transform data using power … query book intro.pdfcollect, combine, and...
TRANSCRIPT
![Page 1: Collect, Combine, and Transform Data Using Power … Query Book Intro.pdfCollect, Combine, and Transform Data Using Power Query in Excel and Power BI Gil Raviv gil.raviv@avanade.com](https://reader030.vdocuments.us/reader030/viewer/2022040406/5ea4624b12eec47e7772b7b5/html5/thumbnails/1.jpg)
Collect, Combine,and TransformData UsingPower Query inExcel and Power BI
Gil Raviv
![Page 2: Collect, Combine, and Transform Data Using Power … Query Book Intro.pdfCollect, Combine, and Transform Data Using Power Query in Excel and Power BI Gil Raviv gil.raviv@avanade.com](https://reader030.vdocuments.us/reader030/viewer/2022040406/5ea4624b12eec47e7772b7b5/html5/thumbnails/2.jpg)
©2017 Avanade Inc. All Rights Reserved. <Highly Confidential> See Avanade’s Data Management Policy
©2018 Avanade Inc. All Rights Reserved.2
About Gil Raviv
• Microsoft MVP
• Director Analytics at Avanade, helps organizations to enable self-service BI
• Power BI Blogger at DataChant.com
• Former PM on Microsoft Excel Product team
![Page 3: Collect, Combine, and Transform Data Using Power … Query Book Intro.pdfCollect, Combine, and Transform Data Using Power Query in Excel and Power BI Gil Raviv gil.raviv@avanade.com](https://reader030.vdocuments.us/reader030/viewer/2022040406/5ea4624b12eec47e7772b7b5/html5/thumbnails/3.jpg)
©2017 Avanade Inc. All Rights Reserved. <Highly Confidential> See Avanade’s Data Management Policy
©2018 Avanade Inc. All Rights Reserved.
Avanade at a Glance in Analytics
AvanadeData & Analytics
Agile delivery thru variety of delivery models: Onshore, Near-shore, Offshore & Composite
Industry & Technology accelerators to jumpstart & accelerate delivery
43% of our customers are in the Global 500
Analytics for:
Internet of Things
Managed Services for On-Premise, Cloud and Hybrid environments
Analyticsas a Service
Analytics Application Development Factory
AnalyticsInnovationLab
Industry leadingAvanade Modern Analytics PlatformOn Azure
70 Locations
22Countries
550+ Analytics customers across all industries
4000+ Analytics professional WW
1000+ Data Engineers & Scientists100+ Big Data Consultants
Analytics: driving digital innovation and business outcomes
3
![Page 4: Collect, Combine, and Transform Data Using Power … Query Book Intro.pdfCollect, Combine, and Transform Data Using Power Query in Excel and Power BI Gil Raviv gil.raviv@avanade.com](https://reader030.vdocuments.us/reader030/viewer/2022040406/5ea4624b12eec47e7772b7b5/html5/thumbnails/4.jpg)
©2017 Avanade Inc. All Rights Reserved. <Highly Confidential> See Avanade’s Data Management Policy
©2018 Avanade Inc. All Rights Reserved.4
Power Query – The Best Data Acquisition & Preparation Tool
![Page 5: Collect, Combine, and Transform Data Using Power … Query Book Intro.pdfCollect, Combine, and Transform Data Using Power Query in Excel and Power BI Gil Raviv gil.raviv@avanade.com](https://reader030.vdocuments.us/reader030/viewer/2022040406/5ea4624b12eec47e7772b7b5/html5/thumbnails/5.jpg)
©2017 Avanade Inc. All Rights Reserved. <Highly Confidential> See Avanade’s Data Management Policy
Excel 2010 and
2013 Add-In
Excel 2016
Data ribbon
Azure Analysis
Services
Power BI Desktop
Microsoft Flows Power Apps Dataflows
Power BI Gateway
+ Service
Power Query Leading the Data Prep Experiencesin Microsoft Stack
Power Query is Available in these Products
![Page 6: Collect, Combine, and Transform Data Using Power … Query Book Intro.pdfCollect, Combine, and Transform Data Using Power Query in Excel and Power BI Gil Raviv gil.raviv@avanade.com](https://reader030.vdocuments.us/reader030/viewer/2022040406/5ea4624b12eec47e7772b7b5/html5/thumbnails/6.jpg)
©2017 Avanade Inc. All Rights Reserved. <Highly Confidential> See Avanade’s Data Management Policy
©2018 Avanade Inc. All Rights Reserved.6
The Challenges
• Power Query Editor is frequently updated
• UI is going through constant incremental changes
• New connectors
• New transformations
• The new data exploration
• Intellisense in Advanced Editor
• How can I write a book that will still be relevant in the
future?
• After a year?
• After 3 years?
• After 5 years?
• My solution
• Don’t focus on features, focus on capabilities
• Hands-on
• Sequences and Principles
![Page 7: Collect, Combine, and Transform Data Using Power … Query Book Intro.pdfCollect, Combine, and Transform Data Using Power Query in Excel and Power BI Gil Raviv gil.raviv@avanade.com](https://reader030.vdocuments.us/reader030/viewer/2022040406/5ea4624b12eec47e7772b7b5/html5/thumbnails/7.jpg)
©2017 Avanade Inc. All Rights Reserved. <Highly Confidential> See Avanade’s Data Management Policy
©2018 Avanade Inc. All Rights Reserved.7
![Page 8: Collect, Combine, and Transform Data Using Power … Query Book Intro.pdfCollect, Combine, and Transform Data Using Power Query in Excel and Power BI Gil Raviv gil.raviv@avanade.com](https://reader030.vdocuments.us/reader030/viewer/2022040406/5ea4624b12eec47e7772b7b5/html5/thumbnails/8.jpg)
©2017 Avanade Inc. All Rights Reserved. <Highly Confidential> See Avanade’s Data Management Policy
©2018 Avanade Inc. All Rights Reserved.8
Chapter 1: Introduction to Power Query
How it started
“Power Query was initially formed in 2011 as part of Microsoft SQL Azure Labs. It was announced at
PASS Summit in October 2011 under the Microsoft codename “Data Explorer.” Figure 1-1 shows its
initial user interface.”
![Page 9: Collect, Combine, and Transform Data Using Power … Query Book Intro.pdfCollect, Combine, and Transform Data Using Power Query in Excel and Power BI Gil Raviv gil.raviv@avanade.com](https://reader030.vdocuments.us/reader030/viewer/2022040406/5ea4624b12eec47e7772b7b5/html5/thumbnails/9.jpg)
©2017 Avanade Inc. All Rights Reserved. <Highly Confidential> See Avanade’s Data Management Policy
©2018 Avanade Inc. All Rights Reserved.9
Chapter 2: Basic Data Preparation Challenges
0000Messy1111 Cleaned
![Page 10: Collect, Combine, and Transform Data Using Power … Query Book Intro.pdfCollect, Combine, and Transform Data Using Power Query in Excel and Power BI Gil Raviv gil.raviv@avanade.com](https://reader030.vdocuments.us/reader030/viewer/2022040406/5ea4624b12eec47e7772b7b5/html5/thumbnails/10.jpg)
©2017 Avanade Inc. All Rights Reserved. <Highly Confidential> See Avanade’s Data Management Policy
©2018 Avanade Inc. All Rights Reserved.10
Chapter 3: Combining Data from Multiple Sources
Combined
Siloed
Siloed
Siloed
![Page 11: Collect, Combine, and Transform Data Using Power … Query Book Intro.pdfCollect, Combine, and Transform Data Using Power Query in Excel and Power BI Gil Raviv gil.raviv@avanade.com](https://reader030.vdocuments.us/reader030/viewer/2022040406/5ea4624b12eec47e7772b7b5/html5/thumbnails/11.jpg)
©2017 Avanade Inc. All Rights Reserved. <Highly Confidential> See Avanade’s Data Management Policy
©2018 Avanade Inc. All Rights Reserved.11
Chapter 4: Combining Mismatched Tables
Mismatched
Combinedmis_matched
MISMATCHED
![Page 12: Collect, Combine, and Transform Data Using Power … Query Book Intro.pdfCollect, Combine, and Transform Data Using Power Query in Excel and Power BI Gil Raviv gil.raviv@avanade.com](https://reader030.vdocuments.us/reader030/viewer/2022040406/5ea4624b12eec47e7772b7b5/html5/thumbnails/12.jpg)
©2017 Avanade Inc. All Rights Reserved. <Highly Confidential> See Avanade’s Data Management Policy
©2018 Avanade Inc. All Rights Reserved.12
Chapter 5: Preserve Context
Combined
+
Preserved
contextless
contextless
contextless
Co
nte
xtC
on
text
Co
nte
xt
![Page 13: Collect, Combine, and Transform Data Using Power … Query Book Intro.pdfCollect, Combine, and Transform Data Using Power Query in Excel and Power BI Gil Raviv gil.raviv@avanade.com](https://reader030.vdocuments.us/reader030/viewer/2022040406/5ea4624b12eec47e7772b7b5/html5/thumbnails/13.jpg)
©2017 Avanade Inc. All Rights Reserved. <Highly Confidential> See Avanade’s Data Management Policy
©2018 Avanade Inc. All Rights Reserved.13
Chapter 6: Unpivoting Tables
Well-
Structured
Badly
Structured
![Page 14: Collect, Combine, and Transform Data Using Power … Query Book Intro.pdfCollect, Combine, and Transform Data Using Power Query in Excel and Power BI Gil Raviv gil.raviv@avanade.com](https://reader030.vdocuments.us/reader030/viewer/2022040406/5ea4624b12eec47e7772b7b5/html5/thumbnails/14.jpg)
©2017 Avanade Inc. All Rights Reserved. <Highly Confidential> See Avanade’s Data Management Policy
©2018 Avanade Inc. All Rights Reserved.14
Chapter 7: Advanced Unpivoting and Pivoting Tables
Well-
Structured
Badly
Structured
![Page 15: Collect, Combine, and Transform Data Using Power … Query Book Intro.pdfCollect, Combine, and Transform Data Using Power Query in Excel and Power BI Gil Raviv gil.raviv@avanade.com](https://reader030.vdocuments.us/reader030/viewer/2022040406/5ea4624b12eec47e7772b7b5/html5/thumbnails/15.jpg)
©2017 Avanade Inc. All Rights Reserved. <Highly Confidential> See Avanade’s Data Management Policy
©2018 Avanade Inc. All Rights Reserved.15
Chapter 8: Addressing Collaboration Challenges
![Page 16: Collect, Combine, and Transform Data Using Power … Query Book Intro.pdfCollect, Combine, and Transform Data Using Power Query in Excel and Power BI Gil Raviv gil.raviv@avanade.com](https://reader030.vdocuments.us/reader030/viewer/2022040406/5ea4624b12eec47e7772b7b5/html5/thumbnails/16.jpg)
©2017 Avanade Inc. All Rights Reserved. <Highly Confidential> See Avanade’s Data Management Policy
©2018 Avanade Inc. All Rights Reserved.16
Chapter 9: Introduction to the Power Query M Formula Language
![Page 17: Collect, Combine, and Transform Data Using Power … Query Book Intro.pdfCollect, Combine, and Transform Data Using Power Query in Excel and Power BI Gil Raviv gil.raviv@avanade.com](https://reader030.vdocuments.us/reader030/viewer/2022040406/5ea4624b12eec47e7772b7b5/html5/thumbnails/17.jpg)
©2017 Avanade Inc. All Rights Reserved. <Highly Confidential> See Avanade’s Data Management Policy
©2018 Avanade Inc. All Rights Reserved.17
Chapter 10: From Pitfalls to Robust Queries
![Page 18: Collect, Combine, and Transform Data Using Power … Query Book Intro.pdfCollect, Combine, and Transform Data Using Power Query in Excel and Power BI Gil Raviv gil.raviv@avanade.com](https://reader030.vdocuments.us/reader030/viewer/2022040406/5ea4624b12eec47e7772b7b5/html5/thumbnails/18.jpg)
©2017 Avanade Inc. All Rights Reserved. <Highly Confidential> See Avanade’s Data Management Policy
©2018 Avanade Inc. All Rights Reserved.18
Chapter 11: Basic Text Analytics
![Page 19: Collect, Combine, and Transform Data Using Power … Query Book Intro.pdfCollect, Combine, and Transform Data Using Power Query in Excel and Power BI Gil Raviv gil.raviv@avanade.com](https://reader030.vdocuments.us/reader030/viewer/2022040406/5ea4624b12eec47e7772b7b5/html5/thumbnails/19.jpg)
©2017 Avanade Inc. All Rights Reserved. <Highly Confidential> See Avanade’s Data Management Policy
©2018 Avanade Inc. All Rights Reserved.19
Chapter 12: Advanced Text Analytics: Extracting Meaning
![Page 20: Collect, Combine, and Transform Data Using Power … Query Book Intro.pdfCollect, Combine, and Transform Data Using Power Query in Excel and Power BI Gil Raviv gil.raviv@avanade.com](https://reader030.vdocuments.us/reader030/viewer/2022040406/5ea4624b12eec47e7772b7b5/html5/thumbnails/20.jpg)
©2017 Avanade Inc. All Rights Reserved. <Highly Confidential> See Avanade’s Data Management Policy
©2018 Avanade Inc. All Rights Reserved.20
Chapter 13: Social Network Analytics
![Page 21: Collect, Combine, and Transform Data Using Power … Query Book Intro.pdfCollect, Combine, and Transform Data Using Power Query in Excel and Power BI Gil Raviv gil.raviv@avanade.com](https://reader030.vdocuments.us/reader030/viewer/2022040406/5ea4624b12eec47e7772b7b5/html5/thumbnails/21.jpg)
©2017 Avanade Inc. All Rights Reserved. <Highly Confidential> See Avanade’s Data Management Policy
©2018 Avanade Inc. All Rights Reserved.21
Chapter 14: Final Project – Combining It All Together
![Page 22: Collect, Combine, and Transform Data Using Power … Query Book Intro.pdfCollect, Combine, and Transform Data Using Power Query in Excel and Power BI Gil Raviv gil.raviv@avanade.com](https://reader030.vdocuments.us/reader030/viewer/2022040406/5ea4624b12eec47e7772b7b5/html5/thumbnails/22.jpg)
©2017 Avanade Inc. All Rights Reserved. <Highly Confidential> See Avanade’s Data Management Policy
©2018 Avanade Inc. All Rights Reserved.22
• Q&A
• Bonus Exercises
• Cool Demos
![Page 23: Collect, Combine, and Transform Data Using Power … Query Book Intro.pdfCollect, Combine, and Transform Data Using Power Query in Excel and Power BI Gil Raviv gil.raviv@avanade.com](https://reader030.vdocuments.us/reader030/viewer/2022040406/5ea4624b12eec47e7772b7b5/html5/thumbnails/23.jpg)
©2017 Avanade Inc. All Rights Reserved. <Highly Confidential> See Avanade’s Data Management Policy
23
![Page 24: Collect, Combine, and Transform Data Using Power … Query Book Intro.pdfCollect, Combine, and Transform Data Using Power Query in Excel and Power BI Gil Raviv gil.raviv@avanade.com](https://reader030.vdocuments.us/reader030/viewer/2022040406/5ea4624b12eec47e7772b7b5/html5/thumbnails/24.jpg)
©2017 Avanade Inc. All Rights Reserved.©2017 Avanade Inc. All Rights Reserved.
Thank You