使用 Power Query 将数字和文本分隔在一列中

这是使用 Excel 数据的自助式 BI 的典型案例。

前几天，有客户问了我这样的问题：

我有一张 Excel 工作表，其中一列包含数字和文本。我想将此工作表导入 Power BI 并对该列中的数字进行分析。

如何将该列中的数字与文本分开？

请记住，我还需要该列中的文本。

我从未遇到过这种情况，所以我首先开始使用我所知道的技术。

我创建了一个具有相同问题的虚拟 Excel，如下所示：

为了创建 PoC，我首先将此数据加载到 SQL Server 数据库中，看看如何在那里解决它。

使用SQL解决问题

T-SQL 有两个函数在这种情况下很有帮助：

TRY_CONVERT()

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

ISNUMERIC()

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

基于这些知识，我编写了一个查询将值分成两列。一张带有数字，一张带有文本：

选择 [值]

,TRY_CONVERT(十进制(18, 5), [值])         AS    [数字]

,IIF(ISNUMERIC([值]) = 0, [值], NULL) AS    [文本]

FROM [dbo].[混合值];

结果如下表：

如果仔细观察，您会发现第 17 行被识别为文本。

这是因为数字包含空格。

我稍后会再讨论这个问题。

切换到 Power Query – 尝试 IsNaN()

现在，我将 Excel 加载到 Power Query 中。

我将列定义为文本并开始应对这一挑战。

第一次尝试使用 Number.IsNaN() 函数。

如果值为 NaN，则此函数返回 true。  “NaN”是一个占位符，表示不适用，例如，因为被 0 除。我尝试用这个来确定文本是否等于 NaN。这是计算列的 M 代码：if Number.IsNaN([Value]) = true然后[值]否则为空结果令我惊讶：奇怪的是，结果是它无法将数字转换为数字。结论