Пользовательский интерфейс power query

Использование Power Query в продуктах и службах «Майкрософт»

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

Продукт	M engine1	Power Query Desktop2	Power Query Online3	Потоки данных 4
Excel для Windows	+	+	—	—
Excel для Mac	+	—	—	—
Power BI	+	+	+	+
Power Apps	+	—	+	+
Power Automate	+	—	+	—
Сервер отчетности Power BI	+	+	—	—
Облачная платформа Azure	+	—	+	+
Сервисы интеграции SQL Server	+	—	—	—
Сервисы аналитики SQL Server	+	+	—	—
Dynamics 365 Customer Insights	+	—	+	+
Mengine	Используется язык M для базового механизма выполнения запросов.
Power Query Desktop	Интерфейс PQ используется в классических приложениях.
Power Query Online	Интерфейс PQ используется в веб-приложениях.
Dataflows	PQ представлен в качестве облачного сервиса, не зависящего от конкретного продукта. Результаты преобразований данных используются другими приложениями.

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

The File Tab

The File tab contains various options for saving any changes made to your queries as well as power query options and settings.

Close & Load – This will save your queries and load your current query into an Excel table in the workbook.
Close & Load To – This will open the Import Data menu with various data loading options to choose from.
Discard & Close – This will discard any changes you made to the queries during your session in the editor and close the editor.

Note, you will still need to save the workbook in the regular way to keep any changes to queries if you close the workbook.

Close & Load and Close & Load To commands are also available from the Home tab.

Преобразование данных в Power Query

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

Итак, имеется таблица расходов, разбитых по месяцам, а также разделенных на две категории.

Только до 26.12

Как за 3 часа разбираться в IT лучше, чем 90% новичков и выйти надоход в 200 000 ₽?

Приглашаем вас на бесплатный онлайн-интенсив «Путь в IT»! За несколько часов эксперты
GeekBrains разберутся, как устроена сфера информационных технологий, как в нее попасть и
развиваться.

Интенсив «Путь в IT» поможет:

За 3 часа разбираться в IT лучше, чем 90% новичков.
Понять, что действительно ждет IT-индустрию в ближайшие 10 лет.
Узнать как по шагам c нуля выйти на доход в 200 000 ₽ в IT.

При регистрации вы получите в подарок:

«Колесо компетенций»

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

«Критические ошибки, которые могут разрушить карьеру»

Собрали 7 типичных ошибок, четвертую должен знать каждый!

Тест «Есть ли у вас синдром самозванца?»

Мини-тест из 11 вопросов поможет вам увидеть своего внутреннего критика

Хотите сделать первый шаг и погрузиться в мир информационных технологий? Регистрируйтесь и
смотрите интенсив:

Только до 26 декабря

Получить подборку бесплатно

pdf 4,8mb
doc 688kb

Осталось 17 мест

Общеизвестно, что такое форматирование данных не позволит построить сводную таблицу. Для приведения значений к нужной структуре используем функционал PQ. Последовательность действий будет следующей:

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

Преобразование данных в Power Query

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

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

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

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

Power Query Records Your Steps & Automates Processes

Power Query not only makes all these tasks easier, but it also records your steps so you do NOT have to do them over-and-over again. It will save you a lot of time if you are preparing the same data every day, week, or month.

It also does a pretty good job of handling errors. If the structure of your source data changes, Power Query will tell you what step it broke at and allow you to fix it. This makes maintenance easy and you don’t have to completely redo your process when something changes.

You can use Power Query to get your data ready for use in pivot tables, charts, and dashboard reports. This is a critical step in the process of summarizing and analyzing data.

Добавление нового запроса

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

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

Интерфейс подключения OData снова появится. Введите параметры подключения, как описано для подключения к веб-каналу OData Northwind. В интерфейсе навигатора найдите и выберите таблицу «Поставщики «.

Нажмите кнопку «Создать», чтобы добавить новый запрос в редактор Power Query. Теперь на панели запросов должны отображаться запросы «Клиенты» и «Поставщики «.

Снова откройте диалоговое окно «Группировать по «, нажав кнопку » Группа» на ленте на вкладке «Преобразование «.

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

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

Примечание

Дополнительные сведения о группе по преобразованию см. в разделе «Группирование» или «Суммирование строк».

Conclusions

Power Query can seem overwhelming at first to someone new to it all, but the UI is very well laid out and easy to catch on to. While it might be new to a user, a lot of the concepts should be familiar to an Excel user already.

Getting familiar with all the parts of the editor and the layout of the ribbon tabs is an essential first step in exploring Power Query and incorporating it into your everyday work.

While there is a lot to learn about Power Query, it is worth putting in the time to learn. There is massive potential to save time in repetitive data cleaning and formatting tasks with it. It’s one of the most powerful and useful tools that has been added to Excel since pivot tables.

What is Power Pivot?

Power Pivot is a calculation engine for pivot tables. You can use Power Pivot to model complex data, set up relationships between tables, calculate things to be show in value field area of Pivot tables / pivot charts or visuals.

Think of Power Pivot as a calculation layer between your data and outputs. You can tell Power Pivot how you want your calculations done thru a language called as DAX and Power Pivot can give the answers. It is an extremely fast & scalable software.

We can use Power Pivot in either Excel or Power BI.

How to get Power Pivot?

In Power BI:

Power Pivot is an part of Power BI. So there is no need to get Power Pivot. It is always there. You can use various features of Power Pivot from Modeling ribbon and from data & relationship views.

In Excel:

The present Power Pivot availability and licensing model is more complex than DAX. Let me try to highlight the key points. A good place to check is where is Power Pivot page by Microsoft.

There are two kinds of Power Pivot for Excel.

Power Pivot engine: this is necessary for calculating values in pivot tables. It is available in Excel 2013, 2016, Office 365 and future versions of Excel.
Power Pivot creator: this is necessary for adding new kinds of measures, managing data model etc. This is currently available only in certain types of Excel (professional, professional plus versions). From Excel 2019, this will be available in all kinds of Excel.

Excel 2016 / 2013 / Office 365 Pro versions: Power Pivot is available in professional & pro plus versions. No need to download anything. Just enable Power Pivot COM Add-in and you are good to go.
Excel 2013 & 2010: You can download free Power Pivot add-in from Microsoft and install it to use Power Pivot.

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

Что делать с данными после преобразования в Power Query? Как добавить их в другие программы? Разберем несколько доступных вариантов импорта данных.

Для начала следует определить подходящие способы в процессе сохранения запроса («Главная» — «Закрыть и загрузить в…»). В сохраненном запросе параметры импорта задаются на вкладке «Запросы и подключения» путем клика правой клавишей мыши по названию запроса и выбора «Загрузить в…».

В редакторе Excel предлагается импортировать преобразованные данные одним из нескольких способов:

Выгрузка на лист

Сперва задаем способ представления данных, затем переходим во вкладку «Таблица», далее вставляем данные на лист. При этом автоматически создается смарт-таблица.

Сводная таблица (диаграмма)

Здесь после определения способа представления нужно перейти в «Отчет сводной таблицы» («Сводная таблица»). Импорт данных будет выполнен в соответствующем виде.

Только подключение

Для сохранения запроса следует выбрать пункт «Только создать подключение».

How to use a Formula and a Function in Power Query

Just like you can use functions and formulas in Excel worksheet, the power query has its own list of functions that you can use. The basics of function and formulas in power query are the same as Excel’s worksheet functions.

In PQ, you need to add a new custom column to add a function or a formula.

Let’s take an example: In the below data (already in the PQ editor) you have the first name and last name (DOWNLOAD LINK).

Imagine you need to merge both names and create a column for the full name. In this case, you can enter a simple formula to concatenate names from both columns.

First, go to the Add Column tab and click on the “Custom Column”.

Now in the custom column dialog box, enter the name of the new column “Full Name” or anything you want to name the new column.

The custom column formula is the place where you need to enter the formula. So enter the below formula in it:

&" "&

When you enter a formula in the “custom column formula”, PQ verify the formula that you have entered and shows a message “No syntax error have been detected” and if there’s an error it will show an error message based on the type of the error.
Once you enter the formula and that formula doesn’t have any errors in it, simply press OK.
Now you have a new column at the end of the data which has values from two columns (first name and the last name).

How to use a Function in Power Query

In the same way, you can also use a function while adding a custom column and Power Query has a huge list of functions that you can use.

Let’s understand how to use a function with an easy and simple example. I’m continuing the above example where we have added a new column by combining the first name and last name.

But now, you need to convert that full name text which you have in that column into the upper case. The function which you can use is “Text.Upper”. As the name suggests, it converts a text to an upper-case text.

First, go to the add column tab and click on the custom column.

Now in the custom column dialog box, enter the column name and below formula in the custom column formula box:

And when you click OK it creates a new column with all the names in the uppercase.
The next thing is to delete the old column and rename the new column. So right-click on the first column and select remove.

In the end, rename the new column his “Full Name”.

There are a total of 700 functions that you can use in power query while adding a new column and here is the complete list provided by Microsoft for these functions, do check them out.

Использование параметров

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

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

Основные преимущества создания и использования параметров:

Централизованное представление всех параметров с помощью окна «Управление параметрами «.
Возможность повторного использования параметра в нескольких шагах или запросах.
Упрощает и упрощает создание пользовательских функций.

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

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

Примечание

Дополнительные сведения о создании и использовании параметров см. в разделе «Использование параметров».

The Queries & Connections Window

When you’re working outside of the power query editor, you can see and interact with all the queries in the workbook through the Queries & Connections window. To open this, go to the Data tab in the regular Excel ribbon, then press the Queries & Connections command button found in the Queries & Connections section.

When opened it will be docked to the right hand side of the workbook. You can undock it by left clicking on the title and dragging it. You can drag it to the left hand side and dock it there or leave it floating. You can also resize the window by left clicking and dragging the edges.

This is very similar to the query list in the editor and you can perform a lot of the same actions with a right click on any query.

One option worth noting that’s not in the query list right click menu, is the Load To option. This will allow you to change the loading option for any query, so you can change any Connection only queries to load to an Excel table in the workbook.

Another thing worth noting is when you hover over a query with the mouse cursor, Excel will generate a Peek Data Preview. This will show you some basic information about the query.

Data Preview – This is a live preview of the data similar to when first setting up a query.
Columns – This will give you a list of all the columns contained in the final results of the query along with a count of how many columns there are. Clicking on any of them will highlight the column in the data preview.
Last Refreshed – This will tell you when the data was last refreshed.
Load Status – This displays whether the data is loaded to a table, pivot table, pivot chart or is a connection only.
Data Sources – This will show you the source of the data along with a count of the number of files if you’re it’s a from folder query.
View in Worksheet – Clicking on this will take you to the output table if the query is loaded to a table, pivot table or pivot chart.

You can also access this Peek view by right clicking on the query and selecting Show the peek.

There are also some useful messages displayed in the Queries & Connections window for each query. It will show you if the query is a connection only, if there were any errors when the query last ran, or how many rows loaded.

Overview of the Power Query Ribbon

Starting in Excel 2016 for Windows, Power Query has been fully integrated into Excel. It is now on the Data tab of the Ribbon in the Get & Transform group.

In Excel 2010 and 2013 for Windows, Power Query is a free add-in. Once installed, the Power Query tab will be visible in the Excel Ribbon.

Click to Enlarge

You use the buttons in the Data or Power Query tab to get your source data. Again, your data could be stored in Excel files, csv files, Access, SQL server database, SharePoint, Salesforce.com, Dynamics CRM, Facebook, Wikipedia, websites, and more.

Once you have specified where your data is coming from, you then use the Power Query Editor window to make transformations to the data.

The buttons in the Power Query Editor Window allow you to transform your data.

Think about some of those tasks you do repeatedly as you browse the buttons in the image above. Each time you press a button your actions (steps) are recorded, and you can quickly re-apply the steps when you receive new data by refreshing the query.

After completing your steps, you can output the data to a Table in your Excel workbook by clicking the Close & Load button.

You can also modify existing queries and refresh your output tables with the changes or updated data.

The Concept of Power Query

To learn power query, you need to understand its concept that works in 3 steps:

1. Get Data

Power query allows you to get data from different sources like web, CSV, text files, multiple workbooks from a folder, and a lot of other sources where we can store data.

2. Transform Data

After getting data in the power query you have a whole bunch of options that you can use to transform it and clean it. It creates queries for all the steps you perform (in a sequence one step after another).

3. Load Data

From the power query editor, you can load the transformed data to the worksheet, or you can directly create a pivot table or a pivot chart or create a data connection only.

Что такое Power Query и зачем он нужен?

Что же делать тем, кому нужно работать быстрее и эффективнее, но учиться программировать не хочется или нет возможности? Ответ прост — используйте Power Query. Это очень мощный инструмент из класса ETL (Extract, Transform, Load — извлечение, преобразование, загрузка). Как следует из названия, он умеет извлекать данные из разных источников, преобразовывать их (та самая рутина), а затем выгружать или загружать, куда нужно. На практике это позволяет решать множество проблем.

Представим, что у Вас есть ежедневная задача по составлению отчета. Вы уже подготовили в Excel таблицу, построили сводные и графики, придумали формулы. Но исходные данные приходят каждый день в виде CSV-файлов и складываются в какую-то папку. Ваша задача: каждый день брать из папки вновь появившиеся файлы, доставать из них только то, что нужно, копировать в свои таблицы Excel, обновлять сводные и графики. Скучно и рутинно. А с помощью Power Query все описанные выше операции можно свести к нажатию одной кнопки «Обновить»! Потрясающе просто и эффективно!

Где использовать параметры

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

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

В следующих разделах вы увидите пример для этих двух сценариев.

Аргумент шага

Чтобы включить эту функцию, сначала перейдите на вкладку «Вид» в редакторе Power Query и выберите параметр «Всегда разрешать» в группе «Параметры».

Например, в следующей таблице «Заказы » содержатся поля OrderID, Units и Margin .

В этом примере создайте новый параметр с именем Minimum Margin с типом десятичного числа и текущим значением 0,2.

Перейдите к запросу «Заказы » и в поле «Поля» выберите параметр » Больше, чем фильтр».

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

После нажатия кнопки «ОК» таблица фильтруется с помощью текущего значения параметра.

Если изменить текущее значение параметра Minimum Margin на 0,3, запрос заказов обновляется немедленно и отображает только строки, в которых поле превышает 30 %.

Совет

Многие преобразования в Power Query позволяют выбрать параметр из раскрывающегося списка. Мы рекомендуем всегда искать его и пользоваться преимуществами доступных параметров.

Аргумент пользовательской функции

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

Но вы можете присвоить этой новой функции имя. В демонстрационных целях имя этой новой функции — MyFunction. После нажатия кнопки «ОК» в области запросов создается новая группа с помощью имени новой функции. В этой группе вы найдете параметры, используемые для функции, запрос, который использовался для создания функции, и саму функцию.

Чтобы протестировать эту новую функцию, введите значение, например 0,4, в поле под меткой минимального поля . Затем нажмите кнопку «Вызвать «. При этом создается новый запрос с именем вызываемой функции, фактически передавая значение 0,4, которое будет использоваться в качестве аргумента для функции, и предоставляя только строки, в которых поле превышает 40 %.

Дополнительные сведения о создании пользовательских функций см. в разделе «Создание настраиваемой функции».

Запросы на проверку правописания в будущем

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

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

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

Если запрос содержит динамическое число строк с данными, но фиксированное количество строк, которые служат в качестве нижнего колонтитула, который следует удалить, можно использовать функцию «Удалить нижние строки «.

Примечание

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

Примечание

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

Примечание

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

Примечание

Дополнительные сведения о работе и работе с ошибками см. в разделе «.

The Add Column Tab

The Add Column tab contains a lot of commands similar to the Transform tab, but the key difference is they will create a new column with the transformation.

General – This section allows you to add new columns based on formulas or custom functions. You can also add index colummns or duplicate a column from here.
From Text – Very similar to the From Text section in the Transform tab, but these commands will create a new column with the transformation.
From Number – Very similar to the From Number section in the Transform tab, but these commands will create a new column with the transformation.
From Date & Time – Very similar to the From Date & Time section in the Transform tab, but these commands will create a new column with the transformation.

Выбор подходящего соединителя

Power Query предлагает огромное количество соединителей данных. Эти соединители варьируются от таких источников данных, как TXT, CSV и Excel файлы, до баз данных, таких как Microsoft SQL Server, и популярных служб SaaS, таких как Microsoft Dynamics 365 и Salesforce.
Если источник данных не отображается в окне получения данных , для подключения к источнику данных всегда можно использовать соединитель ODBC или OLEDB.

Использование оптимального соединителя для задачи обеспечит оптимальный интерфейс и производительность. Например, использование соединителя SQL Server вместо соединителя ODBC при подключении к базе данных SQL Server обеспечивает не только более эффективную работу с данными, но и соединитель SQL Server также предлагает функции, которые могут улучшить работу и производительность, например свертывание запросов. Дополнительные сведения о свертке запросов см. в статье Power Query свертывание запросов.

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

Примечание

Полный список доступных соединителей в Power Query см. в разделе «Соединители» в Power Query.

Why Should You Use Power Query (Benefits)?

If you have this question in your mind, here’s my answer for you:

Different Data Sources: You can load data into a power query editor from different data sources, like, CSV, TXT, JSON, etc.
Transform Data Easily: Normally you use formulas and pivot tables for data transformations but with POWER QUERY you can do a lot of things just with clicks.
It’s Real-Time: Write a query once and you can refresh it every time there is a change in data, and it will transform the new data which you have updated.

Let me share an example:

Imagine you have 100 Excel files that have data from 100 cities and now your boss wants you to create a report with all the data from those 100 files. OKAY, if you decide to open each file manually and copy and paste data from those files and you need at least one hour for this.

But with the power query, you can do it in minutes. Feeling excited? Good.

Further in this tutorial, you will learn how to use Power Query with a lot of examples, but first, you need to understand its concept.