- 19.11.2020
Power Query — Это надстройка для Exel в Microsoft Office версий 2010 и 2013 (возможно установить так же на более новые версии Office). Надстройка расширяет функционал Exel, к примеру позволяет подключаться к разным источникам данных, таких как excel, csv, текстовые файлы, базы данных, папки и так далее. Эти данные можно преобразовывать, делать слияние (объединять) и делать между ними расчеты.
Надстройка распространяется бесплатно и скачать Вы его можете с официального сайта Microsoft. Начиная с Microsoft Office 2016 надстройка уже входит в состав приложения Exel.
— Скачать с сайта Microsoft.
— Скачать с нашего сервера x64 на Русском.
— Скачать с нашего сервера x32 на Русском.
Помимо всего, мы рекомендуем использовать только лицензионные приложения Microsoft Office, приобрести их можете в нашем каталоге от 449₽. Доставка ключей моментальная в автоматическом режиме на Ваш Email.
Power Query и Power Pivot — надстройки Excel для легкой работы с тяжелыми файлами. С помощью Power Query можно подключать и обрабатывать источники информации произвольного вида, а в Power Pivot – выполнять сложные расчеты и создавать модель данных.
Надстройка Power Query
Появление Power Query – одно из важных событий в истории развития Excel. То, что раньше пользователи делали в Excel с помощью рутинных операций по преобразованию данных, писали огромные формулы или макросы на VBA, теперь можно выполнять буквально в несколько щелчков мышкой.
Power Query – это надстройка для создания запросов по импорту, очистке и преобразованию данных.
Power Query сам автоматически генерирует и записывает код запросов на языке M.Но это вовсе не значит, что пользователям нужно писать код – интерфейс Power Query очень понятный и позволяет выполнять операции только с помощью мышки. Большинство операций по преобразованию данных в нем можно выполнить, не написав ни одной строчки кода. Все запросы Power Query записываются и сохраняются, поэтому при последующих подключениях повторять операции еще раз не нужно – они выполнятся автоматически после нажатия на кнопку в меню Данные → Обновить.
Как работает
В привычном нам виде Power Query впервые появился в Excel 2013. В Excel 2010 и 2013 надстройка отображается в виде отдельной вкладки «Power Query».
В Excel 2016 надстройка уже встроена по умолчанию, на вкладке Данные – раздел «Скачать и преобразовать».
В версии Excel 2019 на вкладке Данные разделы с Power Query — «Получить и преобразовать данные» и «Запросы и подключения».
Возможности Power Query
- Из Excel можно напрямую подключаться к самым различным источникам данных: файлы excel, csv, базы данных, папки и т.д.
- Данные в подключенных источниках можно преобразовывать самыми различными способами: сортировать, фильтровать, заменять, делать расчеты, объединять и т.д.
- Преобразованные данные можно импортировать: выгружать на лист, в сводную таблицу, сразу в модель данных (в Power Pivot). Или не выгружать, а просто сохранить запрос.
Операции по преобразованию данных выполняются в окне редактора запросов.
В редакторе Power Query пользователь работает с данными – выполняет преобразования, а его действия автоматически записываются в виде шагов запроса.
О том, как работать в редакторе запросов, читайте в следующей статье.
Как скачать Power Query
Надстройка устанавливается бесплатно и доступна для Excel начиная с версий 2010 года:
- для Excel 2010 и 2013 скачивается с сайта Microsoft;
- для Excel версии после 2016 года ничего скачивать и устанавливать не нужно, так как эта надстройка идет в составе всех лицензий.
Надстройка Power Pivot
Те, кто часто пользуются сводными таблицами, понимают, насколько они удобны и эффективны в работе.
Power Pivot — это надстройка Excel, в которой можно выполнять улучшенные вычисления для сводных таблиц с помощью DAX-формул. Power Pivot значительно расширяет функционал сводных таблиц и позволяет работать с большими объемами данных – в десятки миллионов строк.
Как работает
Вкладка меню Power Pivot во всех версиях Excel, начиная с 2010, выглядит одинаково. Чтобы открыть окно Power Pivot, нажмите в меню Power Pivot → Управление. Если вкладки Power Pivot у вас в меню нет, проверьте, та ли у вас версия Excel. И возможно, вам потребуется добавить эту вкладку в меню.
Возможности Power Pivot
- Power Pivot позволяет обращаться из одной сводной таблицы к данным из нескольких таблиц без формулы ВПР. Это возможно благодаря объединению таблиц в модель данныхс помощью связей.
- Импорт более 100 миллионов строк в рабочую книгу. При работе в «обычном» Excel у пользователей есть ограничения по размеру таблиц – размер листа, то есть чуть больше 1 млн строк. В Power Pivot такого ограничения нет и в него можно загружать миллионы строк. Единственное ограничение по объему — размер файла книги, равный 2 Гбайт, и объем оперативной памяти.
- Благодаря алгоритму сжатия данных в Power Pivot объем файла становится меньше, чем в источнике. Например, текстовый файл объемом 50 Мбайт может сжиматься до 4 Мбайт, занимаемых файлом Excel с таблицей.
- Создание улучшенных формул для сводных таблиц с помощью языка DAX.
В Power Pivot можно писать DAX-формулы для продвинутого анализа в столбцах и создавать меры — вычисления под таблицей.
Кстати, в Power Pivot есть свои инструменты для импорта данных, но из небольшого числа источников – некоторые базы данных, файлы Excel и текстовые и др. Эти инструменты можно найти на вкладке Главная. Так как доступных источников данных не очень много, то, как правило, в качестве источника для него выступает Power Query, функционал которого по подключению намного больше.
Увидеть все загруженные данные можно в нижней части окна Power Pivot – ярлычки с названиями таблиц находятся там же, где названия листов в «обычном» Excel.
Здесь же в меню Главная → Представление диаграммы можно перейти в представление, где создаются связи в модели данных. Создавать связи в этой области очень просто – с помощью «перетаскивания» полей: выбираем нужное поле мышкой и переносим его на соответствующее поле другой таблицы.
Power Pivot поддерживает типы связей «один к одному» и «один ко многим» («многие к одному»).
Перейти обратно от модели данных к таблицам можно в меню Главная → Представление данных. В представлении в виде таблиц удобно делать вычисления с помощью DAX-формул, которые очень похожи на обычные формулы Excel.
DAX-формулы пишут в столбцах таблиц – создают вычисляемые столбцы. Или в области вычислений под таблицей, такие объекты называются меры.
Еще в Power Pivot можно создавать ключевые показатели эффективности (KPI) – автоматические светофоры, которые будут выводиться в сводной таблице. KPI в Power Pivot – это расширение функционала для мер.
Язык DAX используется не только в Power Pivot в Excel, но и в Power BI и Analysis Services. Подробнее о DAX можно прочитать в наших следующих статьях.
Как скачать Power Pivot
Надстройка Power Pivot есть не во всех версиях Excel:
- Power Pivot для Excel 2010 года скачивается бесплатно с сайта Microsoft. К сожалению, в Excel 2010 Power Pivot почти невозможно «подружить» с Power Query, и этот новый функционал в старом Excel сильно ограничен.
- В Excel 2013 и 2016 года Power Pivot идет в составе программы, но не во всех лицензиях. Так, в Office Pro Plus и Office 2019 надстройка есть. Узнать, какие именно версии Office включают Power Pivot, можно здесь.
Кстати, отличная новость – компания Microsoft анонсировала включение Power Pivot во все версии Excel, начиная с версий после 2019 года. Так что теперь Power Pivot всегда будет под рукой.
Если с версией Excel все в порядке, а вкладки Power Pivot в меню нет, то ее нужно добавить в меню с помощью окна Управления надстройками (как это сделать – смотрите далее).
Как добавить Power Query и Power Pivot в меню
В зависимости от того, какая у вас версия Excel, вам может потребоваться перед первым использованием добавить надстройки Power Query и Power Pivot в меню.
Если в меню Excel у вас нет надстроек, то нужно их добавить в окне Управления надстройками:
Файл → Параметры → Надстройки → в Выпадающем меню выбрать → Надстройки COM → Перейти → в открывшемся окне выбрать надстройки.
Как надстройки помогают в создании отчетов Excel
Надстройки Power Query и Power Pivot дополняют друг друга и отлично работают вместе со сводными таблицами, позволяя пользователям создавать в Excel аналитические отчеты. Как правило, используется последовательность работы с данными в таком порядке:
- с помощью Power Query подключают и обрабатывают источники данных;
- из Power Query плоские таблицы загружаются прямо в Power Pivot, минуя ограничение по количеству строк листа Excel;
- в Power Pivot выполняют сложные расчеты и создают модель данных;
- отчеты создаются с помощью сводных таблиц, диаграмм и элементов управления (временной шкалы и срезов).
Отчеты по такой схеме настраиваются один раз и при последующем изменении данных преобразования и вычисления выполнятся автоматически, после нажатия на кнопку в меню Данные → Обновить.
Надстройки в Excel и Power BI
Огромное преимущество надстроек Power Query и Power Pivot – они есть не только в Excel, но и в Power BI. Поэтому на тренингах я обычно говорю слушателям, что мы изучаем сразу две программы.
Интерфейс надстроек в обеих программах очень похож, но функционал Power BI все-таки опережает Excel (сразу уточню, что сравниваю только инструменты Power Query и Power Pivot). Например, Power BI поддерживает больше источников данных и доступны связи «многие-ко-многим», которых в Excel нет.
Для сравнения – интерфейс Power Query в обеих программах выглядит практически одинаково:
Power Query в Excel
Power Query в Power BI
Power Pivot в целом также работает одинаково в Excel и Power BI, кроме ряда некоторых нюансов. Например, так выглядит окно настройки модели данных:
Модель данных в Excel
Модель данных в Power BI
Кстати, если у вас есть файл Excel, в котором вы настраивали аналитику с помощью Power Query и Power Pivot, то его содержимое можно перенести в Power BI. Это делается в Power BI: меню Файл → Импортировать → выберите «Power Query, Power Pivot, Power View». После пары вопросов и некоторых «размышлений» запросы и вычисления из Excel будут перенесены в Power BI.
В общем, надстройки Power Query и Power Pivot – это суперсила (от слова power) Excel, которые переносят возможности аналитики в этой программе на новый уровень. С их помощью пользователи могут создавать отчеты с вычислениями на основе разных источников, без ограничений по объему данных в размер одного листа в миллион строк. Плюс, компания Microsoft включает эти инструменты и в другие свои продукты, так что понимание того, как они работают, вам точно пригодится.
В данной статье я хочу рассказать о некоторых возможностях бесплатной и крайне полезной, но пока еще мало известной надстройки над 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 можно применять такие функции как:
- Разделить столбец по символу или по количеству символов. И в отличие от Excel можно задать максимальное количество столбцов, а также направление откуда нужно считать символы — слева, справа.
- Изменить регистр ячеек в столбце
- Подсчитать количество символов в ячейках столбца.
Числовые функции
К столбцам с числовыми значениями по нажатию на кнопки на Ribbon можно применять:
- Арифметические операции
- Возводить в степени, вычислять логарифмы, факториалы, корни
- Тригонометрические операции
- Округлять до заданных значений
- Определять четность и т.д.
Функции для работы с датами, временем и продолжительностью
К столбцам со значениями даты и времени по нажатию на кнопки на Ribbon можно применять:
- Автоматическое определение формата вписанной даты (в excel c этим большая боль)
- Извлекать в один клик номер месяца, дня недели, количество дней или часов в периоде и т.п.
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:
- разбираю семантику для Толстых проектов,
- Делаю частотные словари,
- Создаю веб-аналитические дашборды и отчеты для анализа конкретных срезов,
- Восстанавливаю достижение целей в системах веб-аналитики, если они не настроены на проекте,
- Сглаживаю прогноз вероятности методами Андрея Белоусова (+Байеса:),
- Делаю аудит контекстной рекламы на данных из K50 статистика,
- И много других разных ad-hoc analysis задач, которые нужно сделать лишь однажды
Вот bi систему, про которую я рассказывал на Yac/M 2015 (видео) я делал полнстью при помощи Power Query и загружал данные внутрь PowerPivot.
Пару слов про локализацию
На сайте Microsoft для пользователей из России по умолчанию скачивается Power Query с переведенным на русским язык интерфейсом. К счастью, локализаторы до перевода на русский языка программирования (как это сделано с языком формул в excel) не добрались, однако жизнь пользователям неоднозначными переводами сильно усложнили. И я призываю вас скачивать, устанавливать и пользоваться английской версией Power Query. Поверьте, она будет гораздо понятнее.
Ссылки по теме
Официальные ресурсы на английском:
- Скачать Английскую версию Power Query
- Справка по формулам языка M
- Ветка Форума Microsoft Technet про Power Query
На момент написания статьи на русском языке информации по Power Query практически нет, и то что мной найдено приведено ниже:
- Импорт данных из таблиц в Google Spreadsheets
- Power BI. Получаем данные из REST API
- Сообщество продвинутых пользователей Excel, Power Pivot и Power Query в Facebook
- Обновляемая страничка в Marketing-wiki про Power Query
На английском:
- Видео с демонстрацией возможностей Power Query на конференции TechEd North America
- Блог Chris Webb, пожалуй наиболее полный ресурс с примерами использования Power Query
- Книга Chris Webb — Power Query for Power BI and Excel
- Блог Ken Puls
- Курс «Excel для интернет-маркетинга»
- Курс «Power BI для интернет-маркетинга»
- Книга Ken Puls и Miguel Escobar — M is for Data Monkey
Файлы к уроку:
- Для спонсоров Boosty
- Для спонсоров VK
Ссылки:
- Страница курса
- Плейлист YouTube
- Плейлист ВК
Описание
В этом уроке вы узнаете:
- Зачем нужен Power Query
- История Power Query
- Как установить Power Query в Excel
- Зачем нужен Power Query
- Как создать запрос в Power Query
В этом вводном уроке курса Excel Power Query на 1-2-3 мы узнаем, что такое Power Query, зачем он нужен, как его установить и запустить.
Power Query — это встроенный инструмент Excel для получения и преобразования данных (Get and Transform).
Специалистам по Excel приходится каждый день тратить уйму времени на преобразование данных. Мы работаем с разными источниками и данные далеко не всегда приходят в удобном виде.
Например, каждый месяц вы получаете таблицу с ответами на вопросы теста сотрудников компании, где вы работаете в таком виде:
Первый столбец — это дата прохождения теста. 2 следующих столбца — это информация о сотруднике и точке продаж. Далее множество столбцов с ответами на вопросы, а вопрос находится в заголовках столбцов. Ваша задача — получить такую таблицу:
Для каждого сотрудника здесь указано количество ответов, количество верных ответов, результат теста и перечень вопросов с неверными ответами.
В Excel эта задача решается долго и тяжело. Если тесты происходят, например, раз в неделю, то каждую неделю вам придется тратить уйму времени на обработку файлов. Более того, чтобы решить эту задачу стандартными возможностями Excel нужен достаточно высокий уровень владения.
Как вы думаете, сколько времени уйдет на решение этой задачи в Excel Power Query? Всего несколько минут. Более того, вам достаточно решить эту задачу всего 1 раз. А когда придут новые данные достаточно будет всего лишь нажать «Обновить».
Решение
Запуск Power Query
Если у вас Excel от 2016 версии и новее, то Power Query устанавливать не нужно. Он уже встроен в Excel.
Если у вас Excel 2010 или 2013, то Power Query нужно сначала скачать. Перейдите по ссылке https://www.microsoft.com/ru-ru/download/details.aspx?id=39379 и нажмите «Скачать». Потом отметьте галочкой файл для вашей разрядности операционной системы (64 или 32 бит). После скачивания файла запустите установку.
Теперь в главном меню Excel должна появиться еще одна вкладка. Если вкладка не появилась, то перейдите в меню Файл — Параметры — Настройки — Управление — Надстройки СОМ. В открывшемся окне отметьте галочкой пункт Power Query.
Добавить кнопки Power Query на панель быстрого доступа
Я рекомендую добавить кнопки для работы с Power Query на панель быстрого доступа. Это ускоряет работу. Я настоятельно рекомендую добавить 3 команды:
- Изменить запрос
- Запросы и подключения
- Запустить редактор запросов
Если вы не знаете как добавлять кнопки в панель быстрого доступа, то посмотрите видео-версию урока на YouTube.
Нажмите «Настройка панели быстрого доступа». В появившемся окне справа снизу найдите кнопку «Экспорт/импорт». Нажмите ее и укажите путь к скачанному файлу.
Предварительные настройки Power Query
Откройте редактор запросов любым удобным способом:
- При помощи созданной кнопки в панели быстрого доступа
- Вкладка Данные — Группа Получить и преобразовать данные — Получить данные — Запустить редактор запросов
В окне Редактора запросов нажмите Файл — Параметры и настройки — Параметры запроса. В пункте Загрузка данных сделайте настройки как на картинке:
Теперь перейдите в пункт Редактор Power Query и поставьте галочку «Отобразить редактор запросов».
Далее переходим в пункт Конфиденциальность и выбираем «Всегда игнорировать уровни конфиденциальности».
Жмем ОК. Настройки вступят в силу при следующем запуске редактора запросов.
Создаем первый запрос Power Query
Создадим первый запрос к веб-странице с таблицей состава индекса Dow Jones. Выполняем следующие действия:
- Запускаем редактор запросов
- Вкладка Главная — Создать источник — Другие источники — Интернет
- Вводим адрес https://finance.yahoo.com/quote/%5EDJI/components?ltr=1
- Нажимаем Подключение
- Слева указываем нужную таблицу и жмем ОК
Перед вам появится следующее окно редактора запросов Power Query:
Сверху мы видим меню в привычном ленточном интерфейсе как и во всех продуктах Microsoft Office. Слева список запросов, справа примененные шаги к запросу. Теперь продолжим работать с нашим запросом.
Удалим шаг Изменить тип. Для этого в списке шагов справа нажмем на крестик слева от названия шага. Укажем тип данных для каждого столбца.
По умолчанию Power Query определил тип данных каждого столбца как текстовый. Об этом говорит пиктограмма с символами ABC слева от названий столбцов.
Попробуйте нажать на пиктограмму ABC столбца Last Price и указать тип данных десятичное число. В столбце во всех строках отобразятся ошибки. Это произошло из-за того, что в нашем регионе целая и дробная части разделяются запятыми, а в таблице с этого сайта точкой. В таком случае нужно указать тип с использованием локали.
Нажмите правой кнопкой мыши на название столбца Last Price — Тип изменения — Используя локаль. Укажите тип данных целое число и языковой стандарт Английский США. Теперь все получилось. То же самое проделайте для других числовых столбцов.
Теперь перейдите на вкладку Главная, щелкните на нижнюю часть кнопки Закрыть и загрузить, в списке выберете Закрыть и загрузить в. Отметьте пункт Таблица и укажите место, куда эту таблицу поместить, потом нажмите ОК. Наш первый запрос готов!
Power Query разное
| Номер урока | Урок | Описание |
|---|---|---|
| 1 | Power Query. Знакомство с Power Query | В этом уроке мы познакомимся в Power Query. Зачем нужен Power Query Как установить Power Query Как его Настроить Как изменить запрос |
| 2 | Power Query. Подключение XML | В этом уроке мы научимся подключаться к файлам в формате XML и импортировать эти данные в Excel. |
| 3 | Power Query. Уникальные значения двух столбцов | В этом уроке мы получим уникальные значения из двух столбцов таблицы. |
| 4 | Power Query. Импорт таблиц PDF | Импорт таблиц из файла PDF, импорт таблиц из множества PDF файлов с объединением в один датасет. |
| 5 | Power Query. Собрать разбитую строку | В этом практическом уроке мы научимся соединять разбитую строку. Этот пример взят из реальной практики одного из спонсоров канала. |
| 6 | Power Query. Пивот со счетом | В этом уроке мы создадим пивот, в котором будут пронумерованы столбцы. |
| 7 | Power Query. Минимальное значение в диапазоне | В этом уроке мы найдем минимальное значение в диапазоне строк. |
| 8 | Power Query. Нарастающий итог 2 | В этом уроке мы изучим еще один способ сделать нарастающий итог в Power Query. |
| 9 | Power Query. Нарастающий итог 3 | В этом уроке мы разберем еще один способ выполнить нарастающий итог в Power Query. |
| 10 | Power Query. Прирост населения Китая | В этом уроке мы сравним прирост населения Китая с приростом населения мира в целом за последние 200 лет. |
| 11 | Power Query. Повторяющиеся значения в строке | В этом уроке разберем как определить есть ли в строке повторения. |
| 12 | Power Query. Таблица навигации по функциям М | В этом уроке вы узнаете как создать таблицу навигации по всем функциям языка Power Query. |
| 13 | Power Query. Удалить запросы и модель данных из книги | Разберем как быстро удалить все запросы и модель данных из текущей книги. |
| 14 | Power Query. Открыть еще 1 Excel и еще 3 трюка | В этом видео я покажу как открыть еще 1 файл Excel, если у вас уже запущен Power Query. |
| 15 | Power Query. Подключиться к ZIP архиву | Пользовательская функция для подключения к zip файлу. Подключимся к txt файлу, который находится в zip архиве. |
| 16 | Power Query. Импорт Word | Импортируем таблицу из документа Word. Для спонсоров разберем импорт таблицы с объединенными ячейками. |
| 17 | Power Query. Фильтрация списком | В этом уроке мы хотим отфильтровать таблицу при помощи списка, например, хотим получить продажи определенных товаров. |
| 18 | Power Query. Пользовательская функция Switch | В этом уроке мы создадим пользовательскую функцию Switch. |
| 19 | Power Query. Информация о формате, Чтение zip | В этом уроке мы узнаем как получить информацию о формате ячеек при помощи Power Query. |
| 20 | Power Query. Импорт данных из gz | В этом уроке мы разберем как импортировать файл в формате gz. |
| 21 | Power Query. Удалить лишние пробелы, Text.Split | В этом уроке мы научимся удалять лишние пробелы в текстовом столбце таблицы. |
| 22 | Power Query. Параметры в SQL-запросе | Вы хотите, чтобы в ваш SQL-запрос подставлялось значение из параметра, источником которого является ячейка с листа Excel. |
| 23 | Power Query. Параметры в SQL-запросе 2 | Ваш запрос очень большой и количество параметров в нем большое. Как организовать все так, чтобы было удобно работать. |
| 24 | Power Query. Добавить столбец в каждую таблицу табличного столбца | В этом уроке вы узнаете как трансформировать табличный столбец, например, вы сможете добавить столбец индекса внутрь каждой таблицы табличного столбца. |
| 25 | Power Query. Интервальный просмотр 1 (ВПР 1) | Объединить 2 таблицы с интервальным просмотром = 1. |
| 26 | Power Query. Относительный путь к файлу и папке | Если ваш источник находится в той же папке, что и отчет, то вы можете указать относительный путь. В таком случае подключение не будет ломаться, если вы запустите файл на другом компьютере. |
| 27 | Power Query. Нарастающий итог в каждой категории | Применим функцию нарастающего итога не ко всей таблице, а к определенному окну. |
| 28 | Power Query. ВПР без Merge или Join | Вам нужно подставить данные из столбца другой таблицы. Как это сделать без объединения таблиц. |
Здравствуйте, господа.
Вынужден обратиться к живой аудитории за помощью, т.к. машинный поиск по сети мне не помог в решении возникшей ситуации (ничего толкового не нашёл).
Пытаюсь установить на 2010 Excel (x64) пресловутую надстройку Power Query. Как только запускаю файл установки, то программа выдаёт следующее:
| Цитата |
|---|
| Для Microsoft Power Query для Excel (x64) на этом компьютере требуется установка всех версий Microsoft Excel с возможностью программирования .NET. Переустановите на этом компьютере все версии Microsoft Excel с включенной возможностью программирования .NET. |
И завершает работу без установки надстройки.
Что делал:
1. Скачал и установил Microsoft .NET Framework v4.6.1 — не помогло. В реестре смотрел, что в системе установлены версии 2.0, 3.0, 3.5 и 4.
2. Сделал восстановление офиса (через панель управления установка и удаление программ) — не помогло.
Полностью сносить офис не пытался, т.к. по времени затратно. (хотя и не исключаю, что окажется единственным возможным выходом из сложившейся ситуации, тогда ой…)
Возможно из вас кто-либо сталкивался с подобной ситуацией и находил выход из неё, кроме переустановки офиса?
Буду благодарен за любую оказанную помощь.
Спасибо.