Home

Power Query is a data connection technology based on the Excel BI component, which can be used to discover, connect, merge, and optimize data sources to meet the needs of analysis. It mainly arranges, cleans, and combines the data through the graphic interface. The conventional operation can help you get the desired result without the user using complex functions. Data sorting and specification are both powerful and easy to use. In particular, merging thousands of workbooks data in an instant.


Power Query 是基于Excel BI组件里面的一种数据连接技术，可用于发现、连接、合并和优化数据源以满足分析的需要。它主要是通过图形界面对数据进行整理、清洗、组合等，常规的操作基本不需要使用者使用复杂函数就可以得到想要的结果。数据整理功能强大且好用。特别是瞬间合并成千上万的工作簿数据，游刃有余。

Session benefits

  • Understand the application of Power Query in Excel data automatic summary;
  • Grasp the effective data specifications;
  • Grasp data acquisition, conversion, and sorting skills, realize "self-help" and "automatic" processing of business data;
  • Case studies and practical operation explanation, learn to deal with a variety of non-standardized multi-table data processing methods.


课程收益

  • 了解Power Query在Excel数据自动汇总的应用；
  • 掌握有效的数据清洗规范；
  • 掌握数据获取、转换、整理技能，实现商务数据"自助"和"自动"处理，提升工作效率；
  • 大量案例实战操作讲解，应对实际工作问题，学会处理多种非标准化的多表数据处理方法。

功能软件版本要求

  • 自带Power Query功能的软件版本：

          Power BI Desktop、Office 2016、Office 2019 、Office365：

  • 支持Power Query，但需安装Power Query插件：Office 2010、Offie2013
  • 注：Office学生版、个人版、家庭版的Power Query功能不全；

          没有此功能的学员，我们统一提供PowerBI Desktop安装包，不会因为软件差异无法完成培训。


Required software:

  • Software version with Power Query function: Power BI Desktop、Office 2016、Office 2019 、Office365：
  • Support Power Query, but need to install Power Query plug-ins: Office 2010, offie2013
  • Note: PowerBi Desktop installation package will be provided.

Session Outline

Power Query规范数据（一）

  • Power Query简介与认识
  • 规范化数据六大特点介绍
  • 一维数据转二维数据
  • 二位数据转一维数据
  • 案例实战：

     数据的拆分合并提取

     翻转曹操《短歌行》诗句

     完成文本与数字拆分功能


Power Query规范数据（二）

  • 数据的行列管理及筛选
  • 数据格式的转换
  • 不规则数据处理
  • 案例实战

      统计每个地区的设备数量

     快速统计学员各分数段总人数


Power Query数据拆分、合并、反转、分组

  • 数据的拆分合并提取
  • 转置数据和反转数据
  • 数据分组
  • 添加数据列
  • 案例实战：

     用Power Query统计班次

     汇总每个地区所销售产品总金额


Power Query合并查询

  • 单列关键字-合并查询-聚合数据
  • 单列关键字-合并查询-扩展数据
  • 多列关键字-合并查询-聚合
  • 案例实战：

    六种方式对比预报名表与签到表


Power Query汇总数据

  • 同个工作簿汇总数据
  • 汇总CSV数据源文件
  • 从文件夹汇总Excel数据M函数Excel. Workbook使用
  • 案例实战：

     快速汇总一个文件夹30个省份的数据文件


Power Query综合实战

  • 复习Power Query数据整理规范清洗功能
  • 案例实战：

   统计每个报订人的总金额

   快速拆分单元格海量数据

   转置复杂二维表为一维表

   完成数据填充并按指定内容分隔行

   拆分成行并将每行金额拆分成平均值

   汇总多个文件中指定工作表内容并清洗

   完成文本文件单选，多选，判断题规范

   按年月季度透视分析公司管理费用明细

Data Specification (I)

  • Brief introduction
  • Six features of standardized data
  • Data transformation between 1D and 2D;
  • Practice:

    - Split, merge and extract

    - Text and number splitting function


Data Specification (II)

  • Data management: row and column
  • Data format conversion
  • Practice

   - Count the number of devices in each region

   - Count the total number of students in each scored segment


Data splitting, merging, reversing, grouping in Power Query

  • Data splitting, consolidation, and extraction
  • Data Transposition
  • Data grouping
  • Add data column
  • Practice:

   - Use power query to count

   - Summarize the total amount of products sold in each region



Merge query

  • Single column keyword - consolidated query - aggregate data
  • Single column keyword - consolidated query - extended data
  • Multi-column keyword - consolidated query - aggregation
  • Practice:

   - Six ways to compare the name list with the check-in list


Data summary

  • Summary data of the same workbook
  • Collect CSV data source files
  • Collect Excel data from folder m function excel.
  • Practice:

   - Quickly summarize data files of 30 provinces in one folder


Practice

  • Functions review
  • Case practice:

   - Count the total amount of each subscriber

   - Fast split massive data

   - Transpose complex 2D table to 1D

   - Complete data filling and separate lines according to the specified content

   - Lines split and average the amount of each line

   - Summarize and clean the specified worksheet contents in multiple documents

  - Complete single selection, multiple selection, and criterion of judgment questions for text documents

  - Analyze the details of the company's monthly management expenses

Speakers

  • 赵保恒 Zhao Baoheng

    赵保恒 Zhao Baoheng

    授课经验：
    10 年 IT 工作经验
    8 年以上教学培训经验

    资质证书与荣誉：
    MVP（微软最有价值专家）
    MCT（微软培训认证讲师）

    Experience
    10 years of IT work experience
    More than 8 years of training experience

    Qualification certificate:
    MVP (Microsoft's most valuable expert)
    MCT (Microsoft training and certification instructor)

    view more

Who Should Attend?

与数据接触较多、需提升数据分析、使用效率的学员。



Employees who work with data and hope to improve data analysis efficiency.

Tickets

Please use your own name and email when registering for a training session. Additional fees may apply if you fail to register under your own identity. 


A prepayment is required for all full-day training sessions. 

全天培训课程需要提前付款。


Half-day session training vouchers cannot be used for full-day training programs.

全天会员培训无法使用"半天会员培训免费"券。

Member Ticket
Member Price RMB 990
Member Company Employee Ticket
Standard Price RMB 990
Non-Member Ticket
Standard Price RMB 990

Registration

  • All registrations shall be made online before the session. Payment also shall be made before the session.
  • AmCham Shanghai members are eligible to attend at "Member Rate"; Employees who work at AmCham Shanghai member companies are eligible to attend at "Employee Rate"; Non-members are eligible to attend at "Non-Member Rate".
Cancellation Policy
  • Cancellation: If you need to cancel your registration, please notify Ivette Corominas at (86 21) 6169 3010 or email ivette.corominas@amcham-shanghai.org at least two weeks prior to the session for a full refund. Cancellation made within two weeks before the session will not be refundable.