Общие задачи с запросами в Power BI DesktopCommon query tasks in Power BI Desktop

При работе в окне Редактор запросов Power BI Desktop можно воспользоваться рядом стандартных задач.When working in the Query Editor window of Power BI Desktop, there are a handful of commonly used tasks. В этом документе показаны эти распространенные задачи и приведены ссылки для получения дополнительных сведений.This document demonstrates those common tasks, and provides links for additional information.

Здесь показываются следующие распространенные задачи:The common query tasks demonstrated here are the following:

  • Подключение к даннымConnect to data
  • Формирование и объединение данных.Shape and combine data
  • Группирование строк.Group rows
  • Сводные столбцы.Pivot columns
  • Создание настраиваемых столбцов.Create custom columns
  • Формулы запросов.Query formulas

Мы будем использовать несколько подключений к данным для выполнения этих задач.We’ll use a few data connections to complete these tasks. Данные доступны для загрузки или подключения, если вы хотите самостоятельно выполнить эти задачи.The data is available for you to download or connect to, in case you want to step through these tasks yourself.

Первое подключение к данным — это книга Excel.The first data connection is an Excel workbook. Второе — веб-ресурс (также используемый в другом контенте справки по Power BI Desktop), доступ к которому можно получить здесь:The other is a Web resource (which is also used in other Power BI Desktop help content) which can be accessed from here:

http://www.bankrate.com/finance/retirement/best-places-retire-how-state-ranks.aspx

Распространенные задачи запросов начинаются с шагов, необходимых для подключения к этим источникам данных.The steps necessary to connect to both of those data sources is where the common Query tasks begin.

Подключение к даннымConnect to data

Для подключения к данным в Power BI Desktop нажмите кнопку Получить данные на вкладке ленты Главная.To connect to data in Power BI Desktop, select the Get Data button from the Home tab on the ribbon. В Power BI Desktop откроется меню с наиболее распространенными источниками данных.Power BI Desktop presents a menu with the most common data sources. Чтобы открыть полный список источников данных, к которым может подключаться Power BI Desktop, нажмите кнопку Еще... внизу меню.For a complete list of data sources to which Power BI Desktop can connect, select the More... button at the bottom of the menu. Дополнительные сведения см. в разделе Источники данных в Power BI Desktop.For more information, see Data Sources in Power BI Desktop.

Для начала выберите Excel и перейдите к рабочей книге, затем выберите ее.To start with, select Excel and navigate to the workbook, then select it. Запрос изучает книгу и представляет найденные данные в окне Навигатор .Query inspects the workbook, then presents the data it found in the Navigator window.

Можно щелкнуть Изменить , чтобы скорректировать или сформировать данные перед загрузкой в Power BI Desktop.You can select Edit to adjust, or shape, the data before loading it into Power BI Desktop. Редактирование запроса перед загрузкой особенно полезно при работе с большими наборами данных, которые требуется сократить перед загрузкой.Editing a query before loading is especially useful when working with large data sets that you intend to pare down before loading. Поскольку нам это и нужно, мы выбираем Изменить.We want to do that, so we select Edit.

Подключение к другим типам данных выполняется так же просто.Connecting to different types of data is just as easy. Нам также нужно подключиться к веб-ресурсу.We also want to connect to a Web resource. Выберите Получить данные > Еще… и щелкните Другие > Интернет.Select Get Data > More... and then select Other > Web.

Отобразится окно Из Интернета , где можно ввести URL-адрес веб-страницы.The From Web window appears, where you can type in the URL of the Web page.

Нажмите кнопку ОКи, как и прежде, Power BI Desktop изучает книгу и представляет найденные данные в окне Навигатор .Select OK, and like before, Power BI Desktop inspects the workbook and presents the data it finds in the Navigator window.

Другие подключения к данным выполняются аналогично.Other data connections are similar. Если для подключения к данным требуется аутентификация, в Power BI Desktop отобразится запрос на ввод соответствующих учетных данных.If authentication is required to make a data connection, Power BI Desktop prompts you for the appropriate credentials.

Пошаговую демонстрацию подключения к данным в Power BI Desktop см. в разделе Подключение к данным в Power BI Desktop.For a step-by-step demonstration of connecting to data in Power BI Desktop, see Connect to Data in Power BI Desktop.

Формирование и объединение данных.Shape and combine data

С помощью редактора запросов легко формировать и комбинировать данные.You can easily shape and combine data with Query Editor. В этом разделе приводится несколько примеров формирования данных.This section includes a few examples of how you can shape data. Полную демонстрацию формирования и объединения данных с помощью Power BI Desktop см. в статье Формирование и объединение данных с Power BI Desktop.For a more complete demonstration of shaping and combining data, see Shape and Combine Data with Power BI Desktop.

В предыдущем разделе мы подключались к двум наборам данных: книге Excel и веб-ресурсу.In the previous section we connected to two sets of data – an Excel workbook, and a Web resource. После загрузки данных в редакторе запросов отображаются следующие сведения с выбранным на веб-странице запросом (из доступных запросов, перечисленных в области Запросы в левой части окна "Редактор запросов").Once loaded in Query Editor we see the following, with the query from the Web page selected (taken from the available queries listed in the Queries pane, on the left side of the Query Editor window).

При формировании данных источник данных преобразуется в нужные форму и формат.When you shape data, you transform a data source into the form and format that meets your needs. В этом случае первый столбец под названием Заголовокне требуется, поэтому его нужно удалить.In this case, we don’t need that first column, titled Header, so we’ll remove it.

В представлении Редактор запросовна ленте и в контекстном меню, которое открывается щелчком правой кнопки мыши, можно найти множество команд.In Query Editor, many commands can be found in the ribbon, and in a context-sensitive right-click menu. Например, если щелкнуть правой кнопкой мыши столбец Заголовок, в отобразившемся меню можно удалить этот столбец.For example, when you right-click on the Header column, the menu that appears lets you remove the column. Вы также можете выбрать столбец, а затем нажать кнопку Удалить столбцы на ленте.You could also select the column and then select the Remove Columns button from the ribbon.

Существует множество других способов форматировать данные в этом запросе: можно удалить любое количество строк сверху или снизу, можно добавлять столбцы, разделять столбцы, заменять значения и выполнять другие задачи формирования, чтобы с помощью редактора запросов получить требуемые данные.There are many other ways you could shape the data in this query; you could remove any number of rows from the top, or from the bottom; you could add columns, split columns, replace values, and perform other shaping tasks to direct Query Editor to get the data how you want it.

Группирование строк.Group rows

В редакторе запросов можно группировать значения из нескольких строк в одно значение.In Query Editor, you can group the values in multiple rows into a single value. Это полезно при суммировании количества продуктов, общего объема продаж или количества учащихся.This can be useful when summarizing the number of products offered, the total sales, or the count of students.

В этом примере мы группируем строки в набор данных по численности обучающихся в образовательных учреждениях.In this example, we group rows in an education enrollment data set. Данные получены из книги Excel и сформированы в редакторе запросов, чтобы извлечь только нужные столбцы, переименовать таблицу и выполнить несколько других преобразований.The data is from an Excel workbook, and has been shaped in Query Editor to get just the columns we need, rename the table, and perform a few other transforms.

Давайте выясним, сколько учреждений (в том числе школьных округов и других образовательных учреждений, таких как региональные службы и т. д.) имеется в каждом штате.Let’s find out how many Agencies (this includes school districts, and other education agencies such as regional service districts, and so on) each state has. Выберите столбец State Abbr (Сокращение для названия штата), затем нажмите кнопку Группировать по на вкладке Преобразование или Главная на ленте (кнопка Группировать по имеется на обеих вкладках).We select the State Abbr column then select the Group By button in the Transform tab or the Home tab of the ribbon (Group By is available in both tabs).

Откроется окно Группировать по.The Group By… .window appears. Когда редактор запросов группирует строки, он создает новый столбец, в который помещаются результаты выполнения команды Группировать по .When Query Editor groups rows, it creates a new column into which it places the Group By results. Скорректировать операцию Группировать по можно следующими способами.You can adjust the Group By operation in the following ways:

  1. Группировать по — это столбец, по которому требуется выполнить группирование. Редактор запросов выбирает выделенный столбец, но его можно изменить в этом окне на любой столбец в таблице.Group by – this is the column to be grouped; Query Editor chooses the selected column, but you can change it in this window to be any column in the table.
  2. Имя нового столбца — редактор запросов автоматически предлагает имя для нового столбца с учетом операции, которая применяется к группируемому столбцу, однако этому новому столбцу можно присвоить любое имя.New column name – Query Editor suggests a name for the new column, based on the operation it applies to the column being grouped, but you can name the new column anything you want.
  3. Операция — здесь можно указать операцию, которую применяет редактор запросов.Operation – here you specify the operation that Query Editor applies.
  4. Добавление группирования и Добавление агрегирования — эти параметры отображаются, если выбран параметр Дополнительно.Add grouping and Add aggregation – these options appear after selecting the Advanced option. Можно выполнять операции группирования (действия Группировать по) с несколькими столбцами, а также выполнять несколько операций агрегирования — и все это можно делать в окне Группировать по в рамках одной операции.You can perform grouping operations (Group By actions) on multiple columns, and perform multiple aggregations, all within the Group By window, and all in one operation. Редактор запросов создает новый столбец (с учетом выбранных в этом окне значений), который работает с несколькими столбцами.Query Editor creates a new column (based on your selections in this window) that operates on multiple columns.

Нажмите кнопку Добавление группирования или Добавление агрегирования, чтобы добавить группирования или агрегаты в операцию Группировать по.Select the Add grouping or Add aggregation button to add more groupings or aggregations to a Group By operation. Можно удалить группирование или агрегат, щелкнув ... (многоточие) и выбрав пункт Удалить. Попробуйте сделать это сейчас, чтобы увидеть, что произойдет.You can remove a grouping or aggregation by selecting the ... icon and selecting Delete, so go ahead and try it and see what it looks like.

Когда мы нажимаем кнопку ОК, запрос выполняет операцию Группировать по и возвращает результаты.When we select OK, Query performs the Group By operation, and returns the results. Итак, взглянем: сейчас в штатах Огайо, Техас, Иллинойс и Калифорния более тысячи образовательных учреждений в каждом!Whew, look at that – Ohio, Texas, Illinois, and California now each have over a thousand agencies!

C помощью редактора запросов всегда можно удалить последнюю операцию формирования, щелкнув X рядом с только что завершенным действием.And with Query Editor, you can always remove the last shaping operation by selecting the X next to the step just completed. Поэтому действуйте! Экспериментируйте, повторяйте этот шаг до тех пор, пока редактор запросов не сформирует данные так, как нужно вам.So go ahead and experiment, redo the step if you don’t like the results, until Query Editor shapes your data just the way you want it.

Сводные столбцы.Pivot columns

С помощью Power BI Desktop можно сводить столбцы и создавать таблицу, которая содержит агрегированные значения для каждого уникального значения в столбце.With Power BI Desktop, you can pivot columns, and create a table that contains aggregated values for each unique value in a column. Например, если нужно знать, сколько разных продуктов в каждой категории продуктов, можно быстро создать таблицу, которая позволяет решить именно эту задачу.For example, if you need to know how many different products you have in each product category, you can quickly create a table the does precisely that.

Рассмотрим следующий пример.Let’s look at an example. Таблица Продукты сформирована так, что в ней отображаются только уникальные продукты (по имени) и указано, к какой категории продуктов они относятся.The following Products table has been shaped to only show each unique product (by name), and which category each product falls under. Чтобы создать новую таблицу, которая показывает количество продуктов в каждой категории (на основе столбца CategoryName ), выберите столбец, а затем щелкните Сводный столбец на вкладке Преобразование на ленте.To create a new table that shows a count of products for each category (based on the CategoryName column), select the column, then select Pivot Column from the Transform tab on the ribbon.

Отобразится окно Столбец сведения, в котором можно узнать, какие значения столбца будут использованы для создания новых столбцов (1). Если развернуть раздел Дополнительные параметры (2), можно выбрать функцию, которая будет применяться к агрегированным значениям (3).The Pivot Column window appears, letting you know which column’s values will be used to create new columns (1), and when you expand Advanced options (2), you can select the function that will be applied to the aggregated values (3).

При нажатии кнопки ОКзапрос отображает таблицу в соответствии с инструкциями преобразования, предоставленными в окне Сводный столбец .When you select OK, Query displays the table according to the transform instructions provided in the Pivot Column window.

Создание настраиваемых столбцов.Create custom columns

В редакторе запросов можно создавать настраиваемые формулы, которые работают в нескольких столбцах таблицы, а затем поместить результаты этих формул в новый (настраиваемый) столбец.In Query Editor you can create custom formulas that operate on multiple columns in your table, then place the results of such formulas into a new (custom) column. Редактор запросов упрощает создание настраиваемых столбцов.Query Editor makes it easy to create custom columns.

В редакторе запросов выберите Настраиваемый столбец на вкладке ленты Добавление столбца.In Query Editor, select Custom Column from the Add Column tab on the ribbon.

Появляется следующее окно.The following window appears. В следующем примере создается настраиваемый столбец с именем Процент ИАЯ , который вычисляет процент всех студентов, изучающих английский язык.In the following example, we create a custom column called Percent ELL that calculates the percentage of total students that are English Language Learners (ELL).

Как и любое другое действие, выполненное в редакторе запросов, если новый настраиваемый столбец не предоставляет нужных данных, можно просто удалить выполненное действие в разделе Примененные действия области Параметры запроса щелкнув X рядом с действием Добавлен настраиваемый столбец .Like any other applied step in Query Editor, if the new custom column doesn’t provide the data you’re looking for, you can simply delete the step from the Applied Steps section of the Query Settings pane by selecting the X next to the Added Custom step.

Формулы запросов.Query formulas

Можно изменять создаваемые редактором запросов действия и создавать пользовательские формулы, чтобы точно контролировать подключение к данным и формирование данных.You can edit the steps that Query Editor generates, and you can create custom formulas to get precise control over connecting to and shaping your data. Всякий раз когда редактор запросов выполняет то или иное действие с данными, формула, связанная с этим действием, отображается в элементе Строка формул.Whenever Query Editor performs an action on data, the formula associated with the action is displayed in the Formula Bar. Для просмотра элемента Строка формулустановите флажок рядом с элементом Строка формул на вкладке ленты Вид .To view the Formula Bar, select the checkbox next to Formula Bar in the View tab of the ribbon.

Редактор запросов сохраняет все примененные действия по каждому запросу как текст, который можно просмотреть или изменить.Query Editor keeps all applied steps for each query as text that you can view or modify. С помощью средства Расширенный редактор, которое отображается при выборе элемента Расширенный редактор на вкладке ленты Вид , можно посматривать или изменять текст любого запроса.You can view or modify the text for any query using the Advanced Editor, which is displayed when you select Advanced Editor from the View tab of the ribbon.

Здесь представлен Расширенный редактор с действиями запроса, связанными с отображаемым запросом USA_StudentEnrollment.Here's a look at the Advanced Editor, with the query steps associated with the USA_StudentEnrollment query displayed. Эти действия создаются на языке формул Power Query, который часто называют M. Дополнительные сведения см. в разделе Сведения о формулах Power Query.These steps are created in the Power Query Formula Language, often referred to as M. For information, see Learn about Power Query formulas. См. дополнительные сведения о спецификации языка формул Microsoft Power Query для Excel.To view the language specification itself, see Microsoft Power Query for Excel Formula Language Specification.

Power BI Desktop предоставляет широкий набор категорий формул.Power BI Desktop provides an extensive set of formula categories. Дополнительные сведения и полный перечень всех формул редактора запросов см. в статье Категории формул Power Query.For more information, and a complete reference of all Query Editor formulas, visit Power Query Formula Categories.

Ниже перечислены категории формул для редактора запросов.The formula categories for Query Editor are the following:

  • ЧисловыеNumber
    • КонстантыConstants
    • ИнформационныеInformation
    • Преобразование и форматированиеConversion and formatting
    • ФорматFormat
    • ОкруглениеRounding
    • ОперацииOperations
    • СлучайныеRandom
    • ТригонометрическиеTrigonometry
    • БайтовыеBytes
  • ТекстовыеText
    • ИнформационныеInformation
    • Текстовые сравненияText comparisons
    • ИзвлечениеExtraction
    • ИзменениеModification
    • ЧленствоMembership
    • ПреобразованияTransformations
  • ЛогическиеLogical
  • ДатыDate
  • ВремениTime
  • DateTimeDateTime
  • DateTimeZoneDateTimeZone
  • ДлительностьDuration
  • ЗаписейRecord
    • ИнформационныеInformation
    • ПреобразованияTransformations
    • ВыборSelection
    • СериализацияSerialization
  • СписокList
    • ИнформационныеInformation
    • ВыборSelection
    • ПреобразованиеTransformation
    • ЧленствоMembership
    • Операции SetSet operations
    • УпорядочениеOrdering
    • Средние значенияAverages
    • ДобавлениеAddition
    • Числовые значенияNumerics
    • ГенераторыGenerators
  • ТабличныеTable
    • Конструирование таблицTable construction
    • ПреобразованияConversions
    • ИнформационныеInformation
    • Операции со строкамиRow operations
    • Операции со столбцамиColumn operations
    • ЧленствоMembership
  • ЗначенияValues
  • Арифметические операцииArithmetic operations
  • Типы параметровParameter Types
  • МетаданныеMetadata
  • Доступ к даннымAccessing data
  • URIURI
  • Двоичные форматыBinary formats
    • Чтение чиселReading numbers
  • ДвоичныеBinary
  • СтроковыеLines
  • ВыражениеExpression
  • ФункцияFunction
  • ОшибкаError
  • Функции сравненияComparer
  • Функции разделенияSplitter
  • Функции объединенияCombiner
  • Функции заменыReplacer
  • ТипType

Дальнейшие действияNext steps

Power BI Desktop предоставляет широкие возможности.There are all sorts of things you can do with Power BI Desktop. Дополнительные сведения об этих возможностях см. в следующих ресурсах.For more information on its capabilities, check out the following resources: