首页
学习
活动
专区
工具
TVP
发布
精选内容/技术社群/优惠产品,尽在小程序
立即前往

Power Query 真经 - 第 6 章 - 从Excel导入数据

【注意】 本节的示例必须在 Excel 运行,因为 Power BI 没有自己的工作表,所以 Power BI 是不支持这种方式的。...(译者注:表,这个名词在 Excel 中会大量出现,本节作者四个工作表介绍了五种模式,分别是:表(Table):按 Ctrl + T 创建的结构化对象;区域(Range):矩形范围的一片单元格命名区域...单击 “Unformatted” 数据范围内的任何(单个单元格。 创建一个新的查询,【获取数据】【自其他源】【来自表格 / 区域】。...图 6-7 通过命名区域导入的数据 Excel 表的一个特点是有一个预定义的标题行,由于命名区域不存在这个功能,Power Query 必须连接到原始数据源,并运行其分析,来确定如何处理数据。...虽然用户可以通过数据源设置对话框一次性更新它们,但用户可能更愿意采取的方法是建立一个与文件的单个连接,然后引用该连接来提取用户所需要的任何其他数据。

16.3K20

Excel新函数】动态数组系列

一、简介 相比Power BI,Power Query和Power Pivot在行列层级运行计算,Excel一直以来主要还是在单元格层面上的。...TOCOL - 将数组或范围转换为单个列。 TOROW - 将范围或数组转换为单行。 WRAPCOLS - 根据每行指定的值数将行或列转换为二维数组。...三、应用案例——查询多列结果 以我们常用的vlookup为例。在下图这种场景,需要查询不同产品,三个地区的售价。常规做法,我们需要在I2:K2三个单元格,各写一个相似的vlookup公式。...如果使用数组运算,我们只需要在I3单元格输入一个公式,即可自动填充到J和K。注意,此时的数组是通过大括号来触发的。公式第三个参数,大括号引用了3、4、5列,即要查询第3、4、5列的值。...=VLOOKUP(H2,$A:$E,{3,4,5},0) 三、隐式交集运算符@ 隐式交集逻辑将多个值减少为单个值。上文两个例子,我们一个公式产生的结果,会自动填充到相邻的范围

2.9K40
您找到你想要的搜索结果了吗?
是的
没有找到

ChatGPT Excel 大师

选择要转换为 Excel 表格的数据范围。2. 访问表格工具设计选项卡,并为表格命名。3. 与 ChatGPT 互动,了解高级表格功能,结构引用、排序和过滤。...从列表中选择要运行或管理的宏。3. 选择适当的操作,运行、编辑、删除,或为宏创建按钮或快捷键。ChatGPT 提示“我已经创建了一些宏来自动执行我的 Excel 工作簿的任务。...如何在创建一个循环,迭代通过单元格范围并将操作应用于每个单元格,自动化重复任务?” 95....确定您在 Excel 的名称管理器遇到的命名范围或问题。2. 与 ChatGPT 合作描述命名范围、它们的目的以及遇到的任何问题,例如不正确的引用或过时的范围。3....与 ChatGPT 合作讨论数据脱敏方法、脱敏算法,以及如何在保持数据格式和完整性的同时脱敏数据替换敏感信息。ChatGPT 提示“我想提高 Excel 文件敏感信息的隐私保护。

5300

Power Query 真经 - 第 8 章 - 纵向追加数据

为了验证和可视化加载到 Excel 的数据量,可以在这里数据透视表来汇总数据。 选择 “Transaction” 表的任何单元格【插入】【数据透视表】。...但事实这里所示,追加和编辑单独的追加项,是一项重要的技能,用户必须掌握它,才能熟练地使用 Power Query。...这个问题的答案是肯定的,它涉及到利用在第 6 章中使用的 Excel.CurrentWorkbook 函数来读取动态命名范围。...是可以的,但正如第 6 章所提到的,没有内置函数可以从活动工作簿的工作表读取数据。相反,必须利用与命名范围对话的能力。一个特定的命名范围。...在公式栏输入以下内容: = Excel.CurrentWorkbook() 现在会看到所有的表格和命名范围的列表,包括 “打印区域”,如图 8-24 所示。

6.6K30

个人永久性免费-Excel催化剂功能第43波-文本处理类函数增强

一般DotNet程序猿可以使用大量微软团队写好的函数,但Excel用户却没这样的福气,Excel催化剂也当搬运工,把这些DotNet的文本处理类函数搬到Excel环境,供普通大众来使用。 ?...切割字符串可以是单个也可以是多个,多个时中间逗号隔开或者直接引用多个切割符号对应的单元格(连续单元格区域) 函数具体的效果如下: ?...只要出现过这一个字即可,多个一个字时可排列组合出多个匹配结果,A,B,C三个单个的字符,可以匹配A/AB、AC、ABC、ACB、B、BC、BA、BAC、BCA、C、CA、CB、CAB、CBA等结果 多个字符一起时...,如下图中的“美国” 仅匹配此多个字符的完整匹配,ABC,BC的MatchString,仅对源文本的ABC和BC两项匹配。...,重新定义Excel函数的学习和使用方法 第5波-使用DAX查询从PowerbiDeskTop获取数据源 第6波-导出PowerbiDesktop模型数据字典 第7波-智能选区功能 第8波-快速可视化数据

1.2K30

Power Query 真经 - 第 5 章 - 从平面文件导入数据

这一点至关重要,因为这意味着当数据被导入到另一个程序( ExcelPower BI )时,必须对其进行解析。...在 Excel ,这个值将被放置在一个单元格。 程序试图【dd/MM/yyyy】格式将 1/13/18 转换为一个日期,但由于没有 13 个月,它认为这不可能是一个日期。...5.2.3 提取数据 在一个新的工作簿,执行如下操作。 创建一个新的查询,【自文件】【 CSV / 文本】。...在 Power BI 桌面版,需要进入【文件】【选项】【选项和设置】。Power BI 在全局或当前文件层面都有【区域设置】选项,这取决于用户希望讲更改应用到的范围。...Power Query 的 CLEAN 函数功能与 Excel 的 CLEAN 函数功能一样,只不过这个函数的运行难以看到视觉上的效果。

5.1K20

Power Query 真经 - 第 3 章 - 数据类型与错误

3.1 数据类型与格式 在现场课程、博客评论和论坛帖子中经常收到的一个问题是:“如何在 Power Query 或 Power BI 中格式化数据?”...工作表单元格:无论是在表格、数据透视表还是 CUBE 函数(译者注:一种可以从数据模型中提取值的 Excel 函数),如果它位于 Excel 网格,则可以对数据应用数字格式。...【警告】 前两种方法只更新所选查询的数据源,而最后一种方法有一个好处,它将更改数据源的所有实例,即使它被用于多个查询。尽管如此,还需要单击【刷新预览】,以使编辑器认识到数据源已经更新了。...然而,在这样做之前,需要注意的是:单击单元格位置的不同,其功能也会不同。 如果单击单元格的 “Error” 一词,Power Query 将为查询添加一个新的步骤,并钻取到该错误。...在 Excel 的【查询和连接】窗格或 Power Query 编辑器【查询】导航器,都可以通过以下操作删除错误查询。 选择 “ErrorData 的错误” 查询并按下 DEL 键。

5.1K20

学习用Excel数据分析?恐怕还缺一本圣经。

随着时间的推移,DAX在那些讨论Power Pivot数据模型的Excel社区和讨论Power BI与SSAS数据模型的商业智能(BI)社区逐渐流行起来。...实际上,如果将Excel的某个范围定义为智能表格(使用创建表功能),那么你可以在Excel编写引用表和列的表达式。...在Excel,你可以使用[@ColumnName]格式引用表的列,其中ColumnName是要引用的列的名称,@符号表示“获取当前行的值”。...在Excel,不需要提供表名,因为Excel公式在单个表中计算。 但是在DAX,则需要指定表名,因为DAX在包含多个表的数据模型工作,来自不同表的两列可能具有相同的名称。...优点是,可以在单个步骤执行许多复杂的计算,而不必为此添加许多列,这对某些特定的公式有用。 缺点是,使用DAX编程的直观性不如Excel

2.4K20

节日分析神器:CUBESET-日期对比一网打尽

这是CUBE系列第三篇文章,前两篇: 将Power Pivot模型数据取值到单元格(第1节) 将Power Pivot模型数据取值到单元格(第2节) 今天是中秋国庆长假前的最后一个工作日,首先预祝各位读者双节快乐...如果Power BI设计报表,针对一个指标(销售额)你需要写一大堆度量值来完成不同周期的对比,TotalMTD、TotalYTD、DATEADD等等轮番上阵,每年的法定节假日周期不同,你还需要单独处理...最简单的数据提取如下图所示,手动收入需要提取数据的开始日期和结束日期,在G列使用CUBEVALUE函数调用Power Pivot中新建的销售额度量值,CUBERSET指定日期范围为E列和F列的值。...红框,第一个销售员指销售员这张维度表,第二个销售员指表的列。 接着,你可能会问,我想看任意时间范围的所有指标怎么办?复制G列的公式,把其中的销售额度量值替换为别的指标,依次排列。...Power BI做到以上效果是可能的,但是是困难的;Excel是灵活的、容易的。随着Power BI的普及,不停有人问,Excel会被替代吗?显然不会,二者场景不同。

23560

Power Pivot忽略维度筛选函数

分列数据的方法比较 如何在Power Query中提取数据?——文本篇 如何在Power Query中提取数据?——数值篇 如何在Power Query中提取数据?...——时间篇(1) 如何在Power Query中提取数据?...——时间篇(2) 从如何在Power Query中提取数据——记录片 如何在Power Query中提取数据——列表篇(1) 如何在Power Query中提取数据——列表篇(2) 如何在Power Query...中提取数据——列表篇(3) 如何在Power Query中提取数据——列表篇(4) 如何在Power Query获取数据——表格篇(1) 如何在Power Query获取数据——表格篇(2) 如何在...智能日期运用——连续时间(2) Power Pivot智能日期运用——时间点 如何在DAX Stadio和Excel返回表和度量值?

7.8K20

吐槽下Excel的十大不规范使用问题

本来是设置几十个单元格,变成了设置几百万个单元格。 因为这些的不合规的操作,致使出现了工作薄文件徒然增大,明明几百行的数据,文件却几十M的大小,明明简单的一个函数,运行起来却巨慢无比。...就算各家插件有几种能耐帮你重新从一堆文本里把需要的信息提取出来,但这个意义何在,这就是加班做的事么?...某些时候,因数据分析的上层需求,需要在数据源中提取相关信息作分析,如果数据源不规范,在数据处理过程,通过查找、替换或各种插件的功能对数据源进行更改,并在原单元格上覆盖保存。...简单一个转换大小写功能,处理成了全部大写,但哪天想看回原始的数据是怎样的,已经没法返回,或更有一些是一个信息量很大的单元格里,如上文的猪肉,3斤,油8两,青菜800克。。...,重新定义Excel函数的学习和使用方法 第5波-使用DAX查询从PowerbiDeskTop获取数据源 第6波-导出PowerbiDesktop模型数据字典 第7波-智能选区功能 第8波-快速可视化数据

1.2K20

2.4 数据清洗12招

1 首行作标题 我们把数据获取到查询往往首行的标题是未识别的状态,只需单击将第一行作为标题。 ?...5 提取 在2.3的案例我埋了个小伏笔,利用了Excel的LEN函数来计算长度,其实在提取功能也有个长度的选项,选择它即可计算出字符长度,只不过在转换选项卡中直接选的话会破坏现有的列,你需要利用取消步骤退回再去提取范围...可能你会发现在转换和添加列选项卡中都有提取功能而且长得一模一样,区别在于添加列是在后面添加一列提取出选中列的长度、字符、范围等,而转换的是在当前列操作。 ?...8 填充 填充有向上和向下两个方向,顾名思义,即把上面或下面的单元格内容填充到无效单元格或空白单元格。...该功能在Excel的实现方式是查找错误或空白,在当前单元格输入=上面或下面单元格后按Ctrl+Enter,现在利用PQ就不需要繁琐的小技巧操作了。

2.4K30

Excel用户如何学习数据分析语言DAX?

随着时间的推移,DAX在那些讨论Power Pivot数据模型的Excel社区和讨论Power BI与SSAS数据模型的商业智能(BI)社区逐渐流行起来。...实际上,如果将Excel的某个范围定义为智能表格(使用创建表功能),那么你可以在Excel编写引用表和列的表达式。...在Excel,你可以使用[@ColumnName]格式引用表的列,其中ColumnName是要引用的列的名称,@符号表示“获取当前行的值”。...在Excel,不需要提供表名,因为Excel公式在单个表中计算。 但是在DAX,则需要指定表名,因为DAX在包含多个表的数据模型工作,来自不同表的两列可能具有相同的名称。...优点是,可以在单个步骤执行许多复杂的计算,而不必为此添加许多列,这对某些特定的公式有用。 缺点是,使用DAX编程的直观性不如Excel

1.3K10

excel常用操作大全

如果您在原始证书编号后添加19,请在B1单元格写入:=A1 '13 ',然后按回车键。 2.如何在文件下拉窗口底部设置最近运行的文件名数量?...3.在EXCEL输入“1-1”和“1-2”等格式后,将成为日期格式,1月1日和1月2日。我该怎么办? 这是由EXCEL自动识别日期格式造成的。...在EXCEL菜单,单击文件-页面设置-工作表-打印标题;您可以通过按下折叠对话框的按钮并用鼠标划定范围,将标题设置在顶端或左端。这样,Excel会自动将您指定的部分添加为每页的页眉。...21、鼠标右键拖动单元格填充手柄 在前一节,介绍了鼠标左键拖动单元格填充手柄自动填充数据序列的方法。事实上,鼠标右键拖动单元格填充手柄更灵活。...选择区域后,选择“插入”\“名称”\“定义”,为区域命名组1,然后在公式中使用区域名称,“==SUM(组1)”。

19.1K10

Power Query 真经 - 第 1 章 - 基础知识

(译者注:每章示例文件都分成了不含有参考答案的用来练习的版本以及包括参考答案在内的完成版本,当打开完成版本时由于路径不一致的问题会导致报错,请读者自行修改为本机对应的文件路径以使其正常运行) 1.2.2...查询导航窗格:在 Excel 365 之前的 Excel 版本,这个窗格默认是折叠的。用户可以单击【查询】一词上方的 “>”,以使其最大化,显示其中所有的 Power Query 查询列表。...本书后续内容均保持查询步骤使用英语以便理解最佳实践,当然这不是必须的,在查询步骤中使用本地化语言,:中文对 Power Query 的使用没有任何影响,取决于个人习惯。)...图 1-13 重命名查询和最后的查询步骤的结果 1.4.3 在 Excel 中加载查询 为了最终完成查询并将数据加载至 Excel ,需要进行如下操作。...转到【数据】选项卡,【查询和连接】。 至于如何在 Excel 启动 Power Query 编辑器的选项,这些是可以自由选择的。

4.7K31

Python与Excel协同应用初学者指南

标签:Python与Excel协同 本文将探讨学习如何在Python读取和导入Excel文件,将数据写入这些电子表格,并找到最好的软件包来做这些事。...可以通过运行type(wb)检查wb的类型。 图10 上面的代码块返回在Python中加载的工作簿的工作表名称。接下来,还可以使用此信息检索工作簿的单个工作表。...但是使用Openpyxl时,除了指定要从中提取值的索引外,还需要指定属性.value,如下所示: 图12 如你所见,除了value属性外,还有其他属性可用于检查单元格row、column和coordinate...这将在提取单元格值方面提供很大的灵活性,而无需太多硬编码。让我们打印出第2列包含值的行的值。如果那些特定的单元格是空的,那么只是获取None。...简单地说,可以在get_book_dict()函数的帮助下提取单个字典的所有工作簿。

17.3K20

Power Query 真经 - 第 2 章 - 查询管理

用于提取 “原始数据” 的查询层:这层查询是用来从数据源中提取数据的。这里只做了很少的转换。事实上,在这个步骤,通常只删除不使用的列或行。...2.1.2 单个查询的好处 Miguel 倾向于在单个查询或尽可能少的查询构建所有步骤。...在其他使用 Power Query 的工具 SSIS 和 Azure Data Factory ,只支持单个查询。如果未来需要将解决方案移植到这些平台之一,那么使用单个查询是一个更好的选择。...总是可以看到所有的东西是如何在一个单一的视图中联系在一起的,并对查询进行最小修改,从而使转换过程处于最理想的状态。 当使用查询诊断工具和检查更高级的特性(查询折叠和检查查询计划)时,这非常有用。...如果在 Excel 的 【查询 & 连接】窗格工作,需要采取的步骤如下。 右击查询或组【上移(下移)】。 根据需要多次重复这个动作,以使的项目获得的正确的顺序。

2.6K40

Power Query 真经 - 第 9 章 - 批量合并文件

它的目的是让用户在将所有文件追加到单个表之前,对单个文件执行数据转换(用户在这里执行的步骤会自动在转换函数自动照搬运行并合并,以便它们可以应用于文件夹的所有文件)。...如果将该文件夹同步到电脑上,则可以使用前面描述的本地文件夹连接器。 如果连接到云端托管版本的 SharePoint 文件夹,则可以一个 SharePoint 专用连接器来实现。...这可以通过筛选【文件夹路径】列来完成,但这里有一点需要注意:每个文件的整个文件夹路径都包含在这些单元格。...图9-18 为“示例文件”生成的 36 行最终输出的一部分 忽略“Forecast”硬编码列名的潜在问题所带来的挑战,当把它保持在单个文件的范围内时,这是一个相当直接的【逆透视】工作。...在这个示例,可以使用“Folder Path”列,因为用户是按照逻辑顺序来命名这些文件的。如果没有这样的结构,那么可能想依靠“创建日期”或“修改日期”字段的一个。

4.7K40

DAX 2 - 第一章 什么是 DAX

从那时起,DAX 在 Excel 社区(使用 DAX 在 Excel 创建 Power Pivot 数据模型)和商业智能(BI)社区(使用 DAX 在 Power BI 和 Analysis Services...不过,DAX 和 Excel 函数还是有一些根本性区别的。 单元格与表 Excel单元格执行计算。...然而,引用表和列在 Excel 已经出现过。如果我们通过“表格”功能将 Excel 范围定义为表格,Excel 就可以引用表格和列来编写公式。...你可能认为 Excel 有两种不同的计算方式,我们可以标准引用单元格单元格 F4 的公式显示为 E4*D4,或者在表引用列。...在 DAX 里,你可以使用迭代器在一个步骤执行相同的操作,迭代器的工作方式正如其名:迭代表,并对表的每一行进行计算,将结果予以汇总,返回需要的单个值。 [!

4.5K30
领券