Разделение чисел и текста в одном столбце с помощью Power Query

Электронная таблица Excel со столбцом, содержащим числа и текст? Какой беспорядок!

Делиться

Введение

Это типичный случай для самообслуживаемой бизнес-аналитики с использованием данных из Excel.

Несколько дней назад клиент задал мне следующий вопрос:

У меня есть электронная таблица Excel, в одном столбце которой находятся числа и текст. Я хочу импортировать эту таблицу в Power BI и выполнить анализ чисел в этом столбце.

Как отделить цифры от текста в этом столбце?

Помните, что мне нужен и текст в этом столбце.

Я никогда раньше не оказывался в подобной ситуации, поэтому сначала начал использовать знакомый мне метод.

Я создал фиктивный файл Excel с той же проблемой, который выглядит следующим образом:

Для создания прототипа я сначала загрузил эти данные в базу данных SQL Server, чтобы посмотреть, как решить эту задачу там.

Решение проблемы с помощью 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];

В результате получилась следующая таблица:

Если присмотреться, то видно, что строка 17 распознана как текст.

Это происходит потому, что число содержит пробел.

Я вернусь к этому позже.

Переход на Power Query – попытка использовать IsNaN()

Теперь я загрузил файл Excel в Power Query.

Я определил столбец как «Текст» и приступил к решению этой задачи.

В первой попытке используется функция Number.IsNaN().

Эта функция возвращает true, если значение равно NaN. «NaN» — это заполнитель, указывающий на неприменимость значения, например, из-за деления на ноль.

Я попытался определить, эквивалентен ли текст значению NaN.

Это M-код для вычисляемого столбца:

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

Результат меня удивил:

Как ни странно, в результате программа не может преобразовать число в число.

Полагаю, это происходит потому, что тип данных столбца — текстовый.

Затем я попытался преобразовать столбец в число и применить к результату функцию IsNaN():

Если Number.IsNaN(Number.From([Value])) = false, то Number.From([Value]) иначе null

Теперь числа преобразуются в числа, но текстовые значения приводят к ошибке:

Теперь эта логика применима и к числам.

Однако преобразование не удается для строк, содержащих текст. Это приводит к появлению строк с ошибками.

Попытка использования Value.Is() в Power Query

Давайте попробуем другую функцию: Value.Is()

Эта функция проверяет, совместимо ли значение с заданным типом данных.

Это должно быть эквивалентно функции ISNUMERIC(), показанной выше:

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

К сожалению, эта функция также не вернула ожидаемый результат:

Когда я попробовал тот же подход, что и выше, предварительно преобразовав значение в число, я получил тот же результат, что и раньше:

Поэтому я подозреваю, что функция Value.Is() ожидает числовой тип данных, но это мне кажется нелогичным.

На тот момент у меня не было времени на более глубокие исследования, так как его было очень мало.

Пришло время изменить подход.

Концепция переключения

Теперь я изучил, как выявлять ошибки в Power Query.

Моя идея заключалась в следующем: что если бы я мог перехватить ошибку преобразования и использовать эту информацию?

Я нашел эту страницу с полезной информацией: Ошибки – PowerQuery M | Microsoft Learn

Исходя из этого, я вывел следующее выражение:

try Number.From([Value]))

После добавления вычисляемого столбца с этим выражением я получил следующий результат:

Я был полон оптимизма, так как ошибки не возникло.

Следующим шагом было расширение архива:

Мне не нужны были столбцы «Ошибки» — только столбец «Значение».

Вот результат после расширения:

Обратите внимание, что я переименовал столбцы непосредственно в функции ExpandRecordColumn().
В противном случае у меня получился бы столбец с названием [Значение.1].

Этот результат стал первым, где я не получил никаких ошибок.

Теперь я добавил вычисляемый столбец, чтобы проверить, пуст ли новый столбец. Если да, то исходный столбец «Значение» содержал текст:

если [Числовое значение] = null тогда [Значение] иначе null

Вот результат:

После установки правильных типов данных и удаления исходного столбца «Значение» я получил следующую таблицу:

Обработайте число с помощью пропусков.

Но у нас всё ещё есть 17-я строка, в которой содержалось число с пробелом.

Как я с этим справился?

Самым простым решением было удалить все пустые значения из столбца «Значение»:

Но мне пришлось добавить этот шаг, прежде чем приступать к разделению двух типов значений:

После добавления этого шага строка 17 распознается как число и сохраняется корректно.

Вот данные после загрузки в Power BI:

Но это работало только в том случае, если текстовые значения представляли собой отдельные слова. Это не работало, когда хранились предложения или несколько слов.

Заключение

Это было увлекательное знакомство с тем, как Power Query, или язык M, работает с типами данных.

Я до сих пор не уверен в причинах ошибок.

Но я научился обрабатывать ошибки, или использовать вызов конструкции try и обрабатывать вывод.

Это было очень полезно.

В любом случае, как вы видите на примере исходного значения в строке 17, качество данных имеет первостепенное значение.

У меня есть ещё один клиент, где пользователи из разных стран работают над одним и тем же файлом Excel, используя свои собственные форматы чисел.

Это настоящий кошмар, потому что Excel очень терпим к типам данных. Он принимает всё, даже если столбец отформатирован как число.

В такой ситуации я вынужден заставлять пользователей использовать параметры форматирования Excel, чтобы гарантировать единообразное распознавание чисел.

Без этого у меня нет возможности импортировать эти данные в Power BI без значительных усилий по очистке цифр.

И будьте уверены, пользователи всегда найдут способ испортить цифры в Excel.

Ссылки

Данные создаются с помощью случайных чисел и слов.

Вот справочная информация по языку M: Справочник по языку формул Power Query M – PowerQuery M | Microsoft Learn

Источник: towardsdatascience.com