1. 从一次数据清洗的“选择困难症”说起

如果你刚开始接触Power BI，或者已经用它做过几个报表，大概率会遇到这样一个场景：面对一份需要处理的数据，你站在Power BI Desktop的界面里，鼠标在“转换数据”和“新建度量值”之间犹豫不决。前者会把你带入Power Query的编辑器，使用M语言进行数据清洗和整形；后者则会打开DAX公式栏，让你开始构建计算逻辑。这个看似简单的选择，背后其实是Power BI两大核心数据处理引擎——Power Query（M语言）和DAX——的分工与协作问题。

我见过不少朋友，包括我自己在早期，都曾在这里“踩坑”。比如，试图用DAX写一个复杂的字符串拆分和清洗逻辑，结果公式冗长且性能堪忧；或者，在Power Query里用M语言构建一个需要动态筛选上下文的复杂比率计算，最后发现根本行不通。这种困惑的根本原因，是对这两种语言的核心定位、能力边界和应用场景不够清晰。

简单来说，你可以把数据准备到分析呈现的整个过程想象成一条流水线。Power Query（M语言）是这条流水线的前端“预处理车间”，它的核心任务是“塑形”——把来自四面八方的、杂乱无章的原材料（原始数据）进行清洗、整合、转换，变成规格统一、干净整洁的“半成品”数据模型。而DAX则是流水线后端的“智能装配与计算中心”，它的核心任务是“计算”——基于已经建好的、关系清晰的数据模型，在用户进行筛选、点击、下钻等交互时，动态地、实时地计算出各种指标（如销售额、增长率、排名等）。

今天，我们就来彻底拆解这对“黄金搭档”。我会结合“RFM分析DAX”、“从文件夹合并Excel并提取首表”等具体场景，帮你理清什么时候该用谁，以及如何让它们高效协作，避免让你的Power BI项目从一开始就走上弯路。

2. 本质差异：M语言与DAX的“出厂设置”与核心使命

要做出正确选择，必须从根上理解它们的设计哲学。这不仅仅是语法不同，而是彻头彻尾的两种范式。

2.1 Power Query M：为“数据整形”而生的声明式函数语言

M语言是Power Query的底层语言。它的设计初衷非常纯粹：描述数据转换的过程。它是一种“声明式”语言，这意味着你更多地是在告诉它“我想要数据变成什么样子”，而不是“一步接一步具体怎么操作”。虽然你写出的步骤是顺序的，但引擎会对其进行优化。

核心特征与工作场景：

1. 行级、静态处理：M语言处理的是查询（Query）加载到数据模型之前的那份静态数据表。它擅长对整列或整行进行操作，比如将一列文本全部转为大写、拆分列、填充空值。它的计算不依赖于报表页面的筛选器上下文，是“一次性”的预处理。
2. 数据源连接与集成：这是M语言的看家本领。无论是从文件夹合并多个Excel/CSV文件（正如热词中提到的场景），还是连接SQL数据库、Web API、SharePoint列表，M语言都能通过直观的图形化界面生成底层M代码，完成复杂的连接和合并操作。
   • 实战示例：从文件夹合并Excel并仅提取每个文件的第一个表这个需求非常典型。在Power Query编辑器中，选择“从文件夹”获取数据后，你会得到一个包含所有文件信息的表。关键步骤在于添加一个自定义列，使用Excel.Workbook([Content], null, true)函数动态读取每个二进制文件内容，然后展开这个自定义列。此时，你会看到每个Excel文件中的所有工作表。要只提取第一个表，你需要筛选[Kind]列为“Sheet”，然后或许再按文件名或其他逻辑确保每个文件只取第一行。这个过程完全由M语言在数据加载阶段完成，结果是一个合并好的、干净的大表，供后续建模使用。
3. 非聚合型复杂转换：需要新增列，且该列的值是通过同一行内其他多个列计算得出的复杂结果时，用M语言更合适。例如，根据“省-市-区”三列组合成一个完整的地址列，或者根据多个条件列使用if...then...else逻辑生成一个新的分类标签。

注意：在Power Query里做的所有转换，都会在数据刷新时重新执行。因此，非常复杂的M脚本可能会影响数据刷新速度。它的目标是产出优质的静态数据模型，而不是响应快速变化的查询。

2.2 DAX：为“动态分析”而生的公式语言

DAX（Data Analysis Expressions）的基因则深深植根于多维数据分析。它脱胎于Excel的公式，但威力远超之。它的核心是在已有的数据模型关系基础上，进行动态、上下文相关的计算。

核心特征与工作场景：

1. 上下文驱动：这是DAX的灵魂，也是最难理解的部分。DAX计算的结果不是固定的，它会随着报表上的切片器、筛选器、行/列标题、视觉对象之间的交叉筛选而动态变化。一个简单的SUM(Sales[Amount])，在“年份”切片器选择2023年时，自动只计算2023年的销售额。这种上下文（筛选上下文）是由报表交互自动创建的。
2. 聚合与时间智能计算：DAX天生为汇总分析而生。求和（SUM）、求平均（AVERAGE）、计数（COUNT/DISTINCTCOUNT）是基础。更强大的是时间智能函数，如TOTALYTD（年初至今累计）、SAMEPERIODLASTYEAR（同期对比）、DATEADD（日期偏移），可以轻松实现复杂的时序分析。
3. 模型层计算：DAX的计算主要作用于数据模型加载之后。它通过三种主要形式存在：
   • 计算列：在数据模型表中新增一列，逐行计算，结果在刷新时固定。但请注意，除非计算逻辑无法在Power Query中实现或依赖于其他表的关系，否则应优先在Power Query中创建列，以获得更好的性能。
   • 度量值：这是DAX的精华。度量值不在数据表中占用空间，只在被视觉对象调用时实时计算。它是动态的、轻量级的，用于表示KPI、比率、排名等。例如，利润率[Profit Margin] = DIVIDE([Total Profit], [Total Sales])就是一个度量值。
   • 计算表：基于现有模型表，通过DAX公式生成一张新表，用于辅助建模（如日期表）。

一个经典误区澄清：很多人觉得DAX只能做简单的加减乘除。实际上，借助CALCULATE这个DAX中最强大的函数，你可以重写筛选上下文，实现极其复杂的逻辑，比如“计算每个产品在它所属品类销售额占比”、“计算新客户的首单金额”等。CALCULATE是实现动态业务逻辑的钥匙。

3. 分工边界图：用场景决定你的工具选择

理论说了很多，我们直接画一条清晰的“三八线”。下面这个表格总结了在常见数据处理与分析任务中，应该如何选择。

一个必须掌握的核心理念：尽可能将数据准备工作前推至Power Query阶段。让数据以最干净、最规整的“星型模型”或“雪花模型”状态进入Power BI。DAX则专注于在这个优质的模型之上，构建灵活、动态的业务计算逻辑。这就像做饭，M语言负责洗菜、切菜、备料（数据准备），DAX负责掌握火候、调味、出锅前的勾芡（数据分析与呈现）。备料工作做得越充分，后面炒菜就越得心应手。

4. 实战串联：以“RFM分析”为例看M与DAX的协作

RFM分析是客户价值分析的一个经典模型，它完美地展示了M语言和DAX如何各司其职，协同工作。假设我们有一张原始的交易明细表，包含客户ID、交易日期、交易金额字段。

4.1 阶段一：Power Query (M语言) 进行数据预处理

我们的目标是为后续的DAX计算准备一个干净、高效的模型。在这个阶段，我们可能要做：

1. 数据清洗：移除金额为0或负数的测试订单、处理日期格式错误、确保客户ID唯一且格式正确。
2. 数据简化（可选但推荐）：如果原始数据非常庞大，可以考虑在Power Query中先进行一些轻度的聚合，以减轻模型压力。例如，可以按客户ID和交易日期对金额进行求和，将单日多笔交易合并为一条。但要注意，不能在这里按客户做最终的RFM聚合，因为R（Recency）需要基于动态的“当前日期”计算，这个逻辑必须留给DAX。
3. 创建日期表：这是构建任何时间智能分析的基础。我们可以在Power Query中利用M语言生成一个覆盖所有交易日期的、结构完整的日期表（包含年、季、月、日、星期等字段），并将其标记为“日期表”。这个表将与交易明细表的交易日期列建立关系。

这个阶段结束后，我们得到的是一个干净的交易事实表和一个日期维度表，它们之间通过日期字段建立了关系。数据模型的雏形已经搭建好了。

4.2 阶段二：DAX 构建动态RFM计算逻辑

现在，我们进入DAX的领域，在报表画布或模型视图中创建度量值。

1. 确定分析快照日期：RFM分析需要一个“当前日期”作为计算基准。通常，我们取数据中最后的交易日期。可以创建一个度量值：[分析截止日] = MAX('交易明细'[交易日期])。
2. 计算R（最近购买时间）：对于每个客户，计算他最后一次交易距离“分析截止日”的天数。

   R值（天数） = VAR CurrentDate = [分析截止日] VAR LastPurchaseDate = CALCULATE(MAX('交易明细'[交易日期]), ALLEXCEPT('交易明细', '交易明细'[客户ID])) RETURN DATEDIFF(LastPurchaseDate, CurrentDate, DAY)

   这个度量值需要放在一个以客户为行的表格视觉对象中才能正确计算每个客户的R值。ALLEXCEPT函数的作用是在计算每个客户的最后购买日期时，清除其他所有筛选器，只保留对客户ID的筛选。
3. 计算F（购买频次）：计算每个客户的总交易次数（按订单数计）。

   F值（交易次数） = COUNTROWS('交易明细')

   同样，这个度量值在客户粒度的上下文中计算。
4. 计算M（购买金额）：计算每个客户的总交易金额。

   M值（总金额） = SUM('交易明细'[交易金额])

5. RFM分箱与客户分群：得到R、F、M三个数值后，我们需要对它们进行分段（例如，按五分位数分为5段）。这可以通过DAX的IF或SWITCH语句，结合PERCENTILEX.INC等函数来实现，生成“高”、“中”、“低”的标签。最终，将三个标签组合，得到如“重要价值客户”、“一般保持客户”等分群结果。
   • 技巧：可以先创建R/F/M的分段度量值，然后创建一个“客户分群”计算列（在客户维度表上），引用这些度量值来静态化每个客户的分群标签。这样可以在不同报表中复用。

整个流程的协作关系：Power Query准备好了“食材”（干净的事实表和日期表），并建立了“厨房”的基本布局（数据模型关系）。DAX则利用这些食材和布局，根据“食客”的实时要求（报表筛选交互），现场烹制出RFM分析这道“菜肴”。如果数据预处理（Power Query阶段）没做好，比如日期格式混乱、存在大量无效数据，那么DAX公式将会写得非常痛苦且容易出错。

5. 性能优化与常见陷阱：让你的选择更具智慧

理解了分工，我们还需要知道如何让它们跑得更快、更稳。

5.1 Power Query (M) 性能要点

1. 尽早筛选，减少行数：在查询步骤中，尽可能早地使用“筛选行”操作，减少后续步骤需要处理的数据量。尤其是在连接大型数据源时，先筛选再合并。
2. 慎用“合并查询”与“追加查询”：合并查询（类似SQL的JOIN）非常消耗资源。确保在合并前，被合并的表已经过充分的筛选和精简。如果可能，尝试在数据源端（如SQL数据库）完成连接操作。
3. 关注“查询折叠”：这是一个高级但至关重要的概念。当你的Power Query操作能被“下推”到数据源（如SQL Server）去执行时，就会发生查询折叠。这能极大提升刷新性能。尽量使用支持折叠的操作（如筛选、投影、简单的聚合），避免使用导致折叠中断的自定义函数或某些复杂转换。你可以通过查看查询设置的“原生查询”来确认是否发生了折叠。

5.2 DAX 性能要点与陷阱

1. 度量值 vs 计算列：这是最重要的性能决策之一。
   • 计算列在数据刷新时计算并物理存储，增加模型大小。仅当该列需要被用于建立关系、作为切片器或行/列标签，且其值静态不变时使用。
   • 度量值是动态计算的，不占存储空间。绝大多数业务计算（总和、比率、对比）都应使用度量值。错误地使用计算列来做聚合计算是常见的性能杀手。
2. 避免在DAX中重复Power Query的工作：不要用DAX去拆分字符串、清洗数据。这违反了分工原则，DAX引擎并不擅长此道，会导致计算性能极差。
3. 理解筛选上下文与行上下文：这是写出高效、正确DAX公式的基础。错误地使用CALCULATE、FILTER等函数，或者混淆上下文，会导致公式返回意外结果或性能低下。例如，在计算列中使用SUM函数而不使用CALCULATE修改上下文，通常会得到整个表的总和，而不是当前行相关的值。
4. 使用变量（VAR）：在复杂的DAX公式中，使用VAR关键字来存储中间计算结果。这不仅能提高公式的可读性，还能避免重复计算，提升性能。

5.3 一个典型陷阱案例：动态标题的实现

热词中提到了“Power BI服务关闭底部工具栏”，这更多是前端设置。但一个相关的需求是：在报表中显示动态标题，例如“截至[最后数据日期]的销售看板”。这个需求需要混合使用M和DAX。

• 错误做法：试图在Power Query中创建一个包含当前日期的表，但这个日期在报表发布后不会自动更新（除非每次刷新数据）。
• 正确协作流程：
  1. 在Power Query中，可以创建一个单行单列的表，用M函数DateTime.LocalNow()或从数据中提取MAX(日期)作为默认值，生成一个“数据更新日期”表。但注意，这只是一个数据加载时刻的静态值。
  2. 更动态的做法是，在DAX中创建一个度量值：[最后数据日期] = “截至 ” & FORMAT(MAX(‘销售表’[日期]), “yyyy年m月d日”) & “ 的销售看板”。
  3. 在报表页面上，插入一个文本框，将其值设置为这个[最后数据日期]度量值。这样，每当报表数据刷新后，这个标题会自动更新为最新的日期。

这个案例再次印证了分工：静态的、一次性的数据准备用M；动态的、随数据变化的文本渲染用DAX。

6. 进阶思考：何时需要打破常规？

绝大多数情况下，遵循“M管准备，DAX管计算”的原则是最优解。但在一些边界场景，你可能需要灵活变通。

1. 在Power Query中调用自定义函数进行复杂迭代：M语言支持递归和自定义函数。对于某些需要行间迭代计算的复杂清洗逻辑（例如，解析有嵌套结构的JSON），在Power Query中完成比在DAX中模拟要高效和直观得多。
2. 使用DAX创建动态计算表：DATATABLE函数或通过SUMMARIZE等函数生成的表，可以作为中间表辅助复杂度量值的计算。这些表不存储在模型里，只在公式求值时动态生成。
3. 性能权衡：有时，一个极其复杂的、基于多条件的DAX度量值运行非常缓慢。如果该计算的结果是静态的（不随前端筛选变化），可以考虑“降级”处理——在Power Query刷新时，通过添加引用查询或自定义列，利用M语言预先计算好这个结果，作为静态列加载到模型。这用刷新时间的增长，换取了报表交互时的极致流畅。这是一个典型的“空间换时间”的决策。

最终，选择M语言还是DAX，不是一个非此即彼的单选题，而是一个基于数据流阶段和计算动态性的连续决策。你的目标应该是构建一个清晰的数据处理管道：让Power Query成为可靠、高效的数据入口和整形层；让DAX成为灵活、强大的模型计算与交互分析层。当你下次再面对那个选择时，不妨先问自己两个问题：“这个操作是为了让数据本身变得更规整吗？”（是，则用M）“这个数字需要随着用户点击报表而改变吗？”（是，则用DAX）。把握住这两个核心问题，你就能在Power BI的世界里游刃有余。