Power Query
文章平均质量分 55
taller_2000
不积跬步,无以至千里;不积小流,无以成江海
展开
-
BI神器Power Query(27)-- 使用PQ实现表格多列转换(3/3)
原始表格包含多列属性数据,现在需要将不同属性分列展示在不同的行中,att1、att3、att5为一组,att2、att3、att6为另一组,数据如下所示。原创 2023-10-02 03:22:11 · 613 阅读 · 0 评论 -
BI神器Power Query(26)-- 使用PQ实现表格多列转换(2/3)
原始表格包含多列属性数据,现在需要将不同属性分列展示在不同的行中,att1、att3、att5为一组,att2、att3、att6为另一组,数据如下所示。原创 2023-10-02 03:20:58 · 750 阅读 · 0 评论 -
BI神器Power Query(25)-- 使用PQ实现表格多列转换(1/3)
原始表格包含多列属性数据,现在需要将不同属性分列展示在不同的行中,att1、att3、att5为一组,att2、att3、att6为另一组,数据如下所示。原创 2023-10-02 03:15:56 · 730 阅读 · 0 评论 -
用户权限数据转换为用户组列表(2/3) - Power Query
使用Power Query实现数据转换,如下是AD用户的列表,每个用户拥有该应用程序的只读或读写权限,现在需要创建新的AD用户组,并根据用户当前的权限,将这些用户添加到相应的组中。原创 2023-09-10 03:21:51 · 274 阅读 · 0 评论 -
BI神器Power Query(24)-- PowerQuery动态加载M公式
Power Query 是Excel中的强大数据处理与转换工具,如果需要“动态”处理数据,大家第一时间想到的是可以使用VBA,利用代码创建M公式,进而创建PQ查询,但是复杂的M公式可能有很多行, 使用VBA处理起来并不是很方便,并且用户打开工作簿文件时必须启用宏功能,代码才能发挥作用。其实无需借助VBA,PQ内置功能就可以实现动态加载M公式,接下来使用一个简单的PQ示例进行演示。这个技巧通常用于动态执行Power Query脚本或表达式,允许在运行时构建和执行Power Query代码,以满足特定的需求原创 2023-09-02 06:51:52 · 387 阅读 · 0 评论 -
BI神器Power Query(23)-- 同一列内多重替换(3)
示例需求:省份清单中“自治区”名称过长,在做报表展示时效果不理想,希望将其替换为简称,例如:内蒙古自治区替换为“内蒙古”。这个直接使用Excel中的替换也可以实现,毕竟只有5个自治区需要替换,接下来看看Power Query中如何实现。单击选中数据表中任意单元格,例如A6,依次单击【数据】>【自表格/区域】,打开Power Query编辑器。第一个方法参见:BI神器Power Query(21)-- 同一列内多重替换(1)M代码如下:let 源 = Excel.CurrentWork原创 2020-12-19 14:08:19 · 3266 阅读 · 2 评论 -
BI神器Power Query(22)-- 同一列内多重替换(2)
示例需求:省份清单中“自治区”名称过长,在做报表展示时效果不理想,希望将其替换为简称,例如:内蒙古自治区替换为“内蒙古”。这个直接使用Excel中的替换也可以实现,毕竟只有5个自治区需要替换,接下来看看Power Query中如何实现。原创 2020-11-28 12:08:50 · 1762 阅读 · 0 评论 -
BI神器Power Query(21)-- 同一列内多重替换(1)
示例需求:省份清单中“自治区”名称过长,在做报表展示时效果不理想,希望将其替换为简称,例如:内蒙古自治区替换为“内蒙古”。这个直接使用Excel中的替换也可以实现,毕竟只有5个自治区需要替换,接下来看看Power Query中如何实现。原创 2020-11-28 08:00:57 · 2276 阅读 · 0 评论 -
BI神器Power Query(20)-- PQ自适应目录
使用Power Query可以轻松导入文本文件、XML、CSV文件等多种数据文件,但是如果保存文件的目录发生变化,刷新查询时会出现类似于下图所示的`DataSource.Error`错误提示。此时需要打开【Power Query编辑器】,在【高级编辑器】对话框中更新文件目录和名称,但是PQ公式语法要求很严格,稍有不慎可能就会出现其他错误。如果数据文件和Excel文件保存在同一目录中,使用VBA代码在实现自动更新,下述代码写入Excel文件的ThisWorkbook模块中。原创 2020-06-19 01:01:35 · 1355 阅读 · 0 评论 -
BI神器Power Query(19)-- PQ提取商品信息
现有商品标题如下图所示,其中包含商品名称和规格信息,为了便于使用,需要将其拆分为三个字段,使用Excel公式虽然也可以拆分,但是公式比较繁杂,PQ可以更快捷地完成拆分。原创 2020-06-15 00:28:35 · 1153 阅读 · 0 评论 -
PowerQuery公式巧备份
Power Query是个强大的功能,PQ的公式显得有些复杂,为了避免文件可能出现的意外损坏,经常备份PQ的公式是个不错的方法,本文介绍几种备份方法。原创 2020-05-22 01:23:58 · 1737 阅读 · 0 评论 -
BI神器Power Query(18)-- PQ制作时间维度表(7)
在上一篇博客中,通过Date.ToText和Date.DayOfWeekName函数创建了新的数据列,如下图所示。有时需要使用年月和年月日,有两种实现方法。利用前面已经提取的年、月、日,转换为text,然后再进行字符串组合使用Date.ToText函数转换日期格式第2种方法更直接和简洁,下文将采用这个方法。依次单击【添加列】=>【自定义列】,在【添加自定义列】对话框中,指定【...原创 2019-12-08 15:02:17 · 2048 阅读 · 0 评论 -
BI神器Power Query(17)-- PQ制作时间维度表(6)
在上一篇博客中,通过Text.From函数创建了“年份”、“季度”和“月份”列数据列,如下图所示。大家都知道在Excel中日期格式有很多种,可以根据用户的使用习惯选择不同的样式,在PQ中创建新列时也可以通过类似的格式化字符串来获得不同的日期格式。依次单击【添加列】=>【自定义列】,在【添加自定义列】对话框中,指定【新列名】为“月份2”,输入公式=Date.ToText([Date],"...原创 2019-12-08 13:08:13 · 1692 阅读 · 0 评论 -
BI神器Power Query(16)-- PQ制作时间维度表(5)
在上一篇博客中,使用【添加列】选项卡中【日期】按钮的相关添加新列,如下图所示。虽然这些内置功能已经添加了年月日等列,但是实际使用中可能会用到多种不同的形式,例如2019年四季度会记作:Y2019Q4,接下来看看如何在PQ中获取这些不同的日期格式。依次单击【添加列】=>【自定义列】,在【添加自定义列】对话框中,指定【新列名】为“年份”,输入公式为"Y" & Text.From([...原创 2019-12-01 02:38:46 · 888 阅读 · 0 评论 -
BI神器Power Query(15)-- PQ制作时间维度表(4)
在上一篇博客中,使用【重复列】添加新列,如下图所示。接下来讲解一下【添加列】选项卡中【日期】按钮的使用方法。如果选中的列不是日期类型,那么多数功能将被禁用,如下图所示。选中【Date】列,依次单击【日期】下拉按钮=>【年】=>【年】,提取日期中的“年”插入新列,如下图所示。使用类似的方法插入月和日。依次单击【日期】下拉按钮=>【月份】=>【月份】依次单...原创 2019-12-01 02:12:58 · 1650 阅读 · 0 评论 -
BI神器Power Query(14)-- PQ制作时间维度表(3)
Power Query支持数据模型,可以将多种数据添加到数据模型,然后建立管理关系并进行后续分析。有些时候会使用和数据仓库星形数据模型的方式进行关联,这里常用的两类表为维度表和事实表。本文讲解如何复制列并指定类型。原创 2019-11-25 23:56:34 · 1057 阅读 · 0 评论 -
BI神器Power Query(13)-- PQ制作时间维度表(2)
Power Query支持数据模型,可以将多种数据添加到数据模型,然后建立管理关系并进行后续分析。有些时候会使用和数据仓库星形数据模型的方式进行关联,这里常用的两类表为维度表和事实表。本文讲解如何添加自定义列并指定类型。原创 2019-11-25 14:10:54 · 2087 阅读 · 0 评论 -
BI神器Power Query(12)-- PQ创建时间维度表(1)
Power Query支持数据模型,可以将多种数据添加到数据模型,然后建立管理关系并进行后续分析。有些时候会使用和数据仓库星形数据模型的方式进行关联,这里常用的两类表为维度表和事实表。本文讲解如何创建时间序列。原创 2019-11-24 11:55:24 · 6237 阅读 · 0 评论 -
BI神器Power Query(11)-- PQ M函数快速查询
Power Query M 函数超过700个,基本上不太可能完全记住全部的函数的使用方法,大家可以通过在线帮助文档查询函数的含义和使用方法。Power Query M 函数引用 https://docs.microsoft.com/zh-cn/powerquery-m/power-query-m-function-reference其实在Power Query编辑器中就可以快速查询函数使用方法...原创 2019-11-08 14:34:15 · 6327 阅读 · 0 评论 -
BI神器Power Query(10)-- PQ从文件夹导入数据
如果需要导入多个数据文件,可以使用“从文件夹”导入功能,一次性导入多个结构相同的数据文件,而无需逐个文件导入在合并。示例数据文件如所示。依次单击【数据】选项卡>【新建查询】>【从文件】>【从文件夹】。在【文件夹】对话框中选择数据文件所在目录,单击【确定】按钮。对话框中列出该目录下全部文件,单击【编辑】按钮,打开PQ编辑器。单击【Extension】列下拉按钮,取...原创 2019-10-13 14:18:48 · 13376 阅读 · 7 评论 -
BI神器Power Query(9)-- PQ从XML文件导入数据
XML(可扩展标记语言,其全称为Extensible Markup Language),是一种标记语言。XML是由SGML(The Standard Generalized Markup Language)发展而来的,1998年2月W3C(万维网联盟)发布XML 1.0规范,自此之后XML被广泛地应用于跨平台数据交换。XML文件的结构与HTML文件很相似,二者的用途是有明显区别的,HTML用来展示...原创 2019-10-13 12:01:53 · 1628 阅读 · 0 评论 -
BI神器Power Query(2)-- PQ数据源
数据源是ETL流程的起始点,也是整个流程的基础,Power Query之所以功能强大,一个重要的特性就是PQ支持多种数据源。这么多数据源是否有些眼花缭乱的赶脚,其实对于多数用户来说,很少能够用到Azure和在线服务,商用数据库可能会略有接触,最常用的功能还是“从文件”和“从其他源”中的自网站、从ODBC和空白查询。原创 2019-07-01 05:41:59 · 2087 阅读 · 0 评论 -
BI神器Power Query(3)-- PQ从表格导入数据
这篇博客讲讲如何使用【从表格】按钮导入数据 ,这个按钮在【获取和转换】组的C位,很明显Office的产品经理认为与其他数据源相比,用户会更多的使用这个功能。其实这个按钮的名字有些容易混淆,Excel通俗的名字是电子表格,在【插入】选项卡中有【表格】按钮,工作表的数据的组织形式也是“表格”,这个“从表格”到底是啥意思呢?工作表中有如下图的数据,它们是“最朴素”的表格。选中有数据的任意单元...原创 2019-07-01 05:43:42 · 5108 阅读 · 0 评论 -
BI神器Power Query(4)-- PQ导入动态名称定义的表格
使用定义名称的方式指定数据区域,数据增减后,这个名称不能自动更新引用范围,如果需要实现类似于表格的自适应扩展,那么就需要使用动态名称。使用Offset和Counta函数如下图所示可以定义动态名称,这个动态名称可以自适应引用A1单元格所在的连续数据区域。动态名称虽然实现了自适应调整引用区域,但是这样定义的名称,无法像普通名称一样使用【数据】选项卡中的【从表格】功能导入PQ中。原创 2019-09-13 07:31:53 · 2768 阅读 · 0 评论 -
BI神器Power Query(5)-- PQ从工作簿导入数据(1/2)
对于Power BI的用户多数都是Excel重度用户,因此在PQ中导入Excel工作薄文件是多数用户都会遇到的场景,本博文讲解从工作簿导入数据时的几个典型应用方式。示例工作簿文件文件中有如下4个工作表:工作表1:动态名称,动态名称:DynamicTab工作表2:单表,表格名称:Tab_2019工作表3:多个表,表格名称:Tab_2019H1,Tab_2019H2工作表4:非表格多区...原创 2019-09-23 08:53:32 · 888 阅读 · 0 评论 -
BI神器Power Query(6)-- PQ从工作簿导入数据(2/2)
对于Power BI的用户多数都是Excel重度用户,因此在PQ中导入Excel工作薄文件是多数用户都会遇到的场景,本博文讲解从工作簿导入数据时的几个典型应用方式。下面讨论导入示例工作簿文件中的如下2个工作表:工作表3:多个表,表格名称:Tab_2019H1,Tab_2019H2工作表4:非表格多区域,无表格,无动态名称,两个非连续数据区域依次单击【数据】选项卡>>【新建查...原创 2019-09-23 11:54:03 · 1486 阅读 · 0 评论 -
BI神器Power Query(7)-- PQ从文本文件导入数据(1/2)
文本文件是经常使用的数据文件格式,PQ可以方便的导入文本文件中的数据,然后在Excel可以进行后续加工和处理。依次单击【数据】选项卡>【新建查询】>【从文件】,其中的【从CSV】和【从文本】两个命令有些令人混淆。从名称上看,似乎【从CSV】用于导入CSV文件,【从文本】用于导入其他文本文件,其实这两个命令的功能是完全相同的,点击任何一个命令,其【导入数据】对话框都是相同的,所支持...原创 2019-10-08 06:43:44 · 2162 阅读 · 0 评论 -
BI神器Power Query(8)-- PQ从文本文件导入数据(2/2)
文本文件是大家经常使用的数据文件格式,PQ可以方便的导入文本文件中的数据,然后在Excel可以进行后续加工和处理。依次单击【数据】选项卡>【新建查询】>【从文件】,其中的【从CSV】和【从文本】两个命令都可以用于导入文本数据。大家在工作中使用的文本数据文件,并不局限于Excel导出的文件,其格式可能会有与上一篇博客(BI神器Power Query(7)-- PQ从文本文件导入数据...原创 2019-10-09 00:50:11 · 1766 阅读 · 0 评论 -
BI神器Power Query(1)-- 什么是PQ?
办公必备软件Office中就提供了一组功能强大的 "获取和转换" 功能, 可为用户提供快捷的数据收集和整理功能,这个功能就是--Power Query(通常简称为PQ),PQ就是大家唾手可得的方便易用的数据加载和数据清洗工具。开始讲Power Query之前,不得不先讲一下数据处理中的ETL(Extract-Transform-Load的缩写)。ETL指将数据从数据源经过“提取–转换–加载”三个步骤至结果的通用过程。原创 2019-06-02 12:54:23 · 5808 阅读 · 0 评论