Dear members and friends,
The European Union Chamber of Commerce in China, Shenyang Chapter is thrilled to invite you to participate in the Online Training on Excel & Power Bi, the useful tools in data specification on July 14，9:00-16:30 in Chinese.中国欧盟商会沈阳分会荣幸地邀请您参与于2020年7月14日9:00-16:30 举办的在线培训：Excel & Power BI中的数据清洗规范利器。语言：中文。
课程简介 Background
Power Query 是基于Excel BI组件里面的一种数据连接技术，可用于发现、连接、合并和优化数据源以满足分析的需要。它主要是通过图形界面对数据进行整理、清洗、组合等，常规的操作基本不需要使用者使用复杂函数就可以得到想要的结果。数据整理功能强大且好用。特别是瞬间合并成千上万的工作簿数据，游刃有余。
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 workbook data in an instant.
适合人群 Who should attend
l 与数据接触较多、需提升数据分析、使用效率的学员。
Employees who work with data and hope to improve data analysis efficiency.
课程收益 Benefits
l 了解Power Query在Excel数据自动汇总的应用；
l 掌握有效的数据清洗规范；
l 掌握数据获取、转换、整理技能，实现商务数据“自助”和“自动”处理，提升工作效率；
l 大量案例实战操作讲解，应对实际工作问题，学会处理多种非标准化的多表数据处理方法。
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.
功能软件版本要求
l 自带Power Query功能的软件版本：
Power BI Desktop、Office 2016、Office 2019 、Office365：
l 支持Power Query，但需安装Power Query插件：Office 2010、Offie2013
注：Office学生版、个人版、家庭版的Power Query功能不全；
没有此功能的学员，我们统一提供PowerBI Desktop安装包，不会因为软件差异无法完成培训。
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.
课程大纲：
1、Power Query规范数据（一）
l Power Query简介与认识
l 规范化数据六大特点介绍
l 一维数据转二维数据
l 二位数据转一维数据
l 案例实战：
n 数据的拆分合并提取
n 翻转曹操《短歌行》诗句
n 完成文本与数字拆分功能
I. 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
2、Power Query规范数据（二）
l 数据的行列管理及筛选
l 数据格式的转换
l 不规则数据处理
l 案例实战
n 统计每个地区的设备数量
n 快速统计学员各分数段总人数
II. 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
3、Power Query数据拆分、合并、反转、分组
l 数据的拆分合并提取
l 转置数据和反转数据
l 数据分组
l 添加数据列
l 案例实战：
n 用Power Query统计班次
n 汇总每个地区所销售产品总金额
III 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
4、Power Query合并查询
l 单列关键字-合并查询-聚合数据
l 单列关键字-合并查询-扩展数据
l 多列关键字-合并查询-聚合
l 案例实战：
n 六种方式对比预报名表与签到表
IV 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
5、Power Query汇总数据
l 同个工作簿汇总数据
l 汇总CSV数据源文件
l 从文件夹汇总Excel数据M函数Excel. Workbook使用
l 案例实战：
n 快速汇总一个文件夹30个省份的数据文件
5. 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
6、Power Query综合实战
l 复习Power Query数据整理规范清洗功能
l 案例实战：
n 统计每个报订人的总金额
n 快速拆分单元格海量数据
n 转置复杂二维表为一维表
n 完成数据填充并按指定内容分隔行
n 拆分成行并将每行金额拆分成平均值
n 汇总多个文件中指定工作表内容并清洗
n 完成文本文件单选，多选，判断题规范
n 按年月季度透视分析公司管理费用明细
VI 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
Notes:
1. Please register for this activity on the website in advance and be sure to specify the email address. The staff of the Chamber of Commerce will send the meeting link to the registered participants by mail before the meeting starts. Please register before June 16th, 2020.请提前在网站上报名参加本次活动，务必填写邮箱地址，商会工作人员在会议开始前将会议链接通过邮件发送给参会人员。截止时间2020年6月16日。
2. The seminar will be broadcast live through Zoom platform. In order to ensure the smoothness of your participation, it is recommended to download Zoom on your device in advance. 研讨会将通过Zoom平台进行现场直播。 为了保证您参与的流畅性，建议您提前下载Zoom。
3. How to join: you can log on to Zoom website https://www.zoom.com.cn/join, enter the meeting ID and password sent to you by the staff of the Chamber of Commerce, and join the meeting; Or you can download Zoom App on your mobile phone or computer, enter the meeting ID and password in the app, and join the meeting. zoom使用方法：登录Zoom网站https://www.Zoom.com.cn/join，输入商会工作人员发给您的会议ID和密码，加入会议；手机或电脑上下载Zoom App，打开App输入会议ID和密码，加入会议。
