PowerBI商业数据分析-第二章

PowerBI商业数据分析第二章xxxPowerQuery数据准备2.1认识PowerQuery目录CONTEXT2.2数据导入2.3数据清洗2.4PQ应用-二维表转一维表2.5认识M语言2.6M语言应用-文本移除和提取2.7分列技巧2.8批量汇总技巧PowerQuery是微软的数据连接和数据准备技术，使业务用户能够无缝访问存储在数百个数据源中的数据，并通过易于使用、引人入胜且无代码的用户体验来对其进行调整以适应他们的需求。2.1认识PowerQuery如果还没有任何数据，在PowerBIDesktop中，点击获取数据，选择相应的数据格式导入后，就可以进入PowerQuery编辑器；如果已经导入数据，想再次进入查看或者编辑，可以点击上图中的“转换数据”进入。2.1认识PowerQueryPowerQuery入口2.1认识PowerQueryPowerQuery界面2.2数据导入PowerQuery的强大数据处理功能，几乎可以从任何来源、任何结构、任何形式上获取数据，具体可以连接的数据类型;不仅支持微软自己的数据格式，比如Excel、SQLServer、Access等;还支持SAP、Oracle、MySQL、DB2等几乎能见到的所有类型的数据格式。2.2数据导入从网页抓取数据示例放映幻灯片查看操作步骤2.3数据清洗PowerQuery最常用的16个界面功能1.提升标题2.更改数据类型方式一方式二3.删除错误/空值右键删除错误筛选-去掉null勾选4.删除重复项右键删除重复项5.填充Excel合并单元格，导入后变为null5.填充6.合并列7.拆分列8.分组9.提取提取前两个字符10.转置10.转置-将标题作为第一行10.转置11.行列操作12.逆透视列13.透视列13.透视列透视“属性”列14.添加列14.1添加重复列14.2添加索引列14.3添加条件列14.3添加条件列14.4添加自定义列14.4添加自定义列14.5添加示例列14.5添加示例列15.追加查询15.追加查询16.合并查询16.合并查询16.合并查询2.4PowerQuery应用示例二维表转一维表二维表什么是一维表、二维表？一维表一维表的每一列就是一个维度，列名就是该列值的共同属性一维表的每一行就是一条独立的记录为什么要转换为一维表？二维表更符合我们日常的阅读习惯，信息更浓缩，适合展示结果，但作为源数据进行数据分析时，一维表更适合。一维表的每一列是一个独立的维度，列是数据分析的基础，比如制作图表时直接把字段拖入到某个属性框中，以及后面将会学习的利用列与其他表建立关系；编写DAX时直接使用列名等。如何转换为一维表？主要是利用PowerQuery的逆透视功能根据不同的二维表结构，灵活使用填充、转置、合并列、分列等操作四种常见的二维表结构：行列标题均为单层的二维表行标题有多层的二维表列标题有多层的二维表行列标题均有多层的二维表二维表转一维表|1.行列标题均为单层的二维表思路：1、选中标题列；2、逆透视其他列。二维表转一维表|1.行列标题均为单层的二维表放映幻灯片查看操作步骤二维表转一维表|2.行标题有多层的二维表思路：1、向下填充行标题列；2、选中多个行标题列；3、逆透视其他列。二维表转一维表|2.行标题有多层的二维表放映幻灯片查看操作步骤二维表转一维表|3.列标题有多层的二维表思路：1、转置表；2、向下填充；3，将第一行用作标题；

4，选中标题列；5、逆透视其他列。二维表转一维表|3.列标题有多层的二维表放映幻灯片查看操作步骤二维表转一维表|4.行、列标题均有多层的二维表思路：1、向下填充；2，合并列；3、转置表；4、向下填充；5、将第一行用作标题；

6，选中标题列；7、逆透视其他列；8、拆分列。实际上是第2种和第3种二维表的结合二维表转一维表|4.行、列标题均有多层的二维表放映幻灯片查看操作步骤2.5认识M语言PowerQuery的秘密都在这里2.5认识M语言2.5认识M语言为什么要学习M语言？

界面操作配合M函数，更加灵活、简洁高效部分复杂操作必须借助M函数2.5认识M语言M函数基本规范

M函数严格区分大小写，每一个字母必须按函数规范书写，第一个字母都是大写表被称为Table，每行的内容是一个Record，每列的内容是一个List行标用大括号{}列标用中括号[]示例：取表中第一行的内容：=表{0}提取表中自定义列的内容：=表[自定义]取表种第一行自定义列的内容：=表{0}[自定义]2.5认识M语言常用的M函数

聚合函数：求和：List.Sum()求最小值：List.Min()求最大值：List.Max()求平均值：List.Average()文本函数：求文本长度：Text.Length()去文本空格：Text.Trim()取前n个字符：Text.Start()取后n个字符：Text.End()移除文本：Text.Remove()提取文本：Text.Select()提取数据函数：从Excel表中提取数据：Excel.Workbook()从Csv/Txt中提取数据：Csv.Document()条件函数：Ifthenelse（相当于Excel中的IF函数)2.5认识M语言从哪里查找和学习M语言官网学习：/zh-cn/powerquery-m/power-query-m-reference

2.6M语言应用示例文本处理技巧Text.Remove和Text.Select2.6M语言应用示例Text.Remove原始数据添加自定义列：Text.Remove([客户],{"A".."Z"})移除英文字符、分离出中文语法：

Text.Remove(被处理的文本，移除的字符）2.6M语言应用示例Text.Remove原始数据语法：

Text.Remove(被处理的文本，移除的字符）Text.Remove([客户],{“一”..“龟”})，移除中文字符2.6M语言应用示例Text.Remove原始数据语法：

Text.Remove(被处理的文本，移除的字符）Text.Remove([客户{“A”..“z”,“一”..“龟”}），移除英文和中文字符2.6M语言应用示例Text.Select原始数据语法：

Text.Select(被处理的文本，选择的字符）Text.Select([客户],{“0”..“9”})）提取数字，与Text.Remove效果一样2.6M语言应用示例Text.Remove和Text.Select总结根据实际情况选择使用：Text.Remove/Text.Select代码更加简洁移除字符类型M语言移除数字Text.Remove([文本数据],{"0".."9"})移除大写英文字符Text.Remove([文本数据],{"A".."Z"})移除小写英文字符Text.Remove([文本数据],{"a".."z"})移除全部英文字符Text.Remove([文本数据],{"A".."z"})移除全部中文字符Text.Remove([文本数据],{"一".."龟"})提取字符类型M语言提取数字Text.Select([文本数据],{"0".."9"})提取大写英文字符Text.Select([文本数据],{"A".."Z"})提取小写英文字符Text.Select([文本数据],{"a".."z"})提取全部英文字符Text.Select([文本数据],{"A".."z"})提取全部中文字符Text.Select([文本数据],{"一".."龟"})2.7PowerQuery应用数据分列技巧分列|按分割符分列|从数字到非数字分列|从小写到大写分列|拆分到行分列|多种分割符分列|多种分割符-利用M语言灵活分列直接添加步骤，输入下述代码即可。主要是使用Splitter.SplitTextByAnyDelimiter函数，第一个参数写入所有分隔符的列表{"，","；","-","+","。"}2.8PowerQuery应用批量汇总汇总多个sheet2.8PowerQuery应用：批量汇总一个Excel表的多个sheet思路：

任意导入一个sheet；

删除“源”之后的步骤；展开数据。汇总多个sheet2.8PowerQuery应用：批量汇总一个Excel表的多个sheet思路：

任意导入一个sheet；

删除“源”之后的步骤；展开数据。放映幻灯片查看操作步骤汇总多个工作簿2.8PowerQuery应用：批量汇总多个工作簿思路：

选择从文件夹汇总汇总多个sheet思路：

任意导入一个sheet；

删除“源”之后的步骤；展开数据。放映幻灯片查看操作步骤2.8PowerQuery应用：批量汇总多个工作簿汇总多个sheet思路：

任意导入一个sheet；

删除“源”之后的步骤；展开数据。2.8PowerQuery应用：批量汇总关于批量汇总的技巧：1、无论是汇总多个sheet还是多个工作簿，都可以使用文件夹的方式；2、汇总以后，文件夹中数据发生变动，或者放入新的文件，PowerBI点击刷新即可；3、虽然可以点击合并按钮自动合并Excel，但建议使用Excel.Workbook函数来解析；4、不仅可以批量汇总