在自助式BI（商业智能）工作中，处理Excel数据时常常会遇到一种典型情况：数字和文本混合存储在同一个列中。
近期，一位客户提出了一个具体需求：
有一个Excel表格，其中某一列同时包含了数字和文本。需要将此表格导入Power BI进行分析，并希望将该列中的数字单独分离出来进行计算。
关键点在于，分离后仍然需要保留该列中的文本信息。
针对这一场景，首先可以尝试在SQL环境中寻找解决方案。
为此，创建了一个包含类似混合数据的示例Excel文件，其内容如下：
图1 – Excel中的示例数据
为了验证解决方案，首先将数据加载到SQL Server数据库中，尝试在SQL层面解决问题。
使用SQL解决问题
T-SQL中有两个函数在此类场景中非常有用：
-
- 此函数尝试将值转换为目标数据类型。如果转换失败，则返回NULL。
-
- 检查一个值是否为数值。如果是，则返回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中“制造”数字相关的问题。
参考资料
文中使用的数据由随机数字和单词生成。
