PowerQuery&M函数

将数据加载到PowerQuery编辑器

切换到对应的表格-数据-获取和转换数据-来自表格/区域-弹出PowerQuery编辑器-关闭并上载至-仅创建链接

这样就可以建立excel和PowerQuery的连接，在excel点击数据-查询和连接，左侧就会弹出查询与连接的表格

PowerQuery的表格可以查看数据的类型，点击“ABC”可以修改数据类型，双击“菜品”可以修改名称

横向合并

将订单信息，订单想去，菜品价目，原料清单四个表格横向合并为一个新的表格。

将数据上传到PowerQuery，选择订单信息这个表格，右键点击引用生成副本，双击重命名为“合并”，点击合并查询-将查询合并为新查询，弹出窗口，选择订单详情表，再点击两个表格的“订单编号”列（根据订单编号匹配两个表的信息），点击确定即可得到横向合并的两个表。

合并之后的表格订单详情表格的信息是以Table类型存在，此时点击“Table”旁边的空白可以在整个表格的下方看到单元格内的详细信息，直接点击“Table”整个表格会返回单元格内的信息，可以在旁边的窗口点击“源”，这样就可以完整的表格啦！

选中“T0020_订单详情”标题旁边的“→”，勾选需要的列就可以，一般不勾选“使用原始列名作为前缀”，点击确定就可以把Table中的信息展开。再按照相同的步骤就可以合并所有表格，

再按照相同的步骤就可以合并所有表格，选中合并好的表格点击“关闭并上载至”，点击“仅限连接”，打开查询与连接窗口，右键合并好的表格选择“加载到”，选择新工作表，就可以将合并好的表上传到excel。

纵向合并

将仓库，组装一部，组装二部三个表格纵向合并成一个新的表格。

新建一个工作表-数据-获取和转换数据-获取数据-来自文件-从excel工作簿-在电脑上选择待合并的工作表-选择多项勾选三个表格-转换数据-追加查询-将查询追加为新查询-三个或更多表-将要合并的表格添加到要追加的表-确定。

按住Shift键选择第一行和最后一行可连续选中。

纵向合并不能在子表的基础上合并，不能在“仓库”追加查询“组装一部”，合并之后再追加查询“组装二部”，需要在一个新的工作表中合并三个表格。

D老师的解释：因为追加查询的操作本质是创建一个全新的、独立的查询，而不是在原查询上直接修改。这个新查询的职责是整合所有子表的数据，并作为后续操作的单一数据源。

数据透视

对横向合并的表格做数据透视，统计不同送餐时间不同原料的需求量总和。

先计算原料数量，点击添加列-自定义列-定义列名，输入公式-确定

按住Ctrl选中要透视的列，右键“删除其他列”，对“要求送餐时间”按升序排序选择透视列，透视的列是“要求送餐时间”，值列选择“原料数量”，高级筛选选择“求和”。

筛选出空值后，点击“关闭并上载至”，选择表-现有工作表。

逆透视

将数据透视表转换为明细表，即对“要求送餐时间”逆透视，按住Ctrl选中“原料”，“单位”，右键点击“逆透视其他列”，即可生成明细表。

合并同类项

将订单详情表合并同类型。

选择“分组依据”，弹出分组依据的窗口，选择基本，按照“订单编号”分组，修改列名为“ALL”，操作选择“所有行”。

点击确定后生成新的列ALL，数据类型是Table，点击Table右边的空白处可以看到详细内容

选择添加列-自定义列，弹出自定义列的窗口，输入公式[ALL][所定菜品]就可以将“所定菜品”提取出来生成新的列。输入“[所定菜品]”后会出现空格要删除，公式正确与否可以看窗口最下方。

自定义列的值类型为List，点击List的右边空白处可以看到详细内容。

此时还没有办法看到每个订单编号对应的菜品，需要使用M函数Text.Combine，第一个参数为List,第二个参数为文本的分隔符，点击右侧查询窗口，“已添加自定义”旁边的小齿轮就可以继续编辑。

输入公式点击确定，删除"Table"就可以得到合并后的表格。

可以在文件-新查询-其他查询-空查询，输入=M函数，了解对应M函数的用法。

• Table 是行（Record） 和列（List） 的容器。

• Record 是字段的容器，代表了表中的一个实体。

• List 是值的容器，代表了表中的一个属性集合。

PowerPivot&D函数

添加PowerPivot

文件-选项-加载项-Excel加载项-COM加载项-转到-勾选，添加PowerPivot。

将数据加载到PowerPivot

1.选中表格-插入数据透视表-勾选将此数据添加到数据模型，再点击PowerPivot-数据模型-管理

2.先将数据加载到PowerQuery，关闭上载至-仅创建连接，勾选将此数据添加到数据模型，再点击PowerPivot-数据模型-管理。这种方法不能再PowerPivot中直接删除数据，需要先在查询与连接窗口删除数据，才能在PowerPivot中将数据删除。是目前最主流的方法。

3.点击PowerPivot-表格-添加到数据模型，不是很推荐，可能没办法随时更新数据。

非重复计数

计算每个客户下单数量（去重）

选中表格中的其中一个单元格-插入-数据透视表-勾选将此数据添加到数据模型，行标签选择客户姓名，值选择订单编号，右键点击“值字段设置”，在下拉框中选择非重复计数。没有勾选将此数据添加到数据模型，直接插入数据透视表是没有办法实现非重复计数的。

计算列和度量值

计算列和度量值的概念

计算列：在PowerPivot的后台，像Excel中在公式栏输入公式的方式添加的列，就是计算列。计算列会增加数据模型的大小，非必须不推荐使用。

度量值：一种自定义的数据透视表值区域汇总方式。

在PowerPivot中双击“添加列”，修改列名，再双击第一个单元格，即可输入公式“='合并3'[数量]*'合并3'[数量.1]，”点击“√”即可计算结果，跟超级表中的计算相同。

选中订单编号，选择自动汇总-非重复计数，在PowerPivot中会生成公式“订单编号 的非重复计数:=DISTINCTCOUNT([订单编号])”，这就是度量值。

再点击数据透视表，字段设置中会出现“fx订单编号的非重复计数”，将字段拖到“值”，就可以得到上面的非重复计数的数据透视表。

这样操作如果度量值修改了，数据透视表也会同步更新，比如

订单编号 的非重复计数:=DISTINCTCOUNT([订单编号])*1.5，数据透视表的数值也会更新。

CALCULATE函数

1- 语法格式

     =CALCULATE 

(自定义的汇总方式, 

 对当前筛选环境的修改)

      CALCULATE( )函数的内部运算逻辑为:先对当前筛选环境进行修改，然后再执行自定义的汇总运算。它的内部计算逻辑与参数的出场顺序是相反的！(SUMIFS)

实际上是先筛选再汇总，比如SUMIFS是先筛选出满足条件的数据，再对数据进行汇总。

       CALCULATE()在执行计算之前，不但能够“识别”其当前所处的透视表筛选环境，而且，还能对其所处的透视表筛选环境进行“修改"。

     数量汇总:=CALCULATE(SUM([数量]))         

2- 当一个DAX度量值公式拖入到数据透视表值区域时，会隐含地在该度量值公式外面，包裹一个看不见的CALCULATE()函数。（作为一个了解）

      带Calculate的度量值：

       订单编号CALC:=CALCULATE(DISTINCTCOUNT([订单编号]))

      不带Calculate的度量值:

       Distince Count of 订单编号:=DISTINCTCOUNT([订单编号])

手动输入度量值“数据汇总:=CALCULATE(SUM('合并3'[数量]))”，回车得到数量汇总。

再点击数据透视表，就可以找到度量值，拖拉其他字段就能进行分类汇总，比如客户姓名。

在PowerPivot输入：

订单编号CALC:=CALCULATE(DISTINCTCOUNT([订单编号]))

Distince Count of 订单编号:=DISTINCTCOUNT([订单编号])

这两个度量值的结果是一样的，都是直接对订单编号做非重复计数，没有对当前的筛选环境进行修改，如果有对当前筛选环境进行修改，那么结果是不一样的。计算完这两个度量值，点击数据透视表，将度量值拖入值区域，Distince Count of 订单编号其实也变成了“Distince Count of 订单编号:=CALCULATE(DISTINCTCOUNT([订单编号]))”

FILTER函数

1- FILTER函数语法：(工作表函数FILTER）

     =FILTER

     (表格或者计算结果为表格的DAX表达式,

      筛选条件)

 “表格或者计算结果为表格的DAX表达式”是指要筛选的表格或者运算结果是表格的DAX表达式，

 “筛选条件”指的是要对FILTER()函数指定表格的每一行进行测试的判断条件。

       FILTER函数为“逐行处理”函数。所谓“逐行处理”是指它能够对其第一参数所指定的表格逐行判断或计算，按行生成一个个中间结果，最后逐行处理函数能够依据这些中间结果再次进行汇总计算。

       FILTER()函数的计算结果是表，为了能看到FILTER()函数的运算结果，我们可以借助其他DAX函数对表格内容进行汇总。

       具体单位的数量:=COUNTROWS(FILTER('合并3','合并3'[单位]="克"))

在excel中输入“=FILTER(合并3,合并3[单位]="克")”，返回结果是一个表格，即在合并3表格中筛选出单位是“克”的数据。

在PowerPivot中输入“单位是克:=FILTER('合并3','合并3'[单位]="克")”返回错误，原因是FILTER返回的是一个表格，不是一个标量值，加上COUNTROWS函数就可以对表格进行汇总。

点击数据-获取和转换数据-现有连接-表格-合并3-打开-新工作表，将合并3上传到前端，右键点击表格-编辑DAX-DAX-输入表达式“EVALUATEFILTER('合并3','合并3'[单位]="克")”，就可以将筛选之后的结果以表格形式加载到前端，EVALUATE可以把筛选结果转换成excel的形式。

从PowerPivot加载到前端的表格，可以将筛选条件修改为其他单位，比如“个”，就可以呈现出单位是“个”的数据。

CALCULATE结合FILTER使用

       CALCULATE()在执行计算之前，不但能够“识别”其当前所处的透视表筛选环境，而且，还能对其所处的透视表筛选环境进行“修改"。

3个度量值比较：

     度量值1:=CALCULATE(SUM('合并3'[原料数量]),'合并3'[单位]="克")

     度量值2:=CALCULATE(SUM('合并3'[原料数量]),FILTER('合并3','合并3'[单位]="克"))

     度量值3:=CALCULATE(SUM('合并3'[原料数量]),FILTER(ALL('合并3'[单位]),'合并3'[单位]="克"))

     说明：在PowerPivot中FILTER(ALL([字段名]),[字段名]="XXXX")的DAX表达式形式可以直接写成:[字段名]="XXXX"。

选择“所定菜品”，“数量汇总”会根据菜品分类汇总，如果把“所定菜品”换成“客户姓名”，“数量汇总”会根据客户姓名分类汇总。这就是“对当前筛选环境的修改”。

上述三个度量值在PowerPivot中的值都是相同的，都是对表中的单位是“克”的原料数量进行汇总。但是在数据透视表它们呈现出的结果是不同的。

度量值1和度量值3的作用是相同的，通过FILTER函数筛选出单位是“克”的原料，用SUM函数汇总，但是ALL函数移除了‘合并3’[单位]的筛选限制。（没有ALL函数其他单位的原料数量是空值，只有“克”有数值，加了ALL之后行标签筛选失效了，区别不出“克”这个单位了，所以所有单位数值都是16450）

这样做可以计算出各个单位的原料数量占单位是“克”原料数量的占比。

SUMX函数

1- 语法格式

        SUMX()返回表中”每一行计算”的表达式之和。

        =SUMX(特定的表格参数,逐行运算的表达式)

        SUMX() 和FILTER()一样，也是逐行处理函数，它能够对第一个参数所指定的表格中每一行逐行处理，每一行的处理规则由SUMX()的第二参数决定。(工作表函数SUMPRODUCT)

2-  用度量值的方式对原料数量进行汇总。

度量值：原料数量汇总:=SUMX('合并3','合并3'[数量]*'合并3'[数量.1])

查看在PowerPivot中书写的度量值。

点击数据-获取和转换数据-现有连接-表格-合并3-打开-新工作表，将合并3上传到前端，右键点击表格-编辑DAX-DAX-输入SQL语句

SELECT
   [MEASURE_NAME] AS[度量值名称],
   [EXPRESSION] AS[度量值表达式],
   [MEASUREGROUP_NAME]AS[度量值所在表]
FROM $SYSTEM.MDSCHEMA_MEASURES
WHERE [MEASURE_AGGREGATOR] = 0

多表分析

文件-关系图视图，可以看到每个表格之间的关系。

比如度量值：销售额汇总:=SUM('订单表'[销售额])

在数据透视表中可以看到多个表。

可以根据不同表格的不同字段分类汇总销售额。