25惊人的电源查询提示和技巧

电源查询是惊人的数据转换工具!它允许您轻松地导入和转换数据,并帮助您为数据创建可重复和健壮的过程。

如果你花少量的时间去学习它,将会节省你的时间和精力。

最好的部分是,它建立在Excel 2016或更高版本中。它也是电力BI中使用的相同技术,因此您在同一时间学习两个需求数据技能!

如果你还没有听说过power query和它能做的很棒的事情,或者你想更好地完全理解它,那么看看我的完整的电源查询指南事先。

这里有一些提示和技巧来帮助你最大限度地利用这个非常有用和简单的工具。

视频教程

删除步骤直到结束

删除-直到结束25惊人的电力查询提示和技巧

有时候,当您构建一个包含许多步骤的查询时,您最终会走上错误的道路。如果您能够确定查询在哪个步骤开始出错,那么您可以删除该步骤和之后的所有步骤,重新开始(无需完全重新开始)。

在里面应用台阶窗口窗格,右键单击要删除的第一步,然后选择删除直到结束从菜单中。

这将删除该步骤和该步骤之后的所有查询步骤。

预览或导航到表对象

查询与表对象25惊人的电力查询技巧和技巧

查询中的一列可能包含表对象。如果是这种情况,列的行条目将显示文本表格和数据类型图标。

您可以在查询中预览表或导航到它,具体取决于单击单元格的位置。

预览表对象25惊人的电力查询技巧和技巧

如果单击包含表对象的单元格的空白区域,则将显示查询下表的预览。

导航到表对象25惊人的电力查询提示和技巧

当您将鼠标悬停在单元格中的单词Table上时,它将更改为指针图标。当您单击查询的这一部分时,它将向查询添加导航步骤,导航到特定的表。

双击编辑查询

双击编辑-查询25惊人的电力查询提示和技巧

可以通过两种不同的方式来编辑查询查询和连接窗口窗格。

  • 你可以右键点击在查询上,然后选择编辑从菜单中。
  • 你可以徘徊查询上的光标,直到出现Peek窗口,然后按下编辑来自Peek窗口的按钮。

最快的方法是左键点击两倍在查询。这将打开所选查询的查询编辑器。

自动为加载到表中的任何查询添加行索引

打开外部数据属性菜单25惊人的电源查询提示和技巧

您可以通过强大的查询编辑器向任何查询添加索引列,但也可以向以特殊方式加载到表中的查询添加索引行。在表输出中选择一个单元格,然后转到数据TAB键,按属性按钮。

请注意属性在查询和连接窗格的右键菜单中找到的选项将打开查询属性窗口,而不是本技巧所需的外部数据属性窗口。

包括-行-数字25惊人的电力查询提示和技巧

这将打开外部数据属性菜单,您可以查看包含行数字选项,然后按好吧按钮。

现在,下次刷新加载到表中的查询时,一个名为_RowNum将作为最左边的列显示,并将包含从0开始的行号的索引。

更改默认加载选项

当你按下时密切与负载对于第一次查询的按钮,电源查询将数据加载到工作簿中的Excel表中,并不会将数据添加到Data Moedel中。这是Power查询中的默认加载设置。

如果您希望将数据加载为连接或需要将其加载到数据模型中以稍后使用Power Pivot,则需要选择关闭和加载到而是在每次创建新查询时选择这些选项。

specific - custom - default - load - settings 25个惊人的电源查询提示和技巧

如果您发现您最终创建的大多数查询只需要一个连接或需要加载到数据模型中,那么您可以更改默认的加载设置。去吧数据选项卡,然后选择获取数据然后打开查询选项

在里面全局数据加载设置,选择指定自定义默认加载设置选项,然后选择或取消选择所需的选项。

  • 取消勾选的加载工作表盒子和盒子加载到数据模型框将导致查询仅作为连接加载,而不会将数据加载到数据模型中。
  • 检查加载工作表框和取消勾选加载到数据模型框将把查询加载到Excel表中,而不是将数据加载到数据模型中。注意这和when是一样的使用标准负载设置被选中。
  • 取消选中加载工作表盒子检查加载到数据模型框将导致您的查询仅作为连接加载,并将数据加载到数据模型中。
  • 检查这两个加载工作表盒子和盒子加载到数据模型框将把查询加载到Excel表中,并将数据加载到数据模型中。

在查询编辑器中显示一个单级字体

单空格字符查询编辑器25惊人的电力查询技巧和技巧

有时很难判断一列数据中的每个项是否具有相同的字符长度。当使用默认查询编辑器字体时,不是所有的字符都有相同的宽度。这样就很难判断是否能够在整个列中按照字符长度一致地分割字段。

查看-Tab-Monospaced-Option 25惊人的电源查询提示和技巧

将字体更改为等宽字符集,请转到视图标签并检查单次选择。这样可以很容易地看到字符数是否对齐。

导航列与箭头键

导航列 - 带箭头键25惊人的电源查询提示和技巧

您可以通过单击列标题来选择列。当选中该列时,它将变成浅绿色,然后您可以通过右键单击列标题或使用ribbon上的任何转换命令对其执行各种转换。

属性选择列后,可以导航到其他列剩下箭头键。事实上,您甚至不需要首先选择一列,只需按下右箭头键,第一列就会被选中。

可以使用行的数据行完成相同的技巧。从单击行标题中选择一行后,您可以使用使用的行程导航到其他行向上箭头键。同样,不需要首先选择一行,只需按下向下箭头键,第一行就会被选中。

如果在列中选择单个单元格,则可以使用Ctrl+空间键盘快捷键选择整个列。你也可以使用Ctrl+一种键盘快捷方式选择整个表。就像在常规Excel工作表中一样!

评论权力查询方程式

评论-在高级编辑25惊人的能量查询提示和技巧

你可以在UI中使用power query做很多事情,而不用触及任何在幕后创建的M代码。如果您确实开始使用高级编辑器研究M代码(视图选项卡然后高级编辑器),然后,您可能会想要向您的代码添加评论。

您可以用两种方法来实现这一点。

可以创建一行注释,方法是用//双向斜杠。

您可以创建多个行注释,方法是在注释的第一行以/*向前斜线后跟ASTIX,然后结束与a的最后一行* /Astrix后面跟着一个斜杠。

遗憾的是,您添加到高级编辑器中的任何注释都将在高级编辑器中可见。通过查询的应用步骤导航时,您将无法在公式栏中看到它们。

无需打开高级编辑器即可添加注释

添加评论-使用公式- bar 25惊人的能量查询技巧和技巧

可以在不打开高级编辑器的情况下添加注释。从要添加注释的应用步骤中选择步骤。使用注释类型将注释添加到步骤的末尾,然后按下输入按钮。

注释将显示在公式栏中,直到您导航到不同的步骤或不同的查询。当您返回时,它将不再可见的公式栏,但它仍然存在于M代码和高级编辑器可见。

添加在公式栏中保持可见的注释

公式25惊人的能量查询技巧和窍门

我已经说过,评论只在高级编辑器中可见,但有一个窍门可以让它们显示在公式栏中。

将注释写在公式中,而不是在步骤的末尾。你需要使用开口/*和关闭* /注释字符,因为注释将需要关闭,所以公式的其余部分不会被视为注释的一部分。

通过查询的应用步骤导航时,这将在公式栏中可见。

使用查询步骤属性添加注释

添加描述-查询-步骤25惊人的电源查询提示和技巧

您可以使用查询的属性窗口向应用步骤窗口窗格中列出的任何查询步骤添加注释。右键单击查询并选择属性从菜单中。

您将能够向步骤中添加描述。该描述将以一行注释的形式出现在高级编辑器中该步骤的M代码上方。

您还可以从此窗口更改步骤的名称。这也将改变高级编辑器中该步骤的引用。

创建顺序列表

创建-a-custom-column-with-a-list 25惊人的电源查询提示和技巧

知道如何创建Power查询中的顺序列表对象。添加自定义列,其中包含下面的列表,从5开始,并以10结尾。

= {5 . . 10}

这甚至会通过引用列而不是静态硬编码号来工作。使用类似于以下内容的公式,其中[开始]和[结束]是查询中的两个整数列。

= {[开始] .. [结束]}

如果Start或End列是小数,表达式将导致错误。

如果“开始号”大于“结束号”,该表达式将导致一个空列表。

使用过滤器时避免区分大小写

电源查询区分大小写。如果您不小心,这会导致数据分析中的错误。

如果你过滤掉这个项目键盘然后显示为键盘在源数据中,查询不会过滤掉小写版本。

添加 - 大写列25惊人的电源查询提示和技巧

在应用任何滤波器之前,可以通过将列转换为大写字母来避免这种情况。

我们还可以通过使用的数据在我们的数据中保留原始案例大写字母添加列选项卡。这将创建一个带有大写值的新列,同时保持原始列的完整性。

过滤 - 上大写列25惊人的电源查询提示和技巧

现在我们可以对该列的大写版本应用任何过滤器,然后删除后面的列。这允许我们在不改变数据大小写的情况下应用不区分大小写的过滤器。

双击重命名列

双击一栏标题重命名25惊人的电力查询提示和技巧

在您的power query职业生涯中,您可能会重命名许多列,所以您将想要学习以最快的方式来做这件事。在列标题上双击左键可以快速更改列的名称。

用键盘快捷键重命名列

重命名与f2 25惊人的电力查询提示和技巧

可以使用键盘快捷键重命名列。这个速度有点慢,但对于那些喜欢键盘而不是鼠标的人来说值得一看。选中列后,按下F2.在键盘上,您可以重命名列标题。

浏览复杂的查询依赖项

当您的工作簿开始因查询而超载时,这个特性可以成为救命稻草。很容易忘记什么查询使用什么数据源,以及哪些查询相互依赖。

查询依赖关系25惊人的电源查询提示和技巧

去吧视图TAB键,按查询的依赖性命令以打开查询依赖项查看器。这将为您提供工作簿中所有查询及其先例和依赖项的可视化表示。

查询依赖关系 - 查看25惊人的电源查询提示和技巧

查询依赖项窗口中有很多。

  1. 您将能够看到您的数据源。这些是窗口中的较小框,并且将具有更大的图标,指示源数据的类型。
  2. 左键单击任何项​​目,它将与其所有家属和先例一起变浅绿色。
  3. 右键单击任何项​​目,将出现菜单,该选项将驻留在该项目上的视图或驻留在所有先例和依赖项上的视图。
  4. 左键单击并拖动任何空白区域来移动视图。
  5. 您可以更改布局以从上到下、从下到上、从左到右或从右到左显示依赖项。
  6. 您可以使用缩放条手动放大或缩小视图。另一种可以放大视图的方法是使用鼠标滚轮(如果你有的话)。另一个选项是双击任何空白区域,左键放大。
  7. 您可以使用右下方的缩小按钮在视图上完全缩小。

钻取到一个值

钻下到一个单值25惊人的电力查询提示和技巧

深入查询中的任何单个项目。右键单击单元格并选择

根据您的数据,这可能是单个值,列表或表。这对于从您的数据中创建单个值,这可能是有用的,您可以在自定义列计算中引用。

在“电源查询”中启用“快速数据加载”

enable - fast - data load -in-Power-Query 25个惊人的电源查询提示和技巧

更快总是更好,对吗?如果您的查询缓慢,您可以启用快速数据加载选项以加快它们。去吧数据TAB键,按获取数据按钮,然后打开查询选项

去吧全局数据加载设置并检查快速数据加载盒子。您的查询将花费更少的加载时间,但在加载期间,Excel可能会没有响应。

禁用自动检测数据类型功能

改变类型-步骤25惊人的能量查询提示和技巧

将数据与电源查询导入Excel时,Excel将猜测每个列的数据类型基于前一百行。然后,电源查询将自动创建查询步骤以将第一行推广到列标题(用于CSV文件)并更改数据类型。

如果您希望控制数据类型选择,但excel不能自动为您创建此步骤,则可以禁用此选项。

禁用自动检测列 - 类型25惊人的功率查询提示和技巧

要打开查询选项,请转到数据选项卡然后获取数据然后选择查询选项

去吧当前工作簿数据加载设置并勾选复选框自动检测非结构化源的列类型和标题

下次创建查询时,除了源步骤外,不会看到其他步骤。

探索功率查询的函数库

Power查询的M代码语言是一种功能语言。这意味着M代码主要由基于其输入进行特定事物的功能构建。在Power查询中写作M代码就像用乐高的建筑物,有许多不同类型的乐高碎片,每件件都设计用于特定目的。

M代码附带了大量可供使用的函数。事实上,您可以在power查询编辑器中探索可用函数的整个库。

分享关键字25惊人的电力查询提示和技巧

创建一个新的空白查询,转到数据TAB键,按获取数据命令然后选择从其他来源然后选择空白查询。现在在公式栏中输入=#共享并按输入

功率查询列表25惊人的电源查询提示和技巧

电源查询将显示工作簿可用的所有项目。这将包括所有电源查询功能!

功能指南25惊人的电力查询提示和技巧

如果你点击列表中的任何一个函数,你会看到一个关于该函数的迷你帮助指南。

  1. 显示函数名称。
  2. 简要说明了功能的作用。
  3. 您可以在数据上测试功能。
  4. 显示了函数语法。
  5. 有一个简单的示例,用于演示功能的输入和输出。

将查询分成两部分

Extract-Previous-Query-Steps 25惊人的电源查询提示和技巧

也许您的查询变得太长了,或者您想在其他查询中使用它的第一部分作为源代码。如果是这种情况,您可以将查询分成两部分。

右键单击要分割查询的步骤,然后选择提取上一页从菜单中。这将在所选步骤之前提取步骤进入新查询。然后,系统会提示您命名新查询。

然后,其余步骤将引用新查询作为其源。

将查询复制并粘贴到新工作簿

复制和粘贴查询 - 新工作簿25惊人的电源查询提示和技巧

如果需要重用以前工作簿中的电源查询,可以轻松地将其从旧工作簿复制并粘贴到新工作簿。

在里面查询和连接旧工作簿的窗口,选择要复制的查询,然后右键单击并选择复制从菜单中。现在导航到新工作簿并在查询和连接窗口,选择粘贴从菜单中。

这将复制所有选定查询以及他们引用的所有查询。

如果复制的任何查询或它们所依赖的查询引用旧工作簿中的表,那么这些查询将显示下载没有完成错误消息。您需要将这些表分别复制到新的工作簿中,以修复此问题。

双击重命名查询

双击重命名查询25惊人的功率查询提示和技巧

您可以从查询编辑器左侧的查询列表窗格中快速重命名任何查询。双击要重命名的查询,然后输入新名称并按下输入确认更改。

双击以隐藏或显示查询编辑器功能区

双击-to-hide-the-clowbon 25惊人的电源查询提示和技巧

如果您发现电源查询编辑器中的功能区正在妨碍,您可以隐藏。双重左键单击任何色带选项卡以隐藏功能区。双击再次单击选项卡以显示功能区。

是的,这与常规的工作簿ribbon相同,但它也可以在电源查询编辑器中使用!

禁用关系检测

禁用 - 创建关系25惊人的电源查询提示和技巧

这是另一个技巧,可以潜在地加快您的电力查询查询。

将数据从电源查询加载到数据模型中时,Excel将尝试在数据模型中查找和构建新数据和其他表之间的关系。此额外处理步骤将减慢您的查询。

如果您总是将数据加载到数据模型中,那么您可能需要关闭此功能以节省时间。去吧数据然后按获取数据按钮,然后打开查询选项

在里面当前工作簿数据加载设置,取消在第一次添加到数据模型时会在表之间创建关系选择。这将防止Excel在数据模型中创建表之间的关系。

关于作者

John Macdougall.

John Macdougall.

John是微软MVP和自由顾问和培训师,擅长Excel, Power BI, Power automation, Power Apps和SharePoint。你可以在约翰的博客或YouTube频道上找到其他有趣的文章。

订阅

广告

相关文章

评论

18岁的评论

  1. 存款准备金率的项目

    谢谢你的文章..这很有帮助......

    回复
  2. Sandeep Kothari.

    看起来太棒了!

    回复
  3. 罗兰

    非常好地组合在一起-对那些探索电源查询的可能性有很大的帮助!谢谢你!

    回复
    • 约翰

      没问题罗兰!

      回复
  4. stevej.

    +1 @roland.
    高级编辑器中的w.r.t.注释:如果注释行正好在步骤上方,当你将鼠标悬停在“应用步骤”窗格中的步骤名称上时,它将变成工具提示。

    此外,查询代码本身是Unicode,因此您可以在步骤名称中使用符号。通常这是矫枉过正,但您可以使用它来引起调试步骤等事物的注意。

    回复
    • 约翰

      非常棒的建议,史蒂夫,谢谢!

      回复
  5. Sylvain.

    嗨约翰,谢谢你的提示。如何改变已经加载的查询,但是之后将在那里实现它只需要连接?我只能想到重复/编辑 - 然后更改连接并删除原始。尚未尝试,因为我有很多人做(减少不需要的额外表......)

    回复
    • 约翰

      只需从工作簿中删除它加载的表。查询将成为一个连接。

      回复
    • 约翰

      您还可以通过右键单击查询和Connections窗口窗格中的查询并从菜单中选择加载来更改事项。

      回复
  6. 穆罕默德

    谢谢你!
    很好

    回复
  7. 马丁

    伟大的提示,谢谢。

    回复
    • 约翰

      马丁没有问题!

      回复
  8. luis.

    伟大的材料!非常感谢

    回复
  9. 克里斯蒂

    嗨,John,我正在向表加载一个power查询,然后向表添加额外的列以执行计算。当我刷新查询时,会花费很长时间,因为它是在后台刷新期间进行计算的。我关掉了自动计算,但它仍在计算。我希望它从电源查询刷新表,然后计算我添加的列。什么好主意吗?谢谢

    回复
    • 约翰

      我唯一可以想到的是删除所有公式,除了刷新前第一行中的那些。然后在刷新后从第一行复制公式。

      回复
  10. 丹

    您是否知道有一种方法(或任何第三方编辑器或插件)可以让您打开给定的Excel文件的电源查询高级编辑器,而无需所有的单击?我们发现Power Query真的很有用,但是使用起来很麻烦,因为它需要5次点击才能到达高级编辑器,而这正是我们想要的。

    回复
    • 约翰

      对不起,我什么都不知道。您可以将查询和连接窗格中的查询复制到文本编辑器中,这将为您提供M代码。

      回复
  11. 丹

    谢谢 - 如果他们希望更好地采用这项技术,它感觉就像Microsoft需要解决。如果您可以使用Visual Studio打开Excel文件并在具有语法突出显示,IntelliSense等中编辑嵌入式M代码的Excel文件。对我来说,这对我来说,这是一个像这样的东西都是惊人的。

    我确实找到了一个值得分享的有用捷径。如果将“启动电源查询编辑器”命令添加到快速访问工具栏,则可以减少要获取编辑器的点击次数。有助于缓解一些痛苦......

    回复

提交评论

您的电子邮件地址不会被公开。必需的地方已做标记*

这个网站使用Akismet来减少垃圾邮件。了解如何处理您的评论数据

获取最新消息

跟着我们

在社交媒体上关注我们,了解Excel中的最新技巧!

把它钉在Pinterest上

分享这