Пошаговое руководство по созданию вашего первого скрипта microsoft power query

Что такое запрос (Query)¶

Запрос (Query) это программа на языке M, задающая последовательность обработки данных.

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

• таблица — table

• значение — value,

• список — list,

• запись — record и т.д.

Список из всех запросов в Excel можно увидеть в разных местах.

В списке из запросов книги (Queries Pane) интерфейсе Excel 2016:

В интерфейсе самого Power Query:

У каждого запроса есть свое имя.

Имя запроса можно увидеть в нескольких местах. Там же его можно изменить:

В интерфейсе Excel 2016.

В интерфейсе Power Query.

По имени запроса можно обращаться к результатам этого запроса из других запросов.

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

Исходные данные для план-фактного анализа

Изучим возможности применения надстройки Power Query для анализа «план-факт» деятельности торгово-развлекательного центра (ТРЦ).

Для анализа «план-факт» будем использовать следующие данные:

1) планируемые величины статей доходов и расходов (бюджет доходов и расходов БДР);

2) фактические величины статей доходов и расходов (Отчет по доходам и расходам).

Аналитика статей доходов и расходов приведена в табл. 1 в разрезе бизнес-единиц и направлений их деятельности.

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

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

Еще одно значительное преимущество Power Query в том, что таблицы (модели данных), сформированные в этой надстройке, менее требовательны к ресурсам ПК за счет формирования таблиц на ссылках — принцип действия надстройки похож на связанные таблицы Excel.

Преимущества Power Query:

1. Можно использовать различные форматы исходных данных: xml, csv, xls, doc.

2. Можно загружать данные в таблицу Excel из различных источников:

• Интернет;
• базы данных SQL, Oracle, Access, IBM DB, Mysql, Sybase и т. д.;
• веб-службы, протоколы, интерфейсы и облачные хранилища.

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

4. Поиск данных по каталогам.

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

Эти возможности используются также в надстройке Excel Power BI., а также в надстройке Power Query Add-In, доступной для предыдущих версий Excel.

Основные этапы подключения и преобразования данных в Excel посредством Power Query представлены на схеме.

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

Обратите внимание. Функция ВПР в Excel.

Функция ВПР осуществляет поиск значений (это значения, которые указаны в строке Искомое_значение) в первом (самом левом) столбец таблицы, диапазон которой указан в строке Таблица.

Пример, в Таблицу №1, добавили столбец Категория, и теперь столбец Название конфет уже не первый, а второй. Если мы укажем в строке Таблица, в качестве диапазона, все ячейки Таблицы №1, то функция ВПР не сработает (ошибка — #Н/Д), так как она будет осуществлять точный поиск в столбце Категория, Таблицы №1, значений из столбца Название конфет, Таблицы №2. И не найдет точных совпадений.

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

Если в диапазоне Таблица указан один, конкретный столбец, то функция ВПР проверяет только его. И данное правило не обязательно.

Можно осуществлять поиск на разных Листах. Алгоритм работы такой же. Формула функции будет выгладить вот так: =ВПР(E6;Лист1!$B$1:$C$11;2;0). В нашем примере формулы функция ВПР расположена на Листе 2, а поиск значения и перенос данных с диапазона поиска происходит на Листе 1. Вместо диапазона можно использовать Заданное имя. Например Конфеты. Тогда формула функции будет выглядеть вот так: =ВПР(E6;Конфеты;2;0).

Editing the Query

Now that the script is in view and we have reviewed the code let’s make our edit. Remember, the goal was to take all the data in the

column and change it to currency.

Fortunately, we already have an idea for how to accomplish this. Recall this line of code:

We have the code which changed the data types for the entire table! This is where we can make our adjustment, and get our result.

Each column is assigned a type one-by-one, so let’s make the change directly in the code. Currently, the

column is assigned

. We want it to be currency, so let’s change this code to

Here’s what it looks like:

Click Done to be taken back to the main screen. The type symbol is now a dollar sign, which means the type has been converted to currency.

Как в таблицах Excel ранжировать значения

Часто вычисление ранга значений из определенного диапазона данных оказывается полезным. Например, если у вас есть лист, содержащий годовые цифры продаж для торгового персонала, вы можете узнать, какой рейтинг у каждого из них, от самого высокого до самого низкого.

Если вы уже использовали функцию РАНГ, то могли заметить, что положения, выдаваемые этой функцией, не имеют связей, которые могли бы вам подойти. Например, если два значения связаны третьим местом, то функция РАНГ присвоит обоим номер 3. Вы, возможно, предпочтете назначить каждому средний ранг — другими словами, ранг 3,5 для обоих значений, поставленных на третье место.

Excel 2010 содержит новую функцию, названную РАНГ.CP, которая исправляет эту проблему. На рис. 98.1 показана таблица, где используется два метода для ранжирования значений в столбце. Первый метод (столбец С) применяет функцию РАНГ, а в столбце D задается новая функция РАНГ.СР. Диапазон В2:В16 назван Продажи.

Рис. 98.1. Ранжирование данных с помощью функции РАНГ и формул массивов

Обратите внимание на то, что два человека стоят на третьем месте. Функция РАНГ присваивает каждому из них значение 3, а функция РАНГ.CP — 3,5

Кроме того, три человека стоят на десятом месте. Функция РАНГ дает каждому из них ранг 10, а функция РАНГ.CP, с другой стороны, дает им троим ранг 11, что является средним для 10,11 и 12.

Формулы, которые используют функцию РАНГ.CP, вернут ошибку в предыдущих версиях Excel. Если вы собираетесь делиться книгой с теми, кто использует версию Excel ниже 2010, то можете задействовать формулу массива вместо функции РАНГ.СР.

Посмотрите на рис. 98.1 и введите следующую формулу массива в строку 4. Затем скопируйте ее в нижестоящие ячейки. Результат будет точно таким же, как и при использовании функции РАНГ.СР: =СУММ(1*(B4 .

Формула массива представляет собой особый вид формулы, которая работает с внутренними массивами данных. При вводе формулы массива вы должны нажать Ctrl+Shift+Enter (а не просто Enter), чтобы указать, что это формула массива. Excel добавит фигурные скобки вокруг формулы, напоминая вам, что это формула массива. Если вы не нажмете Ctrl+Shift+Enter, формула не будет работать.

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

Импорт данных в Power Query

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

,

и

Цель этого руководства — взять столбец

и преобразовать данные из числа в формат валюты с помощью Power Query.

У нас эта таблица сохранена на рабочий стол. Давайте создадим новую книгу Excel под названием Fruit Data.xlsx для импорта наших данных.

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

Это откроет ваш файловый проводник. Выберите книгу Fruits.xlsx для импорта и нажмите ОК .

Power View

Иногда сводная таблица — не лучший вариант визуализации данных. В таком случае можно создавать отчеты Power View. Как и Power Pivot, Power View — это надстройка Excel, которая по умолчанию включена в версии Pro Plus и выше (начиная с версии 2010).

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

Вот некоторые возможности Power View:

— быстро добавлять в отчет таблицы, диаграммы (без необходимости настройки)

организовывать срезы и фильтры

уходить на разные уровни детализации данных

добавлять карты и располагать на них данные

создавать анимированные диаграммы

Пример отчета Power View — на рисунке 5.

Рис. 5. Пример отчета Power View

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

Добавление нового столбца в Power Query¶

Для выбора действия в зависимости от условия в Power Query используется структура с оператором if then else

Пример:

Условный столбец (Conditional column)

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

Пояснения к мастеру конфигурации условного столбца показаны на скриншоте ниже.

Для сведения — задаваемые условия в интерфейсе мастера пишут команду скрипта содержащего операторы . Это значит, что первым будет проверено первое условие в мастере условного столбца. Все множество значений будет проверено по этому условию. В случае, если будут найдены результаты удовлетворяющие первому условию, то для них будет присвоено значение по результатам выполнения первого условия. Для остальных значений из множества, будет проверено следующее условие. И так далее, до тех пор, пока не будут проверенны все условия. Если для элементов множества не будет выполненно ни одно условие, то в условный столбец попадет значение из поля «В противном случае / (otherwise)».

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

Удалить дубликаты в столбцах

Команда «Remove Duplicates» проходит по выбранным столбцам (если выбрана вся таблица, то по всей таблице) и смотрит в них повторяющиеся ячейки (строчки, в случае если выбрана таблица). Если дубликаты найдены функция оставляет первую попавшуюся уникальную строчку и удаляет все последующие повторяющиеся Найти команду можно на Ribbon — Home — Remove Duplicates (Удалить дубликаты в выбранных столбцах)

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

Удалить дубликаты строк в таблице можно нажав на кнопку в левом верхнем углу таблицы предпросмотра.

Аналогичного результата можно добиться если использовать команду «Group By»

Извлечение шагов в отдельный запрос

Для выполнения необходимо щелкнуть правой кнопкой на конкретном шаге обработки. Выбрать пункт меню «Extract previous steps»

ввести имя нового запроса, который будет создан на основе предыдущих шагов

Описание примеров

Примеры к статье построены на основе демонстрационной базы данных, которую можно скачать с сайта Microsoft

Выгруженный из этой базы данных набор записей сформирован при помощи Microsoft Query.

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

Файл nwdata_sums.xls используется для версий Excel 2000-2003

Файл nwdata_sums.xlsx имеет некоторые отличия и используется для версий Excel 2007-2010.

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

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

Разбить запрос на части

С первым способом всё, в общем, понятно. Для этого есть специальная команда в меню по клику правой кнопкой мыши в панели шагов Power Query: «Извлечь предыдущий»:

Извлечь предыдущие шаги в новый запрос

При помощи этой команды все шаги до текущего будут выделены в новый запрос, а все шаги начиная с текущего – останутся в прежнем запросе, причем в качестве источника для прежнего запроса будет использовано имя нового запроса. Имя для нового запроса будет задано при разделении. Таким образом, мы можем дать новому запросу имя GetValueX, и использовать его результат как для продолжения преобразований, так и в качестве источника для других запросов.

Разделяем запросы на несколько

Этот подход – наиболее правильный и логичный, если мы можем непосредственно использовать
ValueX  для получения
ResultTable  (то есть
ValueX  содержит всю необходимую информацию для получения
ResultTable ).

Но если промежуточный шаг, который мы хотим извлечь из запроса, содержит в себе только часть нужных данных, или вообще скалярное значение (например, мы рассчитали минимальную дату по столбцу, и в дальнейшем планируем ее использовать в других запросах) нам придется разбивать запрос немного ранее, и дублировать получение
ValueX  в разных запросах:

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

На этом рисунке зеленая ветвь обозначает наш основной запрос, который мы разбили на два новых заранее, до шага получения искомого значения
ValueX . В красной ветке мы ссылаемся на Query1, чтобы получить отдельным запросом
ValueX  для использования в запросах Query2 и Query3.

А теперь представьте, что нам нужно получить еще и
ValueY , и
ValueZ , и еще несколько промежуточных значений (что является вполне реальным сценарием, если на входе у нас один большой массив данных, из которого мы строим модель). Цепочка зависимостей запросов разрастается, что может привести к самым разным последствиям – от неудобства ориентирования в большом документе и до количества обращений Power Query к источнику данных. Иногда этого избежать невозможно (да и не нужно), но иногда городить целый огород для того, чтобы воспользоваться всего одним числом – совершенно избыточно.

Упрощает повторяющиеся задачи.

Распространенной задачей при работе с книгами Excel является внесение одного и того же набора изменений в разные листы.

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

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

Когда вы подключаетесь к книге с помощью Power Query и вносите изменения, эти изменения сохраняются как «шаги». Эти шаги — это просто инструкции, которые вы даете Excel для изменения данных (например, «Удалить этот столбец» или «Сортировать эту таблицу по цвету»).

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

Как ранжировать список по возрастанию в Excel

Пример 1. Есть документ, в котором записаны фамилии учеников и суммы баллов по оценкам за четверть. Требуется определить ранжирование учеников по успеваемости.

Используем для ячейки C2 формулу =РАНГ(B2;$B$2:$B$7;0), для ячейки D2 – формулу =РАНГ.РВ(B2;$B$2:$B$7;0), а для ячейки E2 – формулу =РАНГ.СР(B2;$B$2:$B$7;0). Протянем все формулы на ячейки ниже.

Таким образом, видно, что ранжирование по функциям РАНГ() и РАНГ.РВ() не отличается: есть два ученика, которые заняли второе место, третьего места нет, а также есть два ученика, которые заняли четвёртое место, пятого места также не существует. Ранжирование было произведено по высшим из возможных вариантов.

В то же время функция РАНГ.СР() присвоила совпавшим ученикам среднее значение из мест, которые они могли бы занимать, если бы сумма баллов, например, была с разницей в один балл. Для второго и третьего места среднее значение – 2,5; для четвёртого и пятого – 4,5.

Возможные ошибки.

#Н/Д — столбец таблицы, по которому происходит поиск, не крайний левый в диапазон поиска.

#Н/Д — диапазон таблицы, в которой происходит поиск, не закреплен. Нужно использовать либо абсолютные ссылки ($), либо присвоить диапазону Заданное имя.

#Н/Д – функция ВПР не находить точного совпадения в диапазоне поиска по заданному значению поиска.

#Н/Д – возможно необходимо отсортировать диапазон, в котором происходит поиск, по возрастанию.

#ССЫЛКА! – возможно номер столбца, который указан в строке Номер_столбца, указан неверно, и функция не находит данные, которые должна перенести.

Ректальное администрирование: Основы для практикующих системных АДминистраторов

Одной из самых популярных и зарекомендовавших себя методологий системного администрирования является так называемое ректальное. Редкий случай сопровождения и обслуживания информационных систем, инфраструктуры организации обходится без его использования. Зачастую без знания данной методологии сисадминам даже бывает сложно найти работу в сфере ИТ, потому что работодатели, особенно всякие аутсорсинговые ИТ фирмы, в основном отдают предпочтение классическим, зарекомендовавшим себя методикам, а не новомодным заграничным веяниям: практикам ITIL, нормальным ITSM и прочей ерунде.

Использование редактора Power Query Editor

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

Нажмите Преобразовать данные , чтобы открыть редактор Power Query.

Просмотр сценария

На первый взгляд, многое происходит, но давайте сосредоточимся на получении сценария.

Открыв редактор Power Query Editor, на панели инструментов в разделе Home нажмите Advanced Editor , чтобы открыть скрипт. Откроется расширенный редактор, и внутри вы увидите скрипт Power Query на языке M.

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

Содержание сценария

Прежде всего, обратите внимание, что сценарий представляет собой формат «let/in», который мы использовали с языком М. Внутри «let» находятся строки кода, которые вносят ваши изменения, а «in» — это то место, где изменения будут выводиться

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

Power View в Excel 2016

Power View в Excel 2016

Добрый день, уважаемые подписчики и читатели блога! Я, как и водится, всегда описываю нововведения в Excel с заметным опозданием. Что поделать — я консерватор (хотя теперь модно говорить — слоупок).

Итак, сегодня начнём разговор о Power инструментах в Excel. В этом видео на канале уже был обзор Power Map. В этой статье мы использовали приложение Bing Maps.

Что такое Power View?

Ответ очень прост — инструмент визуализации данных из таблицы. Есть свои ограничения (обязательна установка Silver Light от Microsoft), есть и масса плюсов в виде интерактивных отчётов, диаграмм, таблиц. Например, у нас есть таблица, нужно чтобы эти данные отобразились в диаграмме. Оговорюсь сразу — таблица большая и всю её можно найти на сайте ЦИК.

Включение Power View в Excel.

Не совсем понятные вещи стали твориться с самого начала — включить надстройку недостаточно! Нам придётся пройти небольшой квест:

• установить MS Silverlight;
• включить надстройку в Excel;
• добавить кнопку Power View на вкладку «Вставка».

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

В плане включения надстройки ещё проще — идём в «Параметры», «Надстройки», внизу выбираем из списка «Надстройки COM» или переходим на вкладку «Разработчик», ищем пункт «Надстройки COM», в появившемся окне отмечаем галкой «Power View», жмём «Ок».

Мы не из пугливых:

• идём в «Параметры»;
• выбираем пункт «Настроить ленту»;
• раскрываем список вкладки «Вставка»;
• создаём новую группу «Отчёты» (или как вам захочется);
• слева выбираем список «Все команды»;
• добавляем Power VIew в созданную группу команд.

Подробно я рассказывал о настройке ленты в Excel вот в этом видео.

После добавления на вкладке «Вставка» появится новый блок кнопок и сама кнопка «Power View».

Тэк-с, своего мы добились. Теперь настало время загрузить таблицу. Как будете её загружать — дело десятое (советую Power Query), главное, чтобы она появилась на листе Excel.

Обязательно сделайте ваши таблицы умными — щёлкнуть в любую ячейку и нажать Ctrl+T, в появившемся окне «Ок», не снимать галку «Таблица с заголовками». Если будете использовать Power Query — у вас уже будут умные таблицы.

Встаём во вторую таблицу и на вкладке «Вставка» нажимаем Power View.

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

Настройка отображения в Power View

Что ж, подредактируем нашу модель данных. Для начала отключим столбец «Процент», чтобы построить круговую диаграмму (ну или столбец «Кол-во голосов», если так будет удобнее). Справа в области запросов снимаем галку со столбца «Процент».

Из представления тут же исчезнет столбец. Теперь можно преобразовать таблицу в диаграмму. Для этого на вкладке «Конструирование» нужно нажать «Другая диаграмма» и выбрать вариант «Круговая диаграмма».

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

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

Есть небольшой минус — чтобы узнать точное количество надо наводить указатель на долю кандидата, мы же просто вставим таблицу с данными, чтобы видеть при щелчке сколько кто набрал. Для этого щёлкнем в свободном месте и отметим галками справа столбец «Кандидат» и «Кол-во голосов». Теперь при щелчке на долю в круговой диаграмме появится и число голосов.

Вот и всё! Таким образом и будем строить наш отчёт. Во второй части статьи (она будет чуть позже) мы поработаем с таблицей по регионам и попробуем отобразить данные на карте. Всем удачи!

Что такое Microsoft Power Query?

Расширение Power Query (известное в Microsoft Excel 2016 как «Получить и преобразовать») — это инструмент преобразования данных, встроенный прямо в Microsoft Excel. Он позволяет импортировать данные из множества различных источников, вносить изменения и загружать данные непосредственно в книгу Excel.

Если вы только начинаете, было бы неплохо изучить основы Microsoft Excel.

При импорте в Power Query он устанавливает соединение с вашими данными. Благодаря этому подключению вы можете работать со своими данными в редакторе и вносить все изменения перед сохранением в книгу.

Power Query сохранит ваши изменения в виде шагов в файле, известном как «Запрос», который можно использовать в любое время. Думайте об этом как о макросах в программировании на VBA, если вы знакомы с ними.

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