В наш век озер данных и петабайтных баз данных просто шокирует то, как часто я все еще получаю данные в виде CSV, текстовых файлов и файлов Excel. В то время как современная аналитика сосредоточена на передовых достижениях в области алгоритмов машинного обучения, ежедневная рутинная работа по анализу данных по-прежнему представляет собой ручной процесс поиска, компиляции и обработки разрозненных типов данных.
Для финансового аналитика данные часто поступают в виде электронной таблицы Excel, но так же часто это дамп данных в CSV или запрос в базу данных SQL. Иногда данные расположены в запутанной структуре или не содержат всех необходимых компонентов для анализа. Время, затраченное на очистку этих данных, является бесценной тратой времени для аналитика, однако иногда эта задача воспринимается как неизбежное зло, с которым нужно мириться.
Решение этой распространенной проблемы на самом деле вполне доступно:Excel и Power BI имеют целый набор инструментов преобразования данных, о которых мало кто знает, под названием Get &Transform (ранее известный как Power Query). Встроенные функции извлечения, преобразования и загрузки (ETL) позволяют финансовым аналитикам беспрепятственно связываться со своими источниками данных и быстрее получать ценную информацию.
Когда мы подготавливаем данные для загрузки в Excel или Power BI, нам обычно приходится выполнять некоторые преобразования данных. Вот некоторые примеры манипулирования данными:
На диаграмме ниже мы видим, что Get &Transform выполняет эту утомительную роль предварительной обработки данных перед их загрузкой.
Почему стоит научиться использовать Get &Transform? Ну, когда я смотрю на то, для чего я лично использовал эту функцию, она предлагает мне гибкий набор инструментов для:
Как правило, когда я получаю новые данные, я изучаю их с помощью Get &Transform перед загрузкой в Power Pivot. Это позволяет мне видеть, какие преобразования могут быть необходимы, и быстро выполнять некоторые повороты и группировки данных, чтобы сформулировать основу для анализа. Во многих случаях на этом этапе я обнаруживаю, что мне нужно больше данных или возникают проблемы с данными. Используя платформу на основе Excel, я могу быстро выполнить итерацию с моим источником данных, чтобы найти эти аномалии данных.
В конечном итоге решение остаться в Excel или перенести анализ данных на другую платформу будет зависеть от аудитории, повторяемости и распространения анализа. Если мои клиенты используют только Excel, то я почти всегда буду использовать Get &Transform для загрузки данных, Power Pivot для выполнения анализа и Excel для создания сводных таблиц и диаграмм. Клиенту это покажется безупречным, поскольку все находится в Excel.
Однако, если мой клиент:
Затем я буду использовать Get &Transform исключительно для первоначального исследования данных, а затем перенесу тяжелую работу в R.
В предыдущих версиях Excel Power Query была надстройкой, которую можно было установить, чтобы помочь с функциями ETL. Однако в Excel 2016 и Power BI эти инструменты более тесно интегрированы. В Excel 2016 к ним можно получить доступ через Данные. вкладку, а затем Получить и преобразовать данные. раздел.
В Power BI эта функция доступна на Главной странице. на вкладке Внешние данные. раздел.
В этой статье мои примеры происходят в Power BI, но интерфейс почти идентичен интерфейсу Excel. Я укажу на различия, когда они возникнут, поэтому руководство должно быть понятно обоим типам пользователей.
Чтобы помочь этому руководству, я создал несколько примеров данных о продажах для вымышленного розничного продавца, который продает снаряжение и одежду для активного отдыха. В каждом из этих примеров данные будут создаваться разными способами, чтобы продемонстрировать реалистичные методы создания дампов данных.
В качестве начального примера мы увидим данные, представленные в виде большого дампа данных в файл CSV. Усложняющим фактором является то, что данные представлены несколькими столбцами, представляющими различные хранилища. В идеале мы хотели бы импортировать данные и преобразовать их в более удобный макет.
Ниже приведен скриншот того, как выглядит необработанный CSV:
Почему мы хотим изменить это? Чтобы воспользоваться возможностями отношений, которые возможны в этих приложениях. Мы увидим эту игру в ходе дальнейшего обсуждения.
А пока давайте предположим, что нам нужно видеть данные как структуру «более узкую и высокую», а не «более широкую и короткую». Первый шаг — загрузить CSV; затем мы начнем «разворачивать» данные.
Как видите, окончательная структура данных уже, чем исходные данные, и намного длиннее. Еще один момент заключается в том, что когда мы нажимаем на различные действия, инструмент с правой стороны создает список примененных шагов, используемых для построения запроса. Важно понимать, что это происходит в фоновом режиме, так как это будет рассмотрено позже.
Get &Transform выглядит и ведет себя одинаково между Power BI и Excel по большей части. Однако в Excel после нажатия Закрыть и загрузить , есть одна дополнительная подсказка. На рисунке ниже мы можем переключаться между тем, хотим ли мы загружать данные в:
Кроме того, нам также предоставляется возможность добавлять эти данные в модель данных или нет. . Установка этого флажка загружает данные в таблицу Power Pivot. Если мы собираемся анализировать данные в Power Pivot, я советую выбрать Только создать подключение. а затем убедитесь, что Добавить эти данные в модель данных опция выбрана. Если данные находятся в пределах строки Excel, и мы предпочитаем проводить анализ в Excel, просто выберите Таблица. .
В следующем ролике мы увидим, что причина, по которой мы отформатировали данные так, чтобы они были длинными и узкими, заключается в том, что мы можем анализировать продажи не только по магазинам, но и по регионам и штатам. Для выполнения этой задачи мы импортируем таблицу, которая сопоставляет каждый магазин с регионом и штатом. Ниже мы увидим, что можем быстро создавать отчеты, отображающие продажи по этим разным группам.
Вы можете себе представить, как этот тип возможностей для преобразования данных в Excel или Power BI может эффективно применяться в любом случае, когда у нас есть динамические группы данных, например:
В то время как в этой статье рассматриваются файлы CSV и другие файлы Excel, Get &Transform работает с широким спектром типов данных. После создания запроса его можно со временем обновлять по мере изменения данных.
Чтобы продемонстрировать способность Get &Transform манипулировать строками, я создал еще один набор данных, который имитирует текстовый файл, показывающий бухгалтерские операции из главной книги фирмы (GL).
Заметили, как номер счета и имя появляются в одной строке? В Power BI мы можем легко разбить номер счета и имя на отдельные поля.
В этом видео вы можете видеть, что после того, как я разделил столбец, инструмент догадался, что новая левая часть поля «Учетная запись» должна быть числом, и создал шаг «Измененный тип 1». Поскольку в конечном итоге мы хотим, чтобы это поле было строкой, мы можем удалить шаг вручную из примененных шагов.
Затем мы берем те же данные и создаем план счетов с сопоставлением с категориями счетов.
Зачем нам проходить все эти шаги, чтобы сопоставить несколько номеров счетов? Настоящая главная книга может состоять из сотен или даже тысяч учетных записей. Этот запрос быстрого сопоставления, как мы показали, масштабируется до этого уровня без дополнительной работы.
Get &Transform поддерживает множество различных источников данных. Хотя это и не исчерпывающий список, ниже приведены некоторые примеры:
Текстовый файл Excel Facebook Adobe Analytics Google Analytics Salesforce Azure Redshift Spark SQL Server SAP HANA Teradata Google BigQueryЛично я пробовал только около половины подключений из приведенного выше списка. Каждый из разъемов, которые я использовал, был довольно надежным; Я перешел от необработанных данных к инсайтам без обременительного объема работы. Не менее важно то, что он служит средством проверки между разрозненными источниками данных, гарантируя, что конечные результаты имеют нормализованный уровень контроля качества.
В фоновом режиме Get &Transform генерирует код каждый раз, когда мы нажимаем кнопку в инструменте или делаем выбор. Ниже приведен пример доступа к коду созданного нами запроса сопоставления учетных записей:
В коде используется функциональный язык M, который автоматически генерируется для основных вариантов использования. Однако для более сложной обработки данных мы можем отредактировать и написать собственный код. В большинстве случаев я буду вносить в этот код лишь незначительные изменения. В более сложных преобразованиях я могу написать большую часть кода с нуля для создания временных таблиц или для выполнения более сложных объединений.
Excel имеет тенденцию достигать своих пределов, когда вы пытаетесь экспортировать более миллиона строк. В тех случаях, когда я преобразовал миллионы строк с помощью Get &Transform, единственный способ отправить несгруппированные строки — это утомительные хаки или обходные пути. Я также обнаружил, что запросы Get &Transform могут быть нестабильными при развертывании для нескольких пользователей, особенно если вы используете несколько источников данных и объединений. В таких случаях я всегда буду использовать R для развертывания обработки повторяющихся данных. Наконец, Excel не предназначен для более сложного моделирования данных. Вы можете выполнять линейные регрессии довольно быстро, но помимо этого вам потребуется использовать более строгую платформу.
Сказав все это, я считаю, что большинству моих клиентов больше всего нравится Excel. Excel по-прежнему остается самым важным инструментом в арсенале финансового аналитика. Внедрив функцию «Получить и преобразовать», Excel и Power BI становятся еще более мощными благодаря целому ряду источников данных, которые они могут принимать.