какое количество подключений к данным доступно в power query
Что такое Power Query?
Power Query — это преобразование данных и подсистема подготовки данных. Power Query поставляется с графическим интерфейсом для получения данных из источников и редактором Power Query для применения преобразований. Так как ядро доступно во многих продуктах и службах, место хранения данных зависит от того, где будет использоваться Power Query. С помощью Power Query можно выполнить обработку данных для извлечения, преобразования и загрузки (ETL).
схема с символьными источниками данных справа, передача Power query для преобразования, а затем переход к различным назначениям, таким как Azure Data Lake Storage, инверсия, Microsoft Excel или Power BI.
Как Power Query помогает при получении данных
Бизнес-пользователи тратят до 80 % времени на подготовку данных, что откладывает работу по анализу и принятии решений. В этом случае возникает несколько трудностей, и Power Query помогает решить многие из них.
| Существующий запрос | Как Power Query справку? |
|---|---|
| Поиск данных и подключение к ним слишком сложны | Power Query обеспечивает подключение к широкому спектру источников данных, включая данные всех размеров и фигур. |
| Возможности подключения к данным слишком фрагментированы | Согласованность опыта работы и обеспечение целостности возможностей запросов по всем источникам данных. |
| Данные часто необходимо переформировать до использования | Очень интерактивный и интуитивно понятный интерфейс для быстрого и итеративного создания запросов к любому источнику данных любого размера. |
| Любое формирование является одноразовым и неповторяемым | При использовании Power Query для доступа к данным и их преобразования вы определяете повторяемый процесс (запрос), который можно легко обновить в будущем для получения актуальных данных. В случае, когда необходимо изменить процесс или запрос для учета базовых данных или изменений схемы, можно использовать те же интерактивные и интуитивно понятные возможности, которые использовались при первоначальном определении запроса. |
| Объем (размер данных), скорость (скорость изменения) и разнообразные (Широта источников данных и фигур данных) | Power Query предлагает возможность работать с подмножеством всего набора данных для определения необходимых преобразований данных, что позволяет легко фильтровать данные и преобразовывать их в управляемый размер. Power Query запросы могут обновляться вручную или путем использования возможностей запланированного обновления в конкретных продуктах (например, Power BI) или даже программно (с помощью Excel объектной модели). Поскольку Power Query предоставляет возможность подключения к сотням источников данных и более 350 различных типов преобразований данных для каждого из этих источников, можно работать с данными из любого источника и из любой фигуры. |
Power Queryные возможности
Power Query взаимодействие с пользователем осуществляется с помощью пользовательского интерфейса редактора Power Query. Цель этого интерфейса — помочь вам применить необходимые преобразования, просто взаимодействуя с удобным для пользователя набором лент, меню, кнопками и другими интерактивными компонентами.
Редактор Power Query — это основной процесс подготовки данных, позволяющий подключаться к широкому спектру источников данных и применять сотни различных преобразований данных путем предварительного просмотра данных и выбора преобразований из пользовательского интерфейса. Эти возможности преобразования данных являются общими для всех источников данных, независимо от ограничений базовых источников данных.
При создании нового шага преобразования путем взаимодействия с компонентами интерфейса Power Query Power Query автоматически создает код M, необходимый для преобразования, поэтому вам не нужно писать какой-либо код.
В настоящее время доступны два Power Queryные возможности:
Несмотря на то, что существует два Power Queryных опыта, они предоставляют практически одинаковый пользовательский интерфейс в каждом сценарии.
Преобразования
Модуль преобразования в Power Query включает множество встроенных функций преобразования, которые можно использовать в графическом интерфейсе редактора Power Query. Эти преобразования могут быть простыми, например удалением столбца или фильтрацией строк, или, как правило, при использовании первой строки в качестве заголовка таблицы. Существуют также дополнительные параметры преобразования, такие как слияние, добавление, группирование, сведение и отмена свертывания.
Все эти преобразования становятся возможными благодаря выбору параметра преобразование в меню, а затем применению параметров, необходимых для этого преобразования. На следующем рисунке показаны некоторые преобразования, доступные в редакторе Power Query.
Потоки данных
Power Query могут использоваться во многих продуктах, таких как Power BI и Excel. Однако использование Power Query в рамках продукта ограничивает его использование только этим конкретным продуктом. Потоки данных — это зависящая от продукта версия службы Power Query интерфейсе, которая выполняется в облаке. используя потоки данных, можно получать данные и преобразовывать данные таким же образом, но вместо отправки выходных данных в Power BI или Excel можно сохранить выходные данные в других вариантах хранения, таких как «инверсия» или «Azure Data Lake Storage. Таким образом, можно использовать выходные данные потоков данных в других продуктах и службах.
Язык формул Power Query M
В любом сценарии преобразования данных существуют некоторые преобразования, которые не могут быть выполнены наилучшим образом с помощью графического редактора. Для некоторых из этих преобразований могут потребоваться специальные настройки и параметры, которые в настоящее время не поддерживаются графическим интерфейсом. Ядро Power Query использует язык сценариев в фоновом режиме для всех Power Query преобразований: язык формул Power Query M, также известный как M.
Язык M — это язык преобразования данных Power Query. Все, что происходит в запросе, в конечном итоге написано в M. Если вы хотите выполнять расширенные преобразования с помощью подсистемы Power Query, можно использовать Расширенный редактор для доступа к скрипту запроса и изменить его по своему мере. Если вы обнаружите, что функции и преобразования пользовательского интерфейса не будут выполнять нужные изменения, используйте Расширенный редактор и язык M для точной настройки функций и преобразований.
Где можно использовать Power Query?
В следующей таблице перечислены продукты и службы Майкрософт, где можно найти Power Query.
Начало работы в Power Query
Power Query — это инструмент для продвинутого бизнес-анализа, предназначенный для подключения к источникам данных и их преобразования.
Power Query в меню Excel
В зависимости от того, какая у вас версия Excel, вид надстройки Power Query может выглядеть по-разному. В Excel 2010 и 2013 надстройка появляется в виде отдельной вкладки «Power Query» (если у вас такой вкладки нет, прочитайте, как ее установить ).
В Excel после 2016 года Power Query уже встроен по умолчанию и находится в меню Данные → раздел Получить и преобразовать данные (в некоторых версиях Excel этот раздел называется Скачать & преобразовать).
Работа с данными в Power Query
Таким образом, Power Query – это полноценный ETL-инструмент (Extract, Transform, Load).
Подключение к данным в Power Query
В Power Query можно подключать данные из самых разных источников: таблицы в самом файле и других Excel-файлах, текстовые/csv файлы, папки, базы данных, источники в интернете, файлы xml и json, pdf-файлы, данные из канала OData и так далее. А также загрузить данные из Power BI и написать запрос с нуля – Пустой запрос.
Чтобы посмотреть, какие именно источники данных доступны в Excel, перейдите на вкладку Данные → Получить данные (или Создать запрос, если у вас не новая версия Excel).
Для примера добавим в Power Query данные из таблицы.
— в Excel 2010 и 2013: вкладка Power Query → Из таблицы (или С листа).
— для Excel после 2016: меню Данные → Из таблицы (Из таблицы/диапазона).
В открывшемся окне поставьте галочку «Таблица с заголовками».
Таблица с данными при этом превратится в «умную» smart-таблицу.
Чтобы открыть список запросов, нажмите в меню Данные → Запросы и подключения. В открывшейся вкладке «Запросы и подключения» отобразится список всех запросов, созданных в файле.
Редактор запросов Power Query
Разберем подробнее интерфейс редактора запросов Power Query.
Если окно редактора у вас закрыто, откройте его в меню Данные → Получить данные → Запустить редактор запросов. Или щелкните 2 раза мышкой по названию запроса на вкладке Запросы и подключения.
Итак, в редакторе Power Query есть:
Преобразование данных
Посмотрим на простом примере, как преобразовать данные в Power Query.
Допустим, у нас есть таблица с выручкой и расходами по городам за несколько лет. В таблице эти показатели разделены на две группы. Столбец с городами тоже имеет группировки (смотрите рисунок).
Если вы знакомы со сводными таблицами, то знаете, что построить сводную на основе таких данных не получится. Привести их в «нужный вид» можно в Power Query буквально за несколько щелчков мышкой:
При создании запроса Power Query сам автоматически записывает его шаги. Их можно увидеть в области справа Параметры запроса → Примененные шаги.
Шаги запроса можно редактировать, выбирая мышкой (таблица в области предварительного просмотра при этом тоже изменится). Ненужные шаги удаляются при нажатии на «крестик». Можно добавлять новые шаги в середину запроса или менять их местами, перемещая мышкой.
Обработка данных в Power Query выполняется последовательно, шаг за шагом, и каждое последующее действие использует результаты предыдущего. Поэтому при добавлении новых шагов или изменении их последовательности обязательно проверьте, все ли в порядке со следующими операциями. Проверить, все ли в порядке, можно, нажав на самый нижний шаг.
Кроме простых операций с данными, Power Query умеет выполнять и другие действия: сортировать, фильтровать, заменять, группировать, заполнять пустые значения, удалять дубликаты, работать с текстом и числами, выполнять простые вычисления, транспонировать таблицы и разворачивать их столбцы, объединять данные и многое-многое другое.
Power Query: стероиды для MS Excel и Power BI
В данной статье я хочу рассказать о некоторых возможностях бесплатной и крайне полезной, но пока еще мало известной надстройки над MS Excel под названием Power Query.
Power Query позволяет забирать данные из самых разных источников (таких как csv, xls, json, текстовых файлов, папок с этими файлами, самых разных баз данных, различных api вроде Facebook opengraph, Google Analytics, Яндекс.Метрика, CallTouch и много чего еще), создавать повторяемые последовательности обработки этих данных и загружать их внутрь таблиц Excel или самого data model.
И вот под катом вы можете найти подробности всего этого великолепия возможностей.
Совместимость и технические подробности
Power Query доступен бесплатно для всех версий Windows Excel 2010, 2013 и встроен по умолчанию в Windows Excel 2016. Для пользователей MacOS X Power Query недоступен (впрочем, даже без этого маковский Excel отвратителен на ощупь и продвинутые пользователи, включая меня, чаще всего работают с нормальным Excel через Parallels или запуская его на удаленной виндовой машинке).
Также, Power Query встроен в новый продукт для бизнес аналитики — Power BI, а еще, ходят слухи, что Power Query будет появляться и в составе других продуктов от Microsoft. Т.е. Power Query ждет светлое будущее и самое время для адептов технологий Microsoft (и не только) заняться его освоением.
Как оно работает
После установки Power Query в интерфейсе Excel 2010–2013 появляется отдельная одноименная вкладка.
В новом Excel 2016 функционал Power Query доступен на вкладке Data (данные), в блоке “Get & Transform”.
Сначала, в интерфейсе Excel мы выбираем конкретный источник данных, откуда нам их нужно получить, и перед нами открывается окошко самого Power Query с предпросмотром первых строчек загруженных данных (область 1). В верхней части окошка располагается Ribbon с командами по обработке данных (область 2). И в правой части экрана (область 3) у нас расположена панель с последовательностью всех действий, которые применяются к данным.
Возможности Power Query
У Power Query очень много возможностей и я хочу остановиться на некоторых из числа моих любимых.
Как я уже писал выше, Power Query замечателен тем, что позволяет подключаться к самым разным источникам данных. Так он позволяет загружать данные из CSV, TXT, XML, json файлов. Притом процесс выбора опций загрузки тех-же CSV файлов гибче и удобнее, чем он реализован штатными средствами Excel: кодировка автоматически выбирается часто правильно и можно указать символ разделителя столбцов.
Объединение файлов лежащих в папке
Power Query умеет забирать данные из указанной папки и объединять их содержимое в единые таблицы. Это может быть полезно, например, если вам периодически приходят какие-то специализированные отчеты за отдельный промежуток времени, но данные для анализа нужны в общей таблице. Гифка
Текстовые функции
Числовые функции
К столбцам с числовыми значениями по нажатию на кнопки на Ribbon можно применять:
Функции для работы с датами, временем и продолжительностью
К столбцам со значениями даты и времени по нажатию на кнопки на Ribbon можно применять:
Unpivot — Pivot
В интерфейсе Power Query есть функция “Unpivot”, которая в один клик позволяет привести данные с одной метрикой разложенные по столбцам по периодам к форме, которая будет удобна для использования в сводных таблицах (понимаю что трудно написал — смотрите пример). Также, есть функция с обратным действие Pivot. Гифка
Функция ВПР (VLOOKUP) одна из наиболее используемых функций в MS Excel. Она позволяет подтягивать данные в одну таблицу из другой таблицы по единому ключу. И вот как раз для этой функции в Power Query есть гораздо более удобная альтернатива — операция Merge. При помощи этой операции соединение таблиц нескольких таблиц в одну по ключу (по простому или по составному ключу, когда соответствие нужно находить по нескольким столбцам) выполняется буквально в 7 кликов мыши без ввода с клавиатуры.
Операция Merge — это аналог join в sql, и ее можно настроить чтобы join был разных типов — Inner (default), Left Outer, Right Outer, Full Outer.
Upd.Мне тут подсказали, что Power Query не умеет делать Aproximate join, а впр умеет. Чистая правда, из коробки альтернатив нет. Гифка
Подключение к различным базам данных. Query Folding.
Power Query также замечателен тем, что умеет цепляться к самым разным базам данных — от MS SQL и MySQL до Postgres и HP Vertica. При этом, вам даже не нужно знать SQL или другой язык базы данных, т.к. предпросмотр данных отображается в интерфейсе Power Query и все те операции, которые выполняются в интерфейсе прозрачно транслируются в язык запросов к базе данных.
А еще в Power Query есть понятие Query Folding: если вы подключены к совместимой базе данных (на текущий момент это MS SQl), то тяжелые операции по обработке данных Power Query будет стараться выполнить на серверной стороне и забирать к себе лишь обработанные данные. Эта возможность радикально улучшает быстродействие многих обработок.
Язык программирования “М”
Надстройка Power Query — это интерпретатор нового, скриптового, специализированного для работы с данными, языка программирования М.
На каждое действие, которое мы выполняем с данными в графическом интерфейсе Power Query, в скрипт у нас пишется новая строчка кода. Отражая это, в панели с последовательностью действий (область 3), создается новый шаг с говорящим названием. Благодаря этому, используя панель с последовательностью действий, мы всегда можем посмотреть как выглядят у нас данные на каждом шаге обработки, можем добавить новые шаги, изменить настройки применяемой операции на конкретном шаге, поменять их порядок или удалить ненужные шаги. Гифка
Также, мы всегда можем посмотреть и отредактировать сам код написанного скрипта. И выглядеть будет он примерно так:
Язык M, к сожалению, не похож ни на язык формул в Excel, ни на MDX и, к счастью, не похож на Visual Basic. Однако, он очень прост в изучении и открывает огромные возможности по манипуляции данными, которые недоступны с использованием графического интерфейса.
Загрузка данных из Яндекс.Метрики, Google Analytics и прочих Api
Немного овладев языком “M” я смог написать программки в Power Query, которые умеют подключаться к API Яндекс.Метрики и Google Analytics и забирать оттуда данные с задаваемыми настройками. Программки PQYandexMetrika и PQGoogleAnalytics я выложил в опенсорс на гитхаб под лицензией GPL. Призываю пользоваться. И я буду очень рад, если эти программы будут дорабатываться энтузиастами.
Для Google Analytics подобного рода экспортеров в разных реализациях достаточно много, но вот для Яндекс.Метрики, насколько я знаю, мой экспортер был первым публично доступным, да еще и бесплатным 🙂
Power Query умеет формировать headers для post и get запросов и забирать данные из интернета. Благодаря этому, при должном уровне сноровки, Power Query можно подключить практически к любым API. В частности, я для своих исследований дергаю данные по телефонным звонкам клиентов из CallTouch API, из API сервиса по мониторингу активности за компьютером Rescuetime, занимаюсь парсингом нужных мне веб-страничек на предмет извлечения актуальной информации.
Еще раз про повторяемость и про варианты применения
Как я уже писал выше, скрипт Power Query представляет собой повторяемую последовательность манипуляций, применяемых к данным. Это значит, что однажды настроив нужную вам обработку вы сможете применить ее к новым файлам изменив всего один шаг в скрипте — указав путь к новому файлу. Благодаря этому можно избавиться от огромного количества рутины и освободить время для продуктивной работы — анализа данных.
Я занимаюсь веб-аналитикой и контекстной рекламой. И так уж получилось, что с момента, как я познакомился с Power Query в ее интерфейсах я провожу больше времени, чем в самом Excel. Мне так удобнее. Вместе с тем возросло и мое потребление другой замечательной надстройки в MS Excel — PowerPivot.
Вот bi систему, про которую я рассказывал на Yac/M 2015 (видео) я делал полнстью при помощи Power Query и загружал данные внутрь PowerPivot.
Пару слов про локализацию
На сайте Microsoft для пользователей из России по умолчанию скачивается Power Query с переведенным на русским язык интерфейсом. К счастью, локализаторы до перевода на русский языка программирования (как это сделано с языком формул в excel) не добрались, однако жизнь пользователям неоднозначными переводами сильно усложнили. И я призываю вас скачивать, устанавливать и пользоваться английской версией Power Query. Поверьте, она будет гораздо понятнее.
Надстройки Power Query и Power Pivot
Power Query и Power Pivot — надстройки Excel для легкой работы с тяжелыми файлами. С помощью Power Query можно подключать и обрабатывать источники информации произвольного вида, а в Power Pivot – выполнять сложные расчеты и создавать модель данных.
Надстройка Power Query
Появление Power Query – одно из важных событий в истории развития Excel. То, что раньше пользователи делали в Excel с помощью рутинных операций по преобразованию данных, писали огромные формулы или макросы на VBA, теперь можно выполнять буквально в несколько щелчков мышкой.
Power Query – это надстройка для создания запросов по импорту, очистке и преобразованию данных.
Как работает
В привычном нам виде Power Query впервые появился в Excel 2013. В Excel 2010 и 2013 надстройка отображается в виде отдельной вкладки «Power Query».
В Excel 2016 надстройка уже встроена по умолчанию, на вкладке Данные – раздел «Скачать и преобразовать».
В версии Excel 2019 на вкладке Данные разделы с Power Query — «Получить и преобразовать данные» и «Запросы и подключения».
Возможности Power Query
Операции по преобразованию данных выполняются в окне редактора запросов.
В редакторе Power Query пользователь работает с данными – выполняет преобразования, а его действия автоматически записываются в виде шагов запроса.
Как скачать Power Query
Надстройка устанавливается бесплатно и доступна для Excel начиная с версий 2010 года:
Надстройка Power Pivot
Power Pivot — это надстройка Excel, в которой можно выполнять улучшенные вычисления для сводных таблиц с помощью DAX-формул. Power Pivot значительно расширяет функционал сводных таблиц и позволяет работать с большими объемами данных – в десятки миллионов строк.
Как работает
Возможности Power Pivot
Кстати, в Power Pivot есть свои инструменты для импорта данных, но из небольшого числа источников – некоторые базы данных, файлы Excel и текстовые и др. Эти инструменты можно найти на вкладке Главная. Так как доступных источников данных не очень много, то, как правило, в качестве источника для него выступает Power Query, функционал которого по подключению намного больше.
Увидеть все загруженные данные можно в нижней части окна Power Pivot – ярлычки с названиями таблиц находятся там же, где названия листов в «обычном» Excel.
Рекомендации по работе с Power Query
В этой статье содержатся советы и рекомендации по максимально эффективному использованию структурирование данных в Power Query.
Выбор правильного соединителя
Полный список доступных соединителей в Power Query см. в разделе соединители в Power Query.
Фильтрация на ранних этапах
Всегда рекомендуется фильтровать данные на ранних стадиях запроса или как можно раньше. Некоторые соединители будут использовать фильтры с помощью свертывания запросов, как описано в разделе Power Query свертывания запросов. Также рекомендуется отфильтровать любые данные, не относящиеся к вашему случаю. Это позволит лучше сосредоточиться на вашей задаче, отображая только данные, относящиеся к предварительной версии данных.
Можно использовать меню Автофильтр, в котором отображается отдельный список значений, найденных в столбце, чтобы выбрать значения для сохранения или фильтрации. Кроме того, панель поиска можно использовать для поиска значений в столбце.
Кроме того, можно воспользоваться специальными фильтрами для определенного типа, как в предыдущих столбцах даты, DateTime или даже даты.
Эти фильтры для конкретного типа помогают создать динамический фильтр, который всегда будет извлекать данные за последние x секунд, минут, часов, дней, недель, месяцев, кварталов или лет, как показано на следующем рисунке.
Дополнительные сведения о фильтрации данных на основе значений из столбца см. в разделе Фильтрация по значениям.
Проделать ресурсоемкие операции последними
Для выполнения определенных операций требуется чтение полного источника данных, чтобы возвращались результаты, и, таким же, будет выполняться слишком много времени для предварительного просмотра в редакторе Power Query. Например, если выполняется сортировка, то возможно, что первые несколько отсортированных строк находятся в конце исходных данных. Поэтому для возврата результатов операция сортировки должна сначала прочитать все строки.
Другие операции (например, фильтры) не требуют считывания всех данных перед возвратом результатов. Вместо этого они работают над данными в том виде, в котором они называются потоковой передачей. Данные передаются по потокам, а результаты возвращаются по своему пути. В редакторе Power Query такие операции должны считывать достаточное количество исходных данных для заполнения предварительной версии.
По возможности сначала выполните такие операции потоковой передачи, а затем выполните все более ресурсоемкие операции. Это позволит сократить время, затрачиваемое на ожидание подготовки к просмотру при каждом добавлении нового шага в запрос.
Временно работать с подмножеством данных
Если добавление новых шагов в запрос в редакторе Power Query выполняется слишком долго, сначала следует выполнить операцию «не выполнять первые строки» и ограничить количество строк, с которыми вы работаете. После добавления всех необходимых действий удалите шаг «не выполнять первые строки».
Использование правильных типов данных
Некоторые функции в Power Query являются контекстными для типа данных выбранного столбца. Например, при выборе столбца даты доступные параметры в группе столбцов даты и времени в меню Добавление столбца будут доступны. Но если для столбца не задан тип данных, эти параметры будут неактивны.
Аналогичная ситуация возникает для фильтров конкретного типа, так как они относятся к определенным типам данных. Если в столбце не определен правильный тип данных, то эти фильтры для конкретного типа будут недоступны.
Крайне важно всегда работать с правильными типами данных для столбцов. При работе с структурированными источниками данных, такими как базы данных, сведения о типах данных будут перенесены из схемы таблицы, найденной в базе данных. Но для неструктурированных источников данных, таких как TXT и CSV-файлы, важно задать правильные типы данных для столбцов, поступающих из этого источника данных. По умолчанию Power Query предлагает автоматическое обнаружение типов данных для неструктурированных источников данных. Вы можете ознакомиться с дополнительными сведениями об этой функции и о том, как она может помочь в работе с типами данных.
Дополнительные сведения о важности типов данных и способах работы с ними см. в разделе типы данных.
Изучение данных
Эти средства профилирования данных помогают лучше понять данные. Средства предоставляют небольшие визуализации, отображающие информацию по каждому столбцу, например:
Вы также можете взаимодействовать с этими функциями, которые помогут подготовить данные.
Дополнительные сведения о средствах профилирования данных см. в разделе средства профилирования данных.
Документирование работы
Рекомендуется документировать запросы путем переименования или добавления описания шагов, запросов или групп по своему усмотрению.
Хотя Power Query автоматически создает имя шага для вас на панели Примененные действия, можно также переименовать шаги или добавить описание к любому из них.
Дополнительные сведения о всех доступных компонентах и компонентах, найденных в области Примененные действия, см. в разделе Использование списка примененных действий.
Создание модульного подхода
Можно полностью создать один запрос, содержащий все преобразования и вычисления, которые могут потребоваться. Но если запрос содержит большое количество шагов, целесообразно разбить запрос на несколько запросов, где один запрос ссылается на следующий. Цель этого подхода — упростить и разделить этапы преобразования на небольшие части, чтобы их было проще понять.
Например, предположим, что у вас есть запрос с девятью шагами, показанными на следующем рисунке.
После этого появится диалоговое окно с предложением ввести имя для нового запроса. Это позволит эффективно разделить запрос на два запроса. Один запрос будет иметь все запросы перед слиянием. Другой запрос будет иметь начальный шаг, который будет ссылаться на новый запрос, и остальные шаги, которые были выполнены в исходном запросе, из шага объединить с таблицей цен вниз.
Можно также использовать ссылки на запросы в том виде, в котором они отображаются. Но рекомендуется размещать запросы на уровне, который на первый взгляд не кажется очень полезным с учетом множества шагов.
Дополнительные сведения о ссылках на запросы см. в разделе Общие сведения о панели запросов.
Создание групп
Хорошим способом сохранения работы является использование групп на панели «запросы».
Единственная цель групп — помочь вам организовать работу, выполнив в качестве папок для запросов. Вы можете создавать группы в группах, если вам когда-либо нужно. Перемещение запросов между группами так же просто, как и перетаскивание.
Попробуйте предоставить группам осмысленное имя, которое имеет смысл для вас и вашего случая.
Дополнительные сведения о всех доступных компонентах и компонентах, найденных в области запросы, см. в разделе Общие сведения о панели запросов.
Запросы будущего подтверждения
Создание запроса, который не будет иметь каких-либо проблем во время следующего обновления, является высшим приоритетом. В Power Query есть несколько функций, чтобы обеспечить устойчивость запроса к изменениям и возможность обновления даже при изменении некоторых компонентов источника данных.
Рекомендуется определить область запроса в соответствии с тем, что он должен делать, и о том, к чему он должен учитывать, с точки зрения структуры, макета, имен столбцов, типов данных и любого другого компонента, относящегося к области.
Ниже приведены некоторые примеры преобразований, которые могут помочь сделать запрос устойчивым к изменениям.
Дополнительные сведения о фильтрации данных по положению строки см. в разделе Фильтрация таблицы по положению строки.
Дополнительные сведения о выборе или удалении столбцов см. в разделе Выбор или удаление столбцов.
Дополнительные сведения о параметрах отмены свертывания столбцов см. в разделе Отмена свертываниястолбцов.
Если в запросе имеется шаг, который изменяет тип данных столбца, но некоторые ячейки выдают ошибки, так как значения не соответствуют требуемому типу данных, можно удалить строки, которые выдавали значения ошибок.
Дополнительные сведения о работе и работе с ошибками см. в разделе Работа с ошибками.
Использование параметров
Рекомендуется создавать динамические и гибкие запросы. Параметры в Power Query помогают сделать запросы более динамичными и гибкими. Параметр служит для простого хранения и управления значением, которое может многократно использоваться различными способами. Но чаще всего используется в двух сценариях:
Аргумент Step — Параметр можно использовать в качестве аргумента для нескольких преобразований, управляемых из пользовательского интерфейса.
Аргумент — пользовательской функции Можно создать новую функцию на основе запроса и указать параметры в качестве аргументов пользовательской функции.
Ниже приведены основные преимущества создания и использования параметров.
Многократное использование параметра в нескольких шагах или запросах.
Делает создание пользовательских функций простым и простым.
Можно даже использовать параметры в некоторых аргументах соединителей данных. например, можно создать параметр для имени сервера при подключении к базе данных SQL Server. затем можно использовать этот параметр в диалоговом окне SQL Server базы данных.
При изменении расположения сервера необходимо всего лишь обновить параметр для имени сервера, и ваши запросы будут обновлены.
Дополнительные сведения о создании и использовании параметров см. в разделе Использование параметров.
Создание многократно используемых функций
Если Вы найдетесь в ситуации, когда необходимо применить тот же набор преобразований к разным запросам или значениям, создайте Power Query настраиваемую функцию, которую можно использовать многократно, как можно более эффективно. Power Query пользовательской функцией является сопоставление набора входных значений с одним выходным значением и создается из собственных функций и операторов M.
Например, предположим, что имеется несколько запросов или значений, требующих одинакового набора преобразований. Можно создать пользовательскую функцию, которую позже можно будет вызвать для запросов или выбранных значений. Эта пользовательская функция экономит время и помогает управлять набором преобразований в центральном расположении, которое можно изменить в любой момент.
Power Query пользовательские функции можно создавать на основе существующих запросов и параметров. Например, представьте запрос, содержащий несколько кодов в виде текстовой строки, и необходимо создать функцию, которая будет декодировать эти значения.
Начните с параметра со значением, которое служит примером.
Из этого параметра вы создадите новый запрос, в котором будут применяться необходимые преобразования. В этом случае необходимо разделить код PTY-CM1090-слабый на несколько компонентов:
Затем можно преобразовать этот запрос в функцию, щелкнув запрос правой кнопкой мыши и выбрав создать функцию. Наконец, можно вызвать пользовательскую функцию в любой запрос или значения, как показано на следующем рисунке.
После нескольких преобразований можно увидеть, что вы достигли желаемого результата и использовали логику для такого преобразования из пользовательской функции.
Дополнительные сведения о создании и использовании пользовательских функций в Power Query из статьи пользовательские функции.