Power Query实战：一列混合数据中如何高效分离数字与文本

在自助式BI（商业智能）工作中，处理Excel数据时常常会遇到一种典型情况：数字和文本混合存储在同一个列中。

近期，一位客户提出了一个具体需求：

有一个Excel表格，其中某一列同时包含了数字和文本。需要将此表格导入Power BI进行分析，并希望将该列中的数字单独分离出来进行计算。

关键点在于，分离后仍然需要保留该列中的文本信息。

针对这一场景，首先可以尝试在SQL环境中寻找解决方案。

为此，创建了一个包含类似混合数据的示例Excel文件，其内容如下：

图1 – Excel中的示例数据

为了验证解决方案，首先将数据加载到SQL Server数据库中，尝试在SQL层面解决问题。

使用SQL解决问题

T-SQL中有两个函数在此类场景中非常有用：

• TRY_CONVERT()

  • 此函数尝试将值转换为目标数据类型。如果转换失败，则返回NULL。

• ISNUMERIC()

  • 检查一个值是否为数值。如果是，则返回1，否则返回0。

基于这些函数，可以编写查询将值分离到两个列中：一个存放数字，另一个存放文本。

SELECT [Values]
            ,TRY_CONVERT(decimal(18, 5), [Values])         AS    [Number]
            ,IIF(ISNUMERIC([Values]) = 0, [Values], NULL) AS    [Text]
  FROM [dbo].[MixedValues];

执行查询后得到如下结果：

图2 – 使用T-SQL查询分离数字与文本的结果

仔细观察会发现，第17行被识别为文本。这是因为该数字中包含了一个空格。这个问题稍后会再讨论。

转向Power Query – 尝试IsNaN()函数

接下来，将Excel数据加载到Power Query中。首先将列定义为文本类型，然后开始处理这个挑战。

第一次尝试使用了Number.IsNaN()函数。该函数在值为“NaN”（非数字，例如由除零运算产生）时返回true。尝试用它来判断文本是否等同于NaN。

用于计算列的M代码如下：

if Number.IsNaN([Value]) = true
then [Value]
else null

结果出人意料：

图3 – 使用Number.IsNaN()的结果。为何无法识别数字？

奇怪的是，结果显示无法将数字转换为数字。推测这可能是因为列的数据类型是文本所致。

于是，尝试先将列转换为数字，再对结果应用IsNaN()函数：

if Number.IsNaN(Number.From([Value])) = false
then Number.From([Value])
else null

现在，数字被成功转换，但文本值却导致了错误：

图4 – 对转换后的值应用IsNaN()，数字被返回，但文本出现错误

此时逻辑对数字有效，但对包含文本的行，转换失败并产生错误。

尝试Power Query中的Value.Is()函数

接下来尝试另一个函数：Value.Is()。该函数检查值是否与某个数据类型兼容，理论上应等效于上述的ISNUMERIC()函数。

if Value.Is([Value], Number.Type) = true
then Number.From([Value])
else null

遗憾的是，这个函数也未能返回预期结果：

图5 – 尝试Value.Is()函数的结果

当采用与之前相同的方法，即先将值转换为数字时，得到了与之前相同的结果：

图6 – 先尝试将值转换为数字时出现的错误

因此，推测Value.Is()函数期望的是数字数据类型，但这在逻辑上似乎说不通。由于时间有限，当时未能进行更深入的研究，需要转换思路。

转换思路

接下来探索如何在Power Query中捕获错误。思路是：能否捕获转换错误并利用这一信息？

在PowerQuery M 错误处理文档中找到了有用信息，并推导出以下表达式：

try Number.From([Value]))

添加使用此表达式的计算列后，得到如下结果：

图7 – 使用try表达式后的结果

结果没有出现错误，这令人感到乐观。下一步是展开记录（Records）：

图8 – 展开try调用输出的记录中的Value

不需要Error列，只需要Value列。展开记录后的结果如下：

图9 – 展开记录后的结果

注意，在ExpandRecordColumn()函数中直接重命名了列，否则会得到一个名为[Value.1]的列。这是第一个没有出现任何错误的结果。

现在，添加一个计算列来检查新列是否为空。如果为空，则说明原始Value列包含文本：

if [Numeric Value] = null then [Value] else null

结果如下：

图10 – 成功将一列中的数值和文本值分离的结果

设置正确的数据类型并移除原始的Value列后，得到最终表格：

图11 – 清理后的最终结果

处理带空格的数字

但第17行的问题仍然存在，该行数字中包含空格。如何处理？

最直接的方法是从Value列中移除所有空格：

图12 – 添加“替换值”步骤以移除数据中的空格

但必须在开始分离两种值类型的步骤之前添加此步骤：

图13 – 在正确位置添加“替换值”步骤

添加此步骤后，第17行被正确识别为数字并存储。加载到Power BI后的数据如下：

图14 – 加载到Power BI后的整洁数据

但这种方法仅适用于文本值为单个单词的情况。如果存储的是句子或多个单词，则无法正常工作。

结论

这次探索深入了解了Power Query及其M语言如何处理数据类型。虽然对于某些错误的确切原因仍不确定，但掌握了如何通过try调用来处理错误并利用其输出，这非常有帮助。

从第17行的原始值可以看出，数据质量至关重要。在实际工作中，例如当来自不同国家的用户使用各自的数字格式在同一Excel文件中协作时，情况会变得复杂。Excel对数据类型高度宽容，即使列被格式化为数字，它也可能接受各种输入，这给后续的数据导入和分析带来了巨大挑战。

在这种情况下，必须引导用户使用Excel的格式设置选项，以确保数字被一致地识别。否则，将数据导入Power BI时就需要投入大量精力进行清理。可以预见，用户总能找到各种方式在Excel中“制造”数字相关的问题。

参考资料

文中使用的数据由随机数字和单词生成。