Мощные запросы в этой книге могут быть несовместимы с текущей версией Excel

Обновлено: 21.11.2024

Одной из самых мощных функций Power BI и Excel является поддержка геопространственных визуализаций. В Excel мы можем использовать визуализацию карты в Power View или напрямую использовать Power Map. В Power BI, как вы знаете, есть две встроенные визуализации, поддерживающие данные о географических координатах: Карта и Заполненная карта. Они прекрасно работают, если у вас достаточно данных, поддерживаемых Bing Maps. Но есть некоторые проблемы с визуализацией карты как в Power BI, так и в Excel. В этом посте я расскажу о некоторых проблемах, с которыми столкнулся сам, и предоставлю решения для этих проблем. Поскольку визуализации «Заполненная карта» и «Карта» в Power BI очень похожи, в этой статье я сосредоточусь на визуализации «Карта». Я не собираюсь подробно объяснять Power View и Power Map, поэтому в этой статье я сосредоточусь на Power BI больше, чем на двух других.

Чтобы поэкспериментировать со всем, что я объясняю в этом посте, вам понадобятся:

Новый образец SQL Server, WideWorldImportersDW (WWI). Вы можете скачать его здесь
Последняя версия Power BI Desktop (текущая версия — 2.35.4399.381, 64-разрядная версия (май 2016 г.))
Excel 2016 или Excel 2013

Если вы используете Excel 2016, вам необходимо включить Power View.

Проверьте это, если хотите узнать больше о функциях бизнес-аналитики в Excel 2016.

Открыть Power BI Desktop
Получить данные из базы данных SQL Server
Выберите Fact.Sales и Dimension.City, затем загрузите данные.

Неверные города в Power BI

Разверните таблицу "Город измерений".
Выберите столбец «Город», затем измените его категорию данных на «Город» (категория данных находится на вкладке «Моделирование» на ленте)

Поместите изображение карты на страницу
Добавьте «Город» в поле «Местоположение».
Укажите «Всего без налогов» в поле «Размер».

Как вы видите, продажи распределены по разным странам, но это не совсем так.

Поместите слайсер на страницу, а затем поместите в него слово "Страна".
Нажмите «США», чтобы отфильтровать карту.

Ой! Это не тихо прямо. Что произошло, так это то, что Bing Map Engine путается с названиями городов, поэтому он показывает город с таким же названием за пределами США, точно так же, как Нью-Плимут, который является городом в Новой Зеландии, но Нью-Плимут, который у нас есть в нашем источнике данных — это New Plymouth из Айдахо в США.

Устранение проблемы с неверными городами в Power BI

Решение 1. Объединение страны и города в Power BI

Мы можем легко решить эту проблему, добавив вычисляемый столбец в таблицу и соединив столбцы «Страна» и «Город», чтобы механизм карт Bing нашел правильные города. Мы можем сделать это с помощью Power Query или написания выражений DAX. Я объясню решение DAX и оставлю вам сценарий Power Query.

Страна, город = ‘Город измерения’[Страна] & “, “ & ‘Город измерения’[Город]

Хм! Теперь это выглядит намного лучше. Переключитесь в режим просмотра «Данные», чтобы увидеть, как выглядит новый столбец.

Решение 2. Подготовка местоположения в Power BI

Мы можем использовать столбец «Местоположение» на карте, чтобы получить более точные точки. Глядя на столбец «Местоположение», мы быстро видим, что он содержит информацию о широте и долготе, что нам и нужно. Нам нужно только удалить "POINTS( " и ")" из строки, а затем заменить пробел запятой, чтобы получить данные о местоположении на основе широты и долготы из столбца "Местоположение".

Введите «POINT («» в «Значение для поиска» и оставьте поле «Replace With» пустым. Это удалит «POINT («» из текста

Ответ: да, можем. Вы можете добавить новый столбец и добавить приведенное ниже выражение, которое выполняет все три вышеуказанных шага всего за один шаг:

Ой! Что не так сейчас? Местоположение предназначено для более точного отображения точек на карте, но почему оно показывает большинство мест в Антарктиде? Серьезно?

Вот когда появится следующая проблема.

Хорошо, продолжайте читать, чтобы узнать, почему Карта показывает неправильные данные.

Проблема Power Query при преобразовании типа данных SQL Server «Geography» в текст Power Query

Давайте рассмотрим весь процесс, через который мы прошли, чтобы выяснить, в чем проблема. Мы получили данные из таблицы Dimension.City из SQL Server, верно? Нажмите «Изменить запросы» и еще раз просмотрите столбец «Местоположение».

Нажмите «Навигация» на панели «Настройки запроса», чтобы увидеть, что у нас было изначально в столбце «Местоположение», прежде чем выполнять какие-либо преобразования.

Давайте также взглянем на Dimension.City и посмотрим, что на самом деле есть в исходной таблице.

Откройте SQL Server Management Studio (SSMS) и подключитесь к экземпляру SQL Server, на котором размещена база данных WideWorldImportersDW.
Запрос Dimension.City

Тип данных столбца «Местоположение» в SQL Server — «география», что означает, что мы можем легко получить широту и долготу из столбца местоположения в SSMS.Теперь выполните следующий запрос, чтобы получить широту и долготу из столбца Location:

ВЫБЕРИТЕ [Ключ города],

[Местоположение] . Широта AS Широта,

[Местоположение] . Длинная долгота AS

ИЗ измерения . Город

Не удалось найти свойство или поле «lat» для типа «Microsoft.SqlServer.Types.SqlGeography» в сборке «Microsoft.SqlServer.Types». ”

Чтобы узнать больше о типе данных geography в SQL Server, перейдите по ссылкам ниже:

Хорошо, давайте параллельно посмотрим на SQL-запрос и результат Power Query.

Ну, теперь мы раскрыли проблему. По какой-то причине Power Query ошибочно преобразовал географический тип данных SQL Server в текстовый. Шаблон преобразования должен быть «POINT (широта и долгота)», а не «POINT (долгота, широта)». Это похоже на ошибку. Я уже сообщил об этом как об ошибке команде Power BI. Если вы также хотите сообщить об этом, нажмите здесь.

Но что нам делать, пока команда Power BI не исправит проблему? Что ж, мы можем преодолеть это двумя способами.

Решение: разделите столбец «Местоположение» на широту и долготу

Мы уже прошли более половины пути в предыдущем разделе, когда преобразовали столбец «местоположение» из «POINT (-78.651695 42.1083969)» в «-78.651695,42.1083969». Поэтому нам буквально нужно разделить столбец «Местоположение» на широту и долготу.

Переименуйте «Местоположение.1» в «Долгота» и «Местоположение.2» в «Широта».

Нажмите "Закрыть" и "Применить".
Когда мы разделяем столбец "Местоположение" на два новых столбца, карта ломается, нажмите "Исправить"

Изменить категорию данных для столбцов "Широта" и "Долгота".
Установите для свойства "Суммирование по умолчанию" столбцов "Широта" и "Долгота" значение "Не суммировать"
Нажмите на карту
Перетащите столбцы "Широта" и "Долгота" на карту.

Проблема устранена.

Проблема Power Query с поддержкой типа CLR «Microsoft.SqlServer.Types.SqlGeography»

Эта проблема возникает, когда вы хотите импортировать данные из запроса SQL, который запрашивает столбец типа geography. Иногда вам нужно получить данные из SQL Server, выполнив операторы SQL. Например, в нашей тестовой базе данных посмотрите на таблицу «Dimension.City». Столбцы «Действителен с» и «Действителен до» представляют медленно изменяющееся измерение, что означает, что не все строки в Dimension.City действительны. Чтобы поддерживать оптимальный размер модели Power BI, рекомендуется не импортировать нужные данные. Это также улучшает производительность нашей модели. Поэтому я заинтересован в импорте только допустимых строк из таблицы Dimension.City, которые представляют собой строки со значением «Действительно для», равным «9999/12/31».

ИЗ измерения . Город

ГДЕ Год ([Действителен до]) = 9999

Мы получили ошибку «DataSource.Error: мы не поддерживаем тип CLR «Microsoft.SqlServer.Types.SqlGeography». Хм, выглядит не красиво. Что ж, давайте двигаться дальше и исправить и эту проблему.

Решение. Получите свойства «Широта» и «Долгота» из «Местоположения» с помощью T-SQL

Мы можем легко решить эту проблему, немного изменив наш T-SQL. Ранее в этой статье я объяснил, что мы можем получить «широту» и «долготу» из типа данных geography в SQL Server. Мы используем ту же технику, чтобы решить эту проблему. Все, что вам нужно сделать, это вызвать свойства «Широта» и «Долгота» «Местоположения». Следовательно, оператор SQL будет примерно таким:

ВЫБЕРИТЕ [Ключ города],

Местоположение. Широта AS Широта,

Местоположение. Длинная долгота AS,

CONCAT (Расположение . Широта

[Последнее зарегистрированное население],

ИЗ измерения . Город

ГДЕ ( ГОД ( [Действителен до] ) = 9999 )

Чтобы заменить старый оператор SQL новым, просто нажмите значок шестеренки ( ) справа от шага «Источник» на панели «Настройки запроса».

Нажмите «ОК», затем нажмите «Закрыть и применить», чтобы получить правильные результаты без ошибок. Единственная разница в результирующем наборе состоит в том, что теперь у нас есть еще два столбца, показывающих широту и долготу. Мы также объединили широту и долготу для поддержки «Местоположения».

В следующем разделе я рассмотрю те же проблемы в Power View и Power Map.

Существуют различные способы подготовки данных для Power View и Power Map в Excel 2016.

Подключение к SQL Server, импорт и преобразование данных с помощью Power Query, а затем загрузка результатов либо в листы Excel, либо в модель данных Power Pivot

Чтобы сделать эту часть максимально простой, я объясню, как загружать данные непосредственно в Power Pivot.

Примечание. Если вы используете Excel 2013, не беспокойтесь, так как он не сильно отличается от Excel 2016. Вам просто нужно импортировать данные с помощью вкладки Power Pivot на ленте

Неверные города в Power View и Power Map

То же самое происходит в Power View и Power Map. Когда вы наносите «Город» на карту, она показывает неправильные города. Это тривиально, так как все эти инструменты используют движок Bing Map для визуализации картографических данных.

Вот что мы получаем в Power View:

Снимок экрана ниже сделан из Power Map:

Решение: объединение страны и города в Power Pivot

Мы объединили страну и город в Power BI, написав очень простое выражение DAX. Этот принцип применим к Power Pivot.

Вот что мы получаем в Power View и Power Map после добавления нового вычисляемого столбца, который объединяет столбцы Country и City Power Pivot.

Результаты Power View:

Результаты Power Map:

Power Pivot игнорирует столбцы с типом данных geography

Подобно тому, с чем мы столкнулись в Power BI, когда вы хотели импортировать результаты SQL-запроса в Power BI, если мы хотим сделать то же самое в Power Pivot, мы увидим, что Power Pivot просто игнорирует столбец «Расположение», когда мы записываем следующий запрос в Power Pivot:

ИЗ измерения . Город

ГДЕ Год ([Действителен до]) = 9999

Вот что мы получаем в Power Pivot:

Как видите, столбца «Местоположение» нет.

И снова решение заключается в получении свойств «Широта» и «Долгота» из «Местоположения» с помощью T-SQL. Поэтому нажмите «Свойства таблицы» на вкладке «Дизайн» на ленте в Power Pivot и измените инструкцию SQL, как показано ниже:

Если вы работаете над проектом, связанным с геопространственными данными, а исходной базой данных является SQL Server, помните о несовместимости Power BI, Power Pivot и Power Query с географическим типом данных SQL Server. Я, вероятно, получу неправильные города в визуализациях карт Power BI, Power View или Power Map, если вы не укажете, какой именно город вам нужен.

Если вы впервые столкнетесь с описанными выше проблемами, не зная, как их исправить, это может быть раздражающим и дорогостоящим. Поэтому я надеюсь, что Microsoft исправит подобные проблемы в следующих выпусках/обновлениях Power BI и Excel 2016.

Если вы столкнулись с какими-либо другими проблемами, было бы здорово, если бы вы поделились ими с нами. Так почему бы не написать свою историю в разделе комментариев ниже?

Важно знать, какую версию Excel вы используете

С 1985 года по настоящее время существовало 30 различных версий Excel. Сейчас у большинства пользователей Excel 2016, 2019, 2021 или 365.

Каждая версия Excel значительно отличалась от предыдущей, и версии для Mac и Windows по-прежнему сильно различаются. Если вам нужно изучить Excel, мы настоятельно рекомендуем вам изучить Excel 365, а не одну из устаревших версий. В этой статье вы узнаете почему.30

Содержание

Excel 365

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

Поскольку бизнес-пользователи не хотят постоянных изменений (и нуждаются в надежных надежных версиях), Microsoft представила концепцию «каналов обновлений» для Office 365. Крупные корпоративные пользователи могут подписаться на шестимесячный канал обновлений. Это обеспечило надежную и проверенную «полугодовую» версию в январе и июле каждого года. Интеллектуальный метод поддерживает постоянно меняющийся характер Office 365, публикуя новую версию наших книг каждый раз, когда добавляются важные новые функции. На момент написания этой статьи (в феврале 2022 г.) мы недавно опубликовали шестое издание, посвященное текущему обновлению за январь 2022 г. Smart Method — единственный издатель, который делает это, чтобы наши книги всегда были в актуальном состоянии.

С 2019 г. в Excel 365 было добавлено несколько замечательных функций, но ничто не сравнится с введением функций динамических массивов и динамических массивов в выпуске за июль 2020 г. Это не тривиальное изменение. Механизм Excel пришлось полностью переработать, чтобы распознавать новую концепцию динамического массива. В 2021 году были представлены автоматический анализ данных и запросы на естественном языке (функции, которые не были включены в выпуск Excel 2021). Связанные типы данных также были значительно расширены (опять же функция только для 365). Мы также нашли несколько отличных приложений для пользовательских типов данных (еще одна функция, представленная совсем недавно) и (в книгах 2021 и 365 Expert Skills) поделились нашей реализацией многоязычных форм и отчетов, которые теперь можно очень легко создавать с помощью этого нового особенность.

В Excel 365 огромное количество старых функций Excel теперь могут работать совершенно по-новому, а некоторые старые любимые функции заменены современными функциями, поддерживающими работу с массивами. Например, функция ВПР была заменена на КСПР (современная функция массива, более универсальная, простая в использовании и более мощная).

Новая возможность динамического массива вывела Excel 365 в другую лигу по сравнению с Excel 2019. Динамические массивы принципиально несовместимы с Excel 2019 и приведут к тому, что современные книги (созданные с помощью Excel 365) не будут работать должным образом в устаревших версиях. В Excel 2021 этой проблемы нет, поскольку он поддерживает динамические массивы.

Excel 365 — это последняя, лучшая и самая мощная версия Excel, которую вы можете использовать. Она доступна по очень скромной ежемесячной подписке.

Использование Power Query в Microsoft Excel открывает для менеджеров бесценные возможности бизнес-аналитики, о чем мы сегодня и поговорим. Но самое главное: что такое Power Query и где к нему можно получить доступ?

Excel Power Query позволяет пользователям подключаться к множеству баз данных, от CSV-файла до SQL Server или Oracle, или даже к таблице на веб-странице. Лучшая часть? Вам не нужны технические знания о том, как подключиться к источнику данных, и подключение можно установить с помощью нескольких простых щелчков прямо в Excel. Вам также не нужны какие-либо знания о том, как написать запрос к данным, поскольку все это делается с помощью интерфейса Power Query. Другими словами, это чрезвычайно мощная функция.

Вы можете получить доступ к Power Query на вкладке "Данные" на ленте Excel.

Как я могу использовать Power Query в качестве менеджера?

Прежде всего, вы можете извлекать данные из многих других источников, таких как веб-страница, база данных (например, SQL Server или Oracle) или даже просто из другой электронной таблицы Excel.
Вы можете преобразовать эти данные, объединив источники, удалив ненужные столбцы и добавив вычисляемые столбцы, пока не получите результаты в нужном формате.
Затем вы можете представить эти результаты в виде диаграмм или сводной таблицы, чтобы выделить важные моменты для будущего обсуждения.
Автоматизация отчетов — это еще одна функция, которую вы можете выполнять с помощью Power Query. Хотите увидеть его в действии? Вот руководство о том, как создать автоматизированную панель мониторинга времени в Excel.

Прежде чем мы начнем, некоторые предварительные условия для этих хаков.

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

5 приемов бизнес-аналитики с использованием Excel Power Query

1. Поиск несопоставленных элементов между двумя источниками данных

Примером этого является наличие двух источников данных, один из которых поддерживается локально, а другой — централизованно. Локальная может быть просто электронной таблицей Excel, а центральная может быть базой данных SQL Server. Они могут содержать, например, данные о продажах, но два источника данных не согласуются друг с другом. Power Query позволяет пользователю быстро находить недостающие элементы и разрешать любые разногласия, очевидно, экономя один из ваших самых ценных ресурсов — время.

В примере данных данные о продажах (1) отличаются от данных о продажах (2). Некоторые элементы совпадают, но в каждом источнике данных есть такие, которые не совпадают с другими. В этом случае несопоставленные элементы легко увидеть, потому что выборка данных очень мала, но на работе вы, скорее всего, будете работать с гораздо большим набором данных.

Если каждый источник содержит огромное количество строк, будет сложно найти отдельные элементы в одном источнике, но не найти их в другом. Вот тут и приходит на помощь Power Query. Используя Power Query, вы можете быстро найти несоответствующие элементы.

Выберите «Получить данные» в крайнем левом углу ленты, а затем нажмите «Объединить запросы». Выберите Объединить, и появится всплывающее окно, позволяющее выбрать два источника данных для сравнения.

Выберите «Данные о продажах» (1) в раскрывающемся списке для первой таблицы и «Данные о продажах» (2) для второй таблицы.

Нажмите на столбец "Продукт" в каждой таблице, чтобы показать, что он будет использоваться для объединения двух таблиц. Они будут выделены.

В раскрывающемся списке «Тип присоединения» отображается ряд параметров. Выберите «Полный внешний». Теперь ваш экран должен выглядеть так:

Нажмите OK, и запрос будет создан.

Результат запроса нужно будет расширить — нажмите на значок стрелки справа от заголовка данных о продажах (2), чтобы увидеть этот экран.

Нажмите OK , и это покажет полную информацию о результате запроса. Там, где строки не совпадают, отображаются нулевые значения.

Нажмите "ОК", а затем выберите "Закрыть и загрузить" в крайнем левом углу ленты.

Данные будут загружены на новый лист, а нулевые значения будут отображаться как пустые, например ячейки A4, D6:

Здесь показано сравнение двух таблиц: "Данные о продажах" (1) и "Данные о продажах" (2). У них одинаковые имена столбцов «Продукт» и «Продажи».

Вы видите, что плитка и гипсокартон существуют в данных о продажах (2), но не в данных о продажах (1). И наоборот, блоки Brick существуют в данных о продажах (1), но отсутствуют в данных о продажах (2).

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

2. Объедините более двух источников данных, чтобы получить один результат

В реляционной базе данных обычно данные хранятся в нескольких разных таблицах. Например, одна таблица может содержать информацию о клиенте, другая таблица может содержать заказы, размещенные клиентом, а еще одна таблица может содержать сведения о фактически заказанных продуктах. Или, возможно, вы хотели бы объединить данные отслеживания рабочего времени сотрудников и табелей учета рабочего времени с информацией из других источников. В любом случае вам может помочь power query.

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

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

Ответ заключается в создании другого запроса на основе Merge1 и данных о ценах.

Выберите Merge1 для первой таблицы и Price Data для второй.

Нажмите «Данные о продажах» (2). «Продукт в объединении 1» и «Продукт в данных о ценах», чтобы указать столбцы, которые необходимо объединить. Ваш экран будет выглядеть так:

В качестве типа соединения выберите Full Outer.

Нажмите OK, и отобразится результат запроса.

Разверните столбцы данных о ценах, нажав на значок стрелки в заголовке (проверьте предыдущий совет, если вы не уверены в этом).

Выберите «Закрыть и загрузить» в крайнем левом углу ленты, и будет создана новая таблица с результатами из трех разных источников данных:

Обратите внимание, что блоки Bricks отображаются в данных о ценах как потерянные, поскольку мы объединились в данных о продажах (2). Продукт и блоки отсутствуют в данных о продажах (2).

Новый запрос теперь автоматически называется Merge2, но это имя можно изменить

3. Создание вычисляемого столбца

В окончательных выходных данных Power Query вы можете выполнить расчет на основе определенных полей в окончательных выходных данных. Например, у вас может быть количество проданных продуктов и цена за каждый продукт. Однако нет ничего, показывающего общую стоимость того, что было продано, что полезно для отчетности перед высшим руководством. Здесь можно использовать вычисляемый столбец.

К приведенному выше результату вы можете добавить столбец, показывающий стоимость продаж в данных о продажах (2).

На панели "Запросы и подключения" (в правой части экрана) щелкните правой кнопкой мыши "Объединить2" и выберите "Изменить".

Появится новое окно с вашим запросом и новой лентой.

Выберите вкладку ленты "Добавить столбец", а затем нажмите "Пользовательский столбец" в разделе "Общие" на ленте.

Появится экран для описания вашего расчета. Имя столбца по умолчанию — Пользовательское, но вы можете указать новое имя.

Введите формулу в поле "Формула пользовательского столбца":

Это можно сделать, дважды щелкнув список доступных столбцов.

Нажмите OK, и новый запрос будет показан с дополнительным вычисляемым столбцом

Выберите вкладку "Главная" и нажмите "Закрыть и загрузить". Теперь ваш отредактированный запрос будет отображаться на исходном листе:

4. Используйте Group By для суммирования запроса

Power Query по умолчанию создает уникальную строку для каждой строки запроса, хотя это может быть слишком подробным для целей отчетности. Если вы хотите увидеть, сколько экземпляров определенного продукта было продано, то простой запрос, показывающий каждый экземпляр продукта, будет не очень полезен. Вы можете использовать Группировать по, чтобы суммировать по продуктам, чтобы получить общее количество проданных товаров для каждого продукта.

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

Возможно, вы захотите просмотреть общие значения по продуктам в своем запросе.

Для этого щелкните правой кнопкой мыши таблицу "Сведения о продукте" на панели "Запросы и подключения" в правой части экрана. Выберите «Редактировать», и появится экран редактирования.

Нажмите на заголовок столбца «Продукт», если он еще не выделен — это столбец, по которому вы собираетесь группировать.

Выберите «Группировать по» на ленте (в разделе «Преобразование»), и появится окно «Группировать по».

Группировать по будет отображаться как продукт. Введите «Всего» в поле имени нового столбца и измените «Операция» на «Сумма». Измените имя столбца на «Продажи» (это столбец для суммирования).

Нажмите "ОК", и ваш суммарный запрос будет показан. Выберите «Закрыть и загрузить» на ленте, и запрос появится на новом листе с итоговыми данными для каждого продукта.

5. Быстрая загрузка данных

При импорте данных из других источников, таких как базы данных, это может быть медленным, особенно при наличии большого количества записей. Чтобы ускорить это, щелкните правой кнопкой мыши источник данных, отображаемый на панели "Запросы и подключения" в правой части экрана.

Выберите Свойства, и появится окно свойств.

Установите флажок «Включить быструю загрузку данных».

Excel Power Query для умного управления бизнесом

Я надеюсь, что эти бизнес-лайфхаки с использованием Power Query в Excel оказались полезными — при правильном использовании они могут сэкономить ваше время и ресурсы, а также помочь получить ценную информацию, которая поможет улучшить отчетность и принять решения в управлении. Если у вас есть какие-либо вопросы или комментарии, дайте нам знать, оставив комментарий ниже.

Как всегда, если у вас есть идеи для нашей серии статей "Быстрые выигрыши в электронных таблицах для менеджеров" (где мы уже писали об автоматизации анализа использования мобильных данных, обзорах ежегодных компенсаций и т. д.), мы будем рады услышать от вас. ты тоже!

Power Query — это надстройка Excel, которая позволяет пользователям импортировать, очищать и консолидировать данные. Вы можете использовать Power Query, когда:

Обработка больших объемов данных (где Excel ограничен одним миллионом строк)
Импорт данных из разных источников (например, Excel, CSV, SharePoint, SQL и Salesforce)
Управление, очистка и объединение нескольких таблиц, файлов и даже папок!

Power Query — это удобный и мощный инструмент даже для новичков, поскольку для начала работы вам не нужен опыт программирования. Более того, он не изменяет исходный источник данных. Результат подобен моментальному снимку измененных данных, перенесенных в Excel. Что в этом хорошего? Что ж, поскольку исходные данные не импортируются напрямую, это не утяжеляет ваш файл Excel (т.е. не замедляет работу вашего компьютера!).

Подводя итог, можно сказать, что Power Query — это инструмент анализа данных, который делает процессы ETL (Extract-Transform-Load) более простыми и эффективными.

2. В чем разница между Power Query и Power Pivot?

Power Pivot часто ассоциируется с Power Query, поскольку это надстройка Microsoft Office, упрощающая анализ данных.Интересно, однако, что эти два инструмента могут работать в тандеме друг с другом! Power Pivot — это просто следующий этап преобразования данных: он позволяет пользователям создавать модели данных из таблиц, предварительно очищенных с помощью Power Query.

Несмотря на то, что Power Pivot похож, он в большей степени используется для анализа данных, вычислений, связанных со временем, и генерации ссылок. Проще говоря:

Power Query позволяет автоматически объединять и повторно обрабатывать файлы (т. е. стандартизировать процессы отчетности или объединять несколько файлов одного формата).
Power Pivot позволяет анализировать базы данных из нескольких миллионов строк, извлекая сводную информацию в соответствии с правилами, установленными вашей компанией.

3. В чем разница между Power Query и Power BI?

Основная технология запросов Power Query также включена в Power BI.

Интерфейс программного обеспечения Power BI Desktop позволяет использовать преимущества трех технологий (Power Query, Power Pivot и Power View). С помощью Power BI вы можете выполнять все этапы цепочки анализа данных, от извлечения данных до создания визуальных элементов на интерактивной панели мониторинга, которыми вы можете делиться и анализировать.

4. Почему Power Query — отличная альтернатива Excel VBA?

Power Query может легко заменить VBA (Visual Basic для приложений), поскольку позволяет:

Обрабатывайте свои таблицы, просто нажимая кнопки. Навыки программирования не требуются!
Визуализируйте свои операции шаг за шагом, не запуская ни одного макроса.
Легко изменять порядок выполнения различных шагов с помощью перетаскивания! Кроме того, вы можете добавлять и удалять ненужные шаги простым щелчком мыши.

Однако Power Query не может заменить VBA, когда речь идет о взаимодействии с интерфейсом Excel, автоматизации процессов, создании новых файлов, отправке электронных писем и т. д.

5. Является ли Microsoft Power Query бесплатным? Существует ли Power Query для Mac?

ДА. Power Query — это бесплатное расширение Excel. Это еще одно основное преимущество надстройки.
(Прочитайте следующий вопрос, чтобы узнать, как установить ее на рабочий стол Excel)

Power Query для Mac? ДА, большие новости: редактор запросов теперь совместим с Excel для Mac! Но на данный момент интеграция довольно ограничена, так как вы можете только изменить путь к файлу и обновить существующие запросы.

1. Как скачать Power Query?

Power Query доступен для Excel с 2010 года.

Вот как добавить Power Query в соответствии с вашей версией Excel*:

Хорошие новости: вам не нужно загружать Power Query для Excel 2019, 2016 или Office 365. Вы можете найти его на ленте «Данные» под кнопкой «Получить и преобразовать».
Для Excel 2013 и 2010 необходимо загрузить надстройку Power Query. Он появится на новой специальной вкладке под названием «Power Query» (в Excel 2010 вам необходимо иметь лицензию Professional Plus с лицензией Software Assurance).

*Вот как проверить версию Excel:

Выберите Файл > Учетная запись > Об Excel. Затем появится окно. Прочитайте первые два числа в скобках. В приведенном ниже примере версия Excel — 2016.

Чтобы узнать больше, прочтите еще одну подробную статью о том, как установить и активировать Power Query в Excel.

2. Как активировать Power Query в Excel?

В зависимости от вашей версии Excel (см. вопрос выше: Где находится Power Query в Excel?), вам просто нужно нажать кнопку «Получить данные» в разделе «Получить и преобразовать» на вкладке «Данные» для Excel 2016. и не только. Для версий Excel до 2013 года просто перейдите на ленту Power Query.

Обратите внимание, что при входе в интерфейс Power Query вы больше не сможете взаимодействовать с Excel. Если вам нужно вернуться в Excel, вам придется закрыть редактор Power Query.

3. Как работает Power Query?

В Excel у вас есть доступ только к следующим функциям, когда вы нажимаете «Запросы и подключения»:

Лента данных: для поиска источников данных (лента Power Query для Excel 2010 и 2013).

Панель "Запросы и подключения": для быстрого просмотра всех сделанных вами подключений (запросов).

После запуска Power Query вы попадаете в редактор Power Query, который состоит из 4 отдельных элементов:

Основная лента: позволяет создавать запросы с нуля благодаря кнопкам (если вы знакомы с языком M, вы также можете получить доступ к расширенному редактору, чтобы настроить их вручную).

Запросы: для отслеживания всех подключений к источникам данных и потокам.

Предварительный просмотр таблицы данных: чтобы визуализировать результаты ваших данных после определенного шага.

Настройки запроса (примененные шаги): для навигации по всем сделанным вами шагам. Power Query автоматически записывает все выполненные преобразования.

В двух последних разделах вы сможете немного попрактиковаться благодаря простому варианту использования (сначала вам нужно будет установить Power Query в Excel):

В разделе 4 вы узнаете, как работает Power Query, преобразуя таблицу данных.
В разделе 5 будет еще интереснее, поскольку вы узнаете, как обрабатывать данные из различных источников и запросы на слияние.

4. Как начать работу с Power Query?

Представьте себе следующую ситуацию:

Вы финансовый контролер технологической фирмы, продающей компьютеры и смартфоны. В настоящее время вы работаете в 3 странах: Франция, Великобритания и Китай. Каждый месяц вы составляете сводный отчет для своего руководителя с такими ключевыми показателями эффективности, как общий доход, доход по стране или доход по продукту. Для этого каждый региональный менеджер — Деннис, Дуглас и Карен — отправляет вам файл.

Проблема : в каждом файле указаны продажи в соответствующем номинале страны. Преобразование их в евро, а затем объединение их в окончательный отчет занимает слишком много времени.

Что, если бы вы могли автоматизировать процесс и рассчитать отчет за несколько минут, используя только Power Query?

Разве это не здорово? Ну, с Power Query вы можете! Начнем с подготовки вашего первого стола 🙂

Во-первых, вам нужны данные. Загрузите наш пример Power Query и сохраните его на локальном сервере.

Затем выполните следующие простые действия:

Разархивируйте файл и сохраните его на локальном сервере.
Теперь откройте новый лист в Excel и перейдите на вкладку "Данные" > "Получить данные" > "Из файла" > "Из книги", как показано ниже.

(если у вас Excel версии 2013 или 2010, просто перейдите на вкладку Power Query)

Перейдите по пути, по которому вы сохранили разархивированные файлы, которые вы только что скачали, и выберите файл Excel с именем «Курсы обмена».

Поздравляем! Теперь вы подключены к источнику данных «Курсы валют». Это первый шаг вашего запроса. Чтобы сгладить данные, мы собираемся применить дополнительные шаги (см. панель «Запросы»)! Запрос — это, по сути, подключение к источнику данных.

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

Теперь давайте немного потренируемся, начав с очистки таблицы данных:

Удалите 3 верхние строки, выберите Удалить строки > Удалить верхние строки и введите «3». Вы сразу увидите преобразование в предварительном просмотре таблицы данных, а также в примененных шагах (Настройки запроса).

Избавьтесь от пустых строк: Удалить строки > Удалить пустые строки (как показано ниже).

Добавьте первую строку в качестве заголовка: нажмите «Использовать первую строку в качестве заголовков». Как вы могли заметить, Power Query автоматически добавил шаг для изменения типа некоторых данных (значения в первом столбце теперь отформатированы как даты).

Удалите пустые столбцы: нажмите "Выбрать столбцы" и снимите отметки со столбцов 7, 8, 9 и 10.

Ты молодец! Еще три шага и все готово!

Отменить сводку столбцов. Выберите столбец «Валютный курс», затем на вкладке «Преобразование» выберите «Отменить сводку» > «Отменить сводку других столбцов» (вы также можете выбрать пять других столбцов и нажать «Отменить сводку»).

Переименуйте столбцы: нажмите на ячейки заголовка, введите «Дата» в первом, «Валюта» во втором и «Курс» в третьем.

Примените фильтр: как и в Excel, щелкните раскрывающееся меню столбца "Дата" и выберите "18 декабря".

На вкладке "Главная" нажмите "Закрыть и загрузить", чтобы загрузить итоговую таблицу на новый лист.

Молодец! Вы сформировали свою первую таблицу данных.

5. Пойдем дальше с Power Query

(Пере)представьте следующую ситуацию:

Благодаря вашим навыкам работы с Power Query вы стали СТАРШИМ финансовым контролером технологической фирмы. Теперь вамнужно консолидировать декабрь 2018 г. данные, которые вам прислали региональные менеджеры.

Проблема : в каждом файле указаны продажи в соответствующем номинале страны. Перевод их в евро занимает много времени, еще больше времени уходит на то, чтобы объединить их в окончательный отчет.

Теперь мы увидим, как объединить различные источники данных и объединить их с таблицей, которую мы подготовили в предыдущем разделе.

* Обратите внимание, что перед началом работы с таблицами необходимо выполнить все шаги из предыдущего раздела.

Во-первых, давайте импортируем всю папку, включая три разных исходных файла:

Если вы все еще находитесь в Power Query, выберите Новый источник > Файл > Папка.

(Если вы вернетесь в Excel, выберите «Получить данные» > «Из папки»)

Выберите папку «Sales_201812», которую вы ранее загрузили и сохранили. После этого нажмите ОК.

Должно появиться новое окно.

Затем вам нужно объединить запросы:

Выберите «Объединить и изменить» в раскрывающемся меню (внизу нового окна).

Выберите пример таблицы «Dec-18» (обратите внимание, что все листы должны иметь одинаковые имена).

Вы можете проверить в первом столбце, включены ли все источники (щелкните раскрывающееся меню), а затем нажмите "ОК".

Теперь вы находитесь в редакторе Power Query.

Перейдите на вкладку "Преобразование" > "Заменить значения".

Появится новое окно:

Выберите столбец "Валюта".
Выберите файл «Обменные курсы» в раскрывающемся меню.
Выберите столбец "Валюта".
Нажмите "ОК".

Теперь вам нужно отобразить только что полученные данные обменного курса:
1. Выберите столбец «Обменный курс».
2. Нажмите кнопку "Развернуть".
3. Отметьте только «Оценить».
4. Снимите флажок "Использовать оригинал...".
5. Нажмите "ОК".

Поздравляем! Вы только что загрузили другой источник данных, объединили запросы и получили соответствующий обменный курс. Больше никакой ВПР или ИНДЕКС+ПОИСКПОЗ!

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

Перейдите на вкладку "Добавить столбец" > "Пользовательский столбец".

Появится новое окно. Запишем формулу Цены в Евро:
1. Назовите его «Цена за единицу в евро».
2. Дважды щелкните "Цена за единицу в местной валюте" (или выберите ее, а затем нажмите "Вставить").
3. Введите «/».
4. Дважды щелкните "Оценить".
5. Нажмите "ОК".

После появления столбца нажмите на символ его типа (прямо перед названием) и выберите «Десятичное число».

Мы должны сделать то же самое, чтобы создать столбец «Доход»:

Перейдите на вкладку "Добавить столбец" > "Пользовательский столбец".
Появится новое окно. Давайте введем формулу дохода:
1. Назовите его "Доход".
2. Дважды щелкните «Цена за единицу в евро» (или выберите его, а затем нажмите «Вставить»).
3. Введите «*».
4. Дважды щелкните "Количество".
5. Нажмите "ОК".

Снова измените тип столбца на "Десятичное число".

Чтобы закончить, давайте экспортируем его в новую таблицу данных в Excel!

Перейдите на вкладку "Главная" > "Закрыть и загрузить" > "Закрыть и загрузить".

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

Вау! Если вы дошли до конца, поздравляем! Теперь вы знаете, как работает Power Query.

Теперь ваша очередь!

Я хочу обратиться к вам: вы поняли все приведенные выше вопросы о Power Query?
Вы загрузили наши учебные файлы и следовали приведенным выше рекомендациям, чтобы попробовать его самостоятельно?
Позвольте мы знаем, оставив быстрый комментарий ниже прямо сейчас!

Читайте также: