从Excel数据模型/Power Query查询单个数据点(获取和转换数据)

问题描述

我使用的是最新版本的Excel 2016(通过O365 E3许可证)，并使用Power Query/Get&；Transform Data。我可以成功地创建查询并将它们加载到页面。我还成功创建了Power Pivot报表。

我想从通过Power Query加载的数据中查询单个数据点。例如，假设一个名为DivisionalRevenue的数据集：

Date        Division    Revenue
2016-01-01  Alpha       1000
2016-01-02  Alpha       1500
2016-01-01  Beta        2000
2016-01-02  Beta         400

我可以轻松地将其加载到Excel工作簿中，或者将其包含在数据模型中并创建一个Power Pivot。然而，Power Pivot并不总是满足我的需求，特别是在数据在页面上的显示方式方面。为了实现我的目标，我可能希望能够查询单个数据点。

我希望页面上有一个单元格，其中包含一个公式，我可以使用它来查询各个数据点。如果它在数据透视表中，我可以使用如下内容：

=GETPIVOTDATA("Revenue",$A$3,"Date",DATE(2016,1,1),"Division","Alpha")

可以从页面上的单元格检索查找值(日期和分区)，也可以将其硬编码到公式中。这是我正在处理的几个报告的要求。

或者，我可以添加一个组合的查阅列，将Date和Divition连接在一起，并使用VLOOKUP拉取如下值：

=VLOOKUP("42371Alpha",I9:L13,4,FALSE)

最后，我可以结合使用索引和匹配来识别正确的行号，然后提取数据。

所有这些解决方案都需要将数据加载到工作表中。需要一个必须刷新的数据透视表才能正常工作。另外两种方法需要创建任意查阅列，以便可以基于多个字段(在本例中为日期和分割)匹配一行，并且必须确保该查阅字段的公式在数据表的长度范围内得到正确扩展。在这两种情况下，我在与同事共享此工作簿时都会有顾虑，以防有人影响透视表或查找相当脆弱的设置。

因此，我真正想要找到的是等同于针对数据集的数据透视表查询。

** This doesn't exist, but I would like to know if something like it does **
=GETQUERYDATA("Revenue","DivisionalRevenue","Date",DATE(2016,1,1),"Division","Alpha")

这样的事情存在吗？这样的事情做得到吗？我是否可以从通过增强查询/获取和转换数据创建的数据集中检索任意数据点？

推荐答案

我认为您需要的是立方体函数：

Some Background

How to easy create cubefunctions from a pivot table

这篇关于从Excel数据模型/Power Query查询单个数据点(获取和转换数据)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！