Электронная таблица 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
