Меню

Редизайнер таблиц power query

Параметризация путей к данным в Power Query

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

Что же сделать в такой ситуации? Давайте рассмотрим этот случай подробнее на следующем примере.

Постановка задачи

Предположим, что у нас в папке E:\Отчеты по продажам лежит файл Топ-100 товаров.xls, представляющий собой выгрузку из нашей корпоративной базы данных или ERP-системы (1С, SAP и т.п.) Этот файл содержит информацию о наиболее популярных товарных позициях и выглядит внутри примерно так:

Исходные данные

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

Поэтому рядом с этим файлом в той же папке мы создаём ещё один новый файл Обработчик.xlsx, в котором создадим запрос Power Query, который будет загружать страшненькие данные из исходного файла-выгрузки Топ-100 товаров.xls, и приводить их в порядок:

Два исходных файла в папке

Создаем запрос к внешнему файлу

Открыв файл Обработчик.xlsx, выберем на вкладке Данные команду Получить данные — Из файла — Из книги Excel (Data — Get Data — From file — From Excel) , затем укажем местоположение исходного файла и нужный нам лист. Выбранные данные загрузятся в редактор Power Query:

Загруженные данные в Power Query

Приведём их в нормальный вид:

  1. Удалим пустые строки через Главная — Удалить строки — Удалить пустые строки (Home — Remove Rows — Remove Empty Rows) .
  2. Удалим ненужные 4 верхних строки через Главная — Удалить строки — Удалить верхние строки (Home — Remove Rows — Remove Top Rows) .
  3. Поднимем первую строку в шапку таблицы кнопкой Использовать первую строку в качестве заголовков на вкладке Главная (Home — Use first row as header) .
  4. Отделим пятизначный артикул от названия товара во втором столбце, используя команду Разделить столбец на вкладке Преобразование (Transform — Split Column) .
  5. Удалим ненужные столбцы и переименуем заголовки оставшихся для лучшей наглядности.

В итоге у нас должна получиться следующая, гораздо более приятная, картина:

Приведенный в порядок файл

Осталось эту облагороженную таблицу выгрузить обратно на лист в наш файл Обработчик.xlsx командой Закрыть и загрузить (Home — Close&Load) на вкладке Главная:

Выгруженные на лист результаты запроса

Находим путь к файлу в запросе

Теперь давайте посмотрим как выглядит наш запрос «под капотом», на встроенном в Power Query внутреннем языке с лаконичным названием «М». Для этого вернемся в наш запрос двойным щелчком по нему в правой панели Запросы и подключения и на вкладке Просмотр выберем Расширенный редактор (View — Advanced Editor) :

Находим путь к файлу в расширенном редакторе

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

Добавляем умную таблицу с путём к файлу

Закроем пока Power Query и вернёмся в наш файл Обработчик.xlsx. Добавим новый пустой лист и сделаем на нём маленькую «умную» таблицу, в единственной ячейке которой будет записан полный путь к нашему файлу исходных данных:

Умная таблица с путем к файлу данных

Для создания «умной» таблицы из обычного диапазона можно использовать сочетание клавиш Ctrl + T или кнопку Форматировать как таблицу на вкладке Главная (Home — Format as Table) . Заголовок столбца (ячейка А1) может быть совершенно любым. Также обратите внимание, что для понятности я дал таблице имя Параметры на вкладке Конструктор (Design) .

Скопировать из Проводника путь или даже ввести его вручную не представляет, конечно, особой сложности, но лучше всего минимизировать человеческий фактор и определять путь, по возможности, автоматически. Это можно реализовать с помощью стандартной функции рабочего листа Excel ЯЧЕЙКА (CELL) , которая умеет выдавать кучу полезной информации об указанной в качестве аргумента ячейке — в том числе и путь к текущему файлу:

Функция ЯЧЕЙКА

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

Находим путь формулой

или в английской версии:

. где функция ЛЕВСИМВ (LEFT) берёт из полной ссылки кусок текста до открывающей квадратной скобки (т.е. путь к текущей папке), а затем к нему приклеивается имя и расширение нашего исходного файла с данными.

Параметризуем путь в запросе

Остался последний и самый главный штрих — прописать в запросе путь к исходному файлу Топ-100 товаров.xls, сославшись на ячейку А2 нашей созданной «умной» таблицы Параметры.

Для этого вернемся в запрос Power Query и ещё раз откроем Расширенный редактор на вкладке Просмотр (View — Advanced Editor) . Вместо текстовой строки-пути в кавычках «E:\Отчеты по продажам\Топ-100 товаров.xlsx» введём туда вот такую конструкцию:

Вводим путь как параметр

Excel.CurrentWorkbook() <[Name="Параметры"]>[Content] <0>[Путь к исходным данным]

Давайте разберемся из чего она состоит:

  • Excel.CurrentWorkbook() — это функция языка М для обращения к содержимому текущего файла
  • <[Name="Параметры"]>[Content] — это уточняющий параметр к предыдущей функции, указывающий, что мы хотим получить содержимое «умной» таблицы Параметры
  • [Путь к исходным данным] — это имя столбца в таблице Параметры, к которому мы обращаемся
  • — это номер строки в таблице Параметры, из которой мы хотим взять данные. Шапка — не в счет и нумерация начинается от нуля, а не от единицы.

Вот и всё, собственно.

Осталось нажать на Готово и проверить как работает наш запрос. Теперь при пересылке всей папки с обоими файлами внутри на другой ПК запрос будет сохранять работоспособность и определять путь к данным автоматически.

Источник



Рекомендации по работе с Power Query

В этой статье содержатся советы и рекомендации по максимально эффективному использованию структурирование данных в Power Query.

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

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.

Фильтрация на ранних этапах

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

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

Меню автофильтра в Power Query.

Кроме того, можно воспользоваться специальными фильтрами для определенного типа, как в предыдущих столбцах даты, DateTime или даже даты.

для столбца дат введите специальный фильтр.

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

Находится в предыдущем фильтре для определенной даты.

Дополнительные сведения о фильтрации данных на основе значений из столбца см. в разделе Фильтрация по значениям.

Использование правильных типов данных

Некоторые функции в Power Query являются контекстными для типа данных выбранного столбца. Например, при выборе столбца даты доступные параметры в группе столбцов даты и времени в меню Добавление столбца будут доступны. Но если для столбца не задан тип данных, эти параметры будут неактивны.

Укажите конкретный параметр в меню Добавить столбец.

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

для столбца дат введите специальный фильтр.

Крайне важно всегда работать с правильными типами данных для столбцов. При работе с структурированными источниками данных, такими как базы данных, сведения о типах данных будут перенесены из схемы таблицы, найденной в базе данных. Но для неструктурированных источников данных, таких как TXT и CSV-файлы, важно задать правильные типы данных для столбцов, поступающих из этого источника данных. По умолчанию Power Query предлагает автоматическое обнаружение типов данных для неструктурированных источников данных. Вы можете ознакомиться с дополнительными сведениями об этой функции и о том, как она может помочь в работе с типами данных.

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

Изучение данных

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

Предварительный просмотр данных или средства профилирования данных в Power Query.

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

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

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

Параметры наведения на качество данных.

Дополнительные сведения о средствах профилирования данных см. в разделе средства профилирования данных.

Документирование работы

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

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

Область примененных действий с документированными шагами и описанием.

Дополнительные сведения о всех доступных компонентах и компонентах, найденных в области Примененные действия, см. в разделе Использование списка примененных действий.

Создание модульного подхода

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

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

Область примененных действий с документированными шагами и описанием.

Этот запрос можно разделить на два на шаге « объединить с таблицей цен ». Таким образом, проще понять шаги, которые были применены к запросу Sales перед слиянием. Чтобы выполнить эту операцию, щелкните правой кнопкой мыши шаг Слияние с таблицей цен и выберите пункт извлечь предыдущее .

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

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

Исходный запрос после действия

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

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

Создание групп

Хорошим способом сохранения работы является использование групп на панели «запросы».

Работа с группами в Power Query.

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

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

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

Запросы будущего подтверждения

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

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

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

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

Дополнительные сведения о фильтрации данных по положению строки см. в разделе Фильтрация таблицы по положению строки.

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

Дополнительные сведения о выборе или удалении столбцов см. в разделе Выбор или удаление столбцов.

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

Дополнительные сведения о параметрах отмены свертывания столбцов см. в разделе Отмена свертываниястолбцов.

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

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

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

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

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

Выберите параметр для аргумента преобразования.

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

Создание функции.

Ниже приведены основные преимущества создания и использования параметров.

Централизованное представление всех параметров с помощью окна Управление параметрами .

Управление окном параметров.

Многократное использование параметра в нескольких шагах или запросах.

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

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

диалоговое окно базы данных SQL Server с параметром для имени сервера.

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

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

Создание многократно используемых функций

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

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

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

Список кодов.

Начните с параметра со значением, которое служит примером.

Пример значения кода параметра.

Из этого параметра вы создадите новый запрос, в котором будут применяться необходимые преобразования. В этом случае необходимо разделить код PTY-CM1090-слабый на несколько компонентов:

  • Origin = Pty
  • Назначение = слабое место
  • Авиакомпании = cm
  • Флигхтид = 1090

Пример запроса преобразования.

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

Вызов пользовательской функции.

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

Окончательный выходной запрос после вызова пользовательской функции.

Дополнительные сведения о создании и использовании пользовательских функций в Power Query из статьи пользовательские функции.

Источник

Знакомство с Power Query на примере транспонирования Таблицы Excel

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

В Excel 2016 Power Query уже встроен в ядро программы. Команды управления запросами находятся во вкладке Данные, в группе Скачать и преобразовать (в английском варианте Get & Transform).

Power Query в Excel 2016

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

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

Кнопки Получение внешних данных в Excel

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

После появления Power Query в среде пользователей Excel произошло потрясение, сравнимое с появлением сводных таблиц. Это не шаг, а прыжок вперед, благодаря которому любой аналитик (и обычный пользователь Excel), имеющий дело с большими и обновляемыми данными из разных источников, может ускорить свою работу в десятки раз. Да, в десятки, если не в сотни. Ведь как раньше делался, скажем, отчет? Импортируются данные (из разных источников), очищаются, связываются вместе с помощью формул типа ВПР, затем делаются необходимые расчеты, все агрегируется с помощью сводных таблиц в краткий отчет. Периодически эти действия нужно повторять, т.к. традиционными методами (без VBA) очень трудно автоматизировать все шаги. Сегодня этому кошмару пришел конец. В Power Query достаточно один раз все настроить и далее все операции импорта, обработки и выгрузки данных повторяются нажатием одной кнопкой обновления.

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

Вкратце алгоритм работы Power Query таков:

1. импорт данных из выбранных источников данных

2. обработка полученных данных

Список возможных источников довольно разнообразный: от текстовых файлов до внешних баз данных и интернета. Также можно легко присоединиться к данным внутри самого MS Excel.

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

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

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

Таблица для транспонирования

В целом это не проблема, т.к. в Excel существует минимум два способа транспонирования.

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

Окно специальной вставки с опцией транспонирования

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

Второй способ транспонирования – воспользоваться функций ТРАНСП. Это формула массива, поэтому для ее вставки нужно вначале указать точный диапазон и ввести с помощью комбинации Ctrl + Shift + Enter.

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

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

Транспонирование таблицы средствами Power Query

Первым делом нужно сделать запрос на источник данных. Нас интересуют данные из этой же книги Excel. Power Query не видит адреса обычных ячеек, а только именованные диапазоны и Таблицы Excel. Как правило, используют Таблицы Excel. Для преобразования обычного диапазона в таблицу рекомендую горячую комбинацию клавиш Ctrl + T.

Создание Таблицы Excel из диапазона

Теперь активируем любую ячейку Таблицы с данными и нажимаем кнопку Данные – Скачать и преобразовать – Из таблицы.

Создание запроса из Таблицы Excel

Открывается окно редактирования Power Query.

Окно редактирования Power Query

Выглядит, как другая программа, но это только отдельное окно внутри Excel. Интерфейс состоит из пяти частей:

1. Инструменты редактирования – лента, на которой находятся команды Power Query.

2. Строка формул – здесь записывается код языка М для выделенного в данный момент шага обработки.

3. Запросы – скрываемая панель для навигации между запросами текущей книги.

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

5. Параметры запроса – панель с названием запроса (можно изменять) и перечнем созданных шагов, которые также можно редактировать.

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

Название запроса лучше всего изменить на более говорящее. Довольно часто в книге используют сразу несколько запросов, поэтому в них нужно ориентироваться. Назовем «Транспонирование».

Переименование запроса

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

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

Вид таблицы внутри Power Query

Обратим внимание, что заголовки перенеслись из заголовков Таблицы Excel, которую мы использовали в качестве источника. Однако транспонирование происходит без заголовков. Поэтому, чтобы избежать потери названий столбцов, «опустим» их названия в первую строку таблицы Преобразование – Таблица Использовать первую строку в качестве заголовков Использовать заголовки как первую строку.

Использовать заголовки как первую строку

Таблица с данными получит такой вид.

Таблица с заголовками в первой строке

Теперь можно транспонировать. Используем команду Преобразование – Транспонировать.

Команда транспонирования в Power Query

Таблица мгновенно изменяется.

Транспонированная таблица

Сделаем первую строку назад заголовками. Можно через Преобразование – Таблица — Использовать первую строку в качестве заголовков либо через кнопку в верхнем левом углу от таблицы.

Команда использования первой строки в качестве заголовков

Получим конечный результат обработки.

Транспонированная таблица

Задача решена. Все шаги преобразования данных записаны и видны справа.

Шаги преобразования в Power Query

Осталось измененные данные вернуть в Excel с помощью команды Главная – Закрыть – Закрыть и загрузить.

Закрыть и загрузить в Excel

Если ее нажать, то результат загрузится на новый лист эксель и будет представлять из себя Таблицу Excel с названием, как у запроса. Но давайте пока зайдем в раскрывающийся список, чтобы посмотреть опции выгрузки. В раскрывающемся списке выберем Закрыть и загрузить в… Откроется следующее окно.

Опции выгрузки данных из Power Query

Если выбрать Только создать соединение, выгрузки не произойдет. Такой вариант применяют, если требуется дальнейшая обработка или использование этого запроса. Для выгрузки в Excel можно выбрать Новый лист либо указать конкретный диапазон. Если установить галочку Добавить эти сведения в модель данных, то результат запроса даже без выгрузки в Excel можно будет использовать в модели данных или Power Pivot. Этот вариант позволяет обрабатывать миллионы (миллионы!) строк, т.к. на обработку данных в памяти требуется гораздо меньше ресурсов. Оставляем все по умолчанию и жмем Загрузить. В процессе выгрузки таблица имеет серенький цвет, а когда выгрузка завершена, становится зелененькой.

Выгруженная из Power Query Таблица Excel

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

Самое интересное происходит далее. Если добавить новые данные, то для повторения всех действий достаточно обновить запрос через правую кнопку в панели запросов (см. чуть ниже), либо во вкладке Данные – Подключения – Обновить все.

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

Добавление данных в источник

А теперь обновим запрос.

Это просто праздник какой-то! (с).

Обратим внимание, что справа в окне Excel появляется панель для управления существующими запросами.

Панель управления запросами Power Query

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

Управление заросом

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

Контекстное меню управления запросом

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

Изменить – команда открытия окна редактирования. Эквивалентно двойному нажатию левой кнопки мыши по самому запросу.

Обновить – обновление выбранного запроса (если нужно обновить только один запрос, а не все).

Загрузить в… – изменение места загрузки (в таблицу, модель или создания только соединения)

Дублировать – сделать копию выбранного запроса.

Другие команды не менее важны, но их рассмотрим в другой раз.

Панель Запросы книги можно закрыть или снова отобразить с помощью команды Данные – Скачать и преобразовать – Показать запросы.

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

Источник

Полный курс Power Query

На этой странице находятся ссылки на все уроки моего бесплатного курса по Power Query для Excel. Если вы изучите все уроки, то станете очень продвинутым пользователем Power Query. Желаю вам успехов в освоении Power Query!

Внимание. Старица находится в процессе разработки.

Введение

Во вводном модуле вы узнаете:

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

Основы

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

Урок Описание
Подключение Excel Подключаемся к файлам Excel. Импортируем данные из таблиц, именных диапазонов, динамических именных диапазонов.
Подключение CSV/TXT, таблиц, диапазонов Подключаемся к к файлам CSV/TXT, Excel
Подключение XML Подключаемся к источнику в формате XML
Объединение таблиц по горизонтали Учимся объединять таблицы по горизонтали — JOIN, merge
Виды объединения таблиц по горизонтали Изучаем виды объединения таблиц по горизонтали — LEFT JOIN, FULL JOIN, INNER JOIN, CROSS JOIN
Виды объединения таблиц по горизонтали 2 Изучаем анти-соединение и соединение таблицы с ней же самой — ANTI JOIN, SELF JOIN
Группировка Изучаем операцию группировки с агрегированием — GROUP BY
Объединить таблицы с агрегированием Объединить таблицы по горизонтали и сразу выполнить группировку с агрегированием — JOIN + GROUP BY
Объединить таблицы по вертикали Учимся объединять две таблицы по вертикали — combine
Объединение таблиц по вертикали, когда не совпадают заголовки столбцов Как объединить две таблицы по вертикали, если названия столбцов не совпадают
Объединить по вертикали все таблицы одной книги друг за другом Как объединить по вертикали все таблицы одной книги, находящиеся на разных листах Excel
Объединить по вертикали все файлы в папке Объединяем по вертикали таблицы, которые находятся в разных файлах в одной папке
Консолидация множества таблиц пользовательской функцией Объединяем по вертикали множество таблиц с предварительной обработкой при помощи пользовательской функции
Анпивот (Unpivot) Изучаем операцию Анпивот — из сводной таблицы делаем таблицу с данными
Многоуровневый анпивот (Анпивот с подкатегориями) Более сложный вариант Анпивота — в строках находится несколько измерений
Скученные данные Данные собраны в одном столбце, нужно правильно его разбить на несколько
Скученные данные 2 Разбираем еще один пример скученных данных
Создание параметра Мы можем ввести значение в какую-то ячейку Excel, а потом передать это значение в формулу Power Query
Таблица параметров Создадим целую таблицу параметров и будем их использовать в запросах Power Query
Поиск ключевых слов Научимся искать ключевые слова в текстовом поле
Поиск ключевых слов 2 Будем искать ключевые поля в текстовом поле и присваивать этому значению какую-то категорию
Ссылка на другую строку Как сослаться на другую строку
Ссылка на другую строку 2 Как сослаться на другую строку, используя объединение по горизонтали
Уникальные значения двух столбцов Как получить уникальные значения из двух столбцов
Деление на справочник и факт Разделим один датасет на два датасета: справочник и факт
Импорт из PDF, Импорт из множества PDF Научимся импортировать таблицы из одного PDF файла и из всех PDF файлов в папке

Формулы М

В этом модуле мы будем изучать язык формул Power Query или M Language.

Урок Описание
Введение 1 На примере поиска без учета регистра я показываю преимущества изучения формул Power Query
Введение 2 Знакомимся с основами языка формул М: ключевые слова let, in, разбираем как устроены функции
Введение 3 Зачем изучать язык формул, когда существует удобный пользовательский интерфейс
Объекты Power Query Знакомимся с основными объектами Power Query: Table, List, Record
Объект Table Изучаем объект Table — таблица
Объект Record Изучаем объект Record — запись, каждая строка таблицы — это запись
Объект List 1 Изучаем объект List — список, каждый столбец таблицы является списком
Объект List 2 Продолжаем изучать объект List — список
Ссылки 1 Как ссылаться на список и запись и отдельные элементы списка или записи
Ссылки 2 Решаем практическую задачу по объединению таблиц и отрабатываем ссылки на строки и столбцы таблицы
Ссылки 3 В процессе решения практической задачи отрабатываем ссылки на элементы записи и таблицы
Ссылки 4 Закрепляем навык делать ссылки на практическом примере из жизни
Работа с датой, временем и длительностью, #datetime, #duration Основы работы с датой и временем
Power Query аналоги текстовых функций Excel Изучаем аналоги текстовых Excel функций ЛЕВСИМВ, ПРАВСИМВ, ДЛСТР, НАЙТИ, ПСТР
Группировка, Пивот, Анпивот, Сортировка Выполняем знакомые операции, но уже без помощи пользовательского интерфейса, а ручным вводом формул
Вычисления в Power Query Вычисляем процент от общей суммы, процентное изменение относительно предыдущего дня, создаем рейтинг
Пользовательские функции Учимся создавать пользовательские функции на примере нарастающего итога
Агрегирование текста, группировка Выполняем группировку с агрегированием текстовых значений
Функция Table.TransformColumns Table.TransformColumns позволяет нам применить любую функцию преобразования к столбцу
Скользящее среднее в Power Query, List.Range Изучаем функцию List.Range на примере вычисления скользящего среднего
Разгруппировка, скрытые возможности Table.ReplaceValues Разгруппировка строки — разбить число на N равных частей; Вводим функцию внутри Table.ReplaceValues
Нюансы консолидации и List.PositionOf Нужно выполнить объединение по вертикали с предварительной обработкой, но количество лишних строк сверху всегда разное
Удалить лишние пробелы в текстовом столбце, Text.Split Изучим функцию Text.Split и удалим с помощью нее лишние пробелы
Обработка заголовков в двух строках, Функция List.Zip, Практика List.x, Record.x Каждый заголовок разбит на две строки, нужно превратить эти две строки в одну объединенную
Фильтрация таблицы списком Есть список интересующих нас категорий, нужно из таблицы выбрать только эти значения
Разделить столбец на строки, Расширенные возможности Text.Trim Разберем на практическом примере, что еще может чистить функция Text.Trim помимо лишних пробелов по краям
Нарастающий итог 2 Вычислим нарастающий итог функциями List.Range и List.Accumulate
Создание пользовательской функции Switch Повторим пользовательские функции на примере создания функции Switch
Информация о форматах ячеек, Чтение Zip файла Распакуем XLSX файл и получим данные о формате ячеек
Множественная текстовая замена с List.Generate Ищем определенный перечень текстовых фрагментов и заменяем на другой.
Минимум в диапазоне строк

Ошибки

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

Урок Описание
Введение Определимся, что мы будем назвать ошибками и ловушками
Как развернуть все столбцы В данные добавился новый столбец, но у вас он не разворачивается, потому что вы хардкодом прописали список для экстракта
Измененный тип, Неверная фильтрация в UI Запрос, который раньше работал, вдруг, работать перестал. Возможно, дело в ошибке «Измененный тип»; В результирующей таблице меньше строк, чем должно быть. Возможно, вы попали в ловушку неверной фильтрации
Неверное количество столбцов при импорте CSV Вы импортировали CSV файл, все было хорошо, но когда в источник добавились новые столбцы вы их не увидели в Power Query
Подключиться к последнему файлу Каждую неделю в сетевую папку для вас добавляют новый файл источник. Вам нужно автоматически подключаться только к самому свежему файлу
Консолидация и MissingField.Type Вы хотите выполнить массовое объединение таблиц по вертикали, но не во всех таблицах присутствует полный список столбцов. Что делать?
Автоматически удалить пустые столбцы В вашем источнике часто присутствуют лишние пустые столбцы; Научимся автоматически удалять их
Ошибка Formula.Firewall Разберем 2 способа избежать ошибки Formula.Firewall
Разные имена листов
Разные имена листов 2
Разные названия столбцов
Удалить лишние пробелы 2 Удалим лишние пробелы в текстовых столбцах с помощью функцию Text.SplitAny и Text.Combine
Доступ к веб-содержимому При подключении к веб-страницам постоянно появляется окно «Доступ к веб-содержимому»; Как от этого окна избавиться?
Импорт плохо структурированного TXT TXT источник плохо структурирован и столбцы не распознаются автоматически
Не совпадает сумма при округлении Выполнив округление общая сумма перестала совпадать на незначительную величину
Фиксировать ширину столбцов Excel Настроенная вами ширина столбцов Excel сбивается после каждого обновления запроса.

Веб-запросы

Разберем множество практических примеров подключения к веб-страницам и web api.

Урок Описание
Веб-запросы. Текстовый документ, веб-страница, практика List.Zip На одной веб-странице находятся данные без заголовков, а заголовки для нее на другой странице.
Веб-запросы. Неразмеченный текст Текс находится на веб-странице и он плохо размечен. Нужно преобразовать его в таблицу.
Веб-запросы. Многостраничное извлечение Как извлечь таблицу из множества веб-страниц и объединить их всех по вертикали.
Веб-запросы. Получение котировок Yahoo Finance Получим историю котировок любой акции с сайта Yahoo Finance.
Веб-запросы. JSON, получение котировок Yahoo Finance 2 Получим историю котировок любой акции при помощи API Yahoo Finance.
Веб-запросы. HTML, получить данные по облигациям Получим данные по облигациям из множества веб-страниц и все объединим в одну таблицу.
Получить котировки Московской биржи Получим историю котировок с сайта Московской биржи.
YouTube Data API Получить статистику по любому YouTube видео и каналу прямо на лист Excel.
Подключение к личному OneDrive Как подключиться к файлу или папке из личного OneDrive.
Подключиться к книге на Google Drive Как подключиться к файлу, который лежит в облаке Google Drive

Практика

Отработаем изученные функции на реальных практических примерах из жизни.

Урок Описание
Динамическая таблица дат Создаем саморасширяющуюся таблицу календаря
Продажи год назад Отработаем SELF JOIN на примере вычисления продаж прошлого года
Количество позиций в текстовой строке В ячейках столбца находится перечень товаров в чеке через точку с запятой; Сделаем так, чтобы каждая позиция находилась в своей отдельной строке
Консолидация листов и книг одновременно Объединим по вертикали все таблицы, находящиеся на разных листах разных книг Excel
Собрать разбитую строку Каждая строка данных разбита на несколько строк. Нужно привести данные в порядок.
Обработка типичной выписки Отработаем несколько приемов, обработав банковскую выписку.
Прирост населения Китая На примере анализа прироста населения Китая отработаем несколько приемов.
Нужные столбцы нужной книги Как выбрать нужную книгу среди множества и из этой книги получить только определенные столбцы.
Объединить разбитую строку Ряды данных разбиты на части. Нужно правильно соединить ряды обратно.
Группировка, пивот, анпивот, условия Практическое применение группировки, пивота, анпивота и условной логики
Процент от суммы по категории в Power Query и в Power Pivot Учимся вычислять процент от общей суммы в Power Query и в Power Pivot.
Выполнить пивот и пронумеровать столбцы Делаем пивот и столбцы автоматически пронумеровываем.
Интересная консолидация
Преобразовать строки в столбцы
Повторяющиеся значения в строке
Нарастающий итог 3
Минимальное значение в диапазоне строк
Нарастающий итог 4. Группировки
Функция List.Contains и создание пользовательской функции
Скученные данные 3

Power Query + VBA

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

Трюки и советы

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

Источник

Power Query: стероиды для MS Excel и Power BI

image

В данной статье я хочу рассказать о некоторых возможностях бесплатной и крайне полезной, но пока еще мало известной надстройки над 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 умеет забирать данные из указанной папки и объединять их содержимое в единые таблицы. Это может быть полезно, например, если вам периодически приходят какие-то специализированные отчеты за отдельный промежуток времени, но данные для анализа нужны в общей таблице. Гифка

Текстовые функции

К столбцам из текста в Power Query по нажатию на кнопки на Ribbon можно применять такие функции как:

  1. Разделить столбец по символу или по количеству символов. И в отличие от Excel можно задать максимальное количество столбцов, а также направление откуда нужно считать символы — слева, справа.
  2. Изменить регистр ячеек в столбце
  3. Подсчитать количество символов в ячейках столбца.

Числовые функции

К столбцам с числовыми значениями по нажатию на кнопки на Ribbon можно применять:

  1. Арифметические операции
  2. Возводить в степени, вычислять логарифмы, факториалы, корни
  3. Тригонометрические операции
  4. Округлять до заданных значений
  5. Определять четность и т.д.

Функции для работы с датами, временем и продолжительностью

К столбцам со значениями даты и времени по нажатию на кнопки на Ribbon можно применять:

  1. Автоматическое определение формата вписанной даты (в excel c этим большая боль)
  2. Извлекать в один клик номер месяца, дня недели, количество дней или часов в периоде и т.п.

Unpivot — Pivot

В интерфейсе Power Query есть функция “Unpivot”, которая в один клик позволяет привести данные с одной метрикой разложенные по столбцам по периодам к форме, которая будет удобна для использования в сводных таблицах (понимаю что трудно написал — смотрите пример). Также, есть функция с обратным действие Pivot. Гифка

Операция Merge — смерть ВПР

Функция ВПР (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.

Вот часть задач, которые я делаю с использованием Power Query:

  1. разбираю семантику для Толстых проектов,
  2. Делаю частотные словари,
  3. Создаю веб-аналитические дашборды и отчеты для анализа конкретных срезов,
  4. Восстанавливаю достижение целей в системах веб-аналитики, если они не настроены на проекте,
  5. Сглаживаю прогноз вероятности методами Андрея Белоусова (+Байеса:),
  6. Делаю аудит контекстной рекламы на данных из K50 статистика,
  7. И много других разных ad-hoc analysis задач, которые нужно сделать лишь однажды

Вот bi систему, про которую я рассказывал на Yac/M 2015 (видео) я делал полнстью при помощи Power Query и загружал данные внутрь PowerPivot.

Пару слов про локализацию

На сайте Microsoft для пользователей из России по умолчанию скачивается Power Query с переведенным на русским язык интерфейсом. К счастью, локализаторы до перевода на русский языка программирования (как это сделано с языком формул в excel) не добрались, однако жизнь пользователям неоднозначными переводами сильно усложнили. И я призываю вас скачивать, устанавливать и пользоваться английской версией Power Query. Поверьте, она будет гораздо понятнее.

Источник

Читайте также:  Запрос все таблицы с префиксом
Adblock
detector