Відомості про Power Query в Excel
За допомогою Power Query (відомого як Отримання перетворення & Excel) можна імпортувати або підключитися до зовнішніх даних, а потім сформувати ці дані, наприклад видалити стовпчик, змінити тип даних або об'єднати таблиці відповідно до ваших потреб. для Windows, Excel для Mac та Excel для Інтернету.
Примітка: Power Query в Excel для Windows використовує платформу .NET Framework, але потрібна версія 4.7.2 або пізніша. Останні платформа .NET Framework можна завантажити тут.
Примітка: Power Query в Excel для Windows використовує час виконання Microsoft Edge WebView2.
Існує чотири етапи використання Power Query.
- Підключення Підключення до даних у хмарі, службі або локально
- Перетворення Формування даних відповідно до ваших потреб, у той час як вихідне джерело залишається незмінним
- Об'єднати Інтеграція даних із кількох джерел для отримання унікального подання до даних
- Навантаження Завершіть запит і завантажте його на аркуш або модель даних і періодично оновлюйте його.
У наступних розділах докладно розглядаються усі етапи.
Ви можете використовувати Power Query для імпорту в одне джерело даних, наприклад, у книгу Excel, або в кілька баз даних, веб-каналів або служб, розкиданих по всій хмарі. Джерела даних включають дані з Інтернету, файлів, баз даних, Azure або навіть таблиць Excel у поточній книзі. За допомогою Power Query ви зможете об'єднати всі ці джерела даних, використовуючи власні унікальні перетворення та поєднання, щоб отримати аналітичні відомості, які ви не бачили б інакше.
Після імпорту можна оновити дані, щоб внести додатки, зміни та видалення із зовнішнього джерела даних. Для отримання додаткових відомостей див. у розділі Оновлення підключення до зовнішніх даних у Excel.
Перетворення даних означає, що вони якимось чином змінюються відповідно до вимог аналізу даних. Наприклад, можна видалити стовпець, змінити тип даних або відфільтрувати рядки. Кожна з операцій є перетворенням даних. Цей процес застосування перетворень (і поєднання) до одного або кількох наборів даних також називається формуванням даних.
Подумайте про це так. Ваза починається як шматок глини, яка формує у щось практичне та красиве. Дані збігаються. Він повинен сформувати таблицю, яка підходить для ваших потреб і забезпечує привабливі звіти та панелі моніторингу.
Power Query використовує виділене вікно з ім'ям Редактор Power Query для спрощення та відображення перетворень даних. Ви можете відкрити Редактор Power Query, обравши Запустити Редактор запитів у команді Отримати дані у групі Отримання даних перетворення &, але вона також відкривається під час підключення до джерела даних, створення запиту або завантаження запиту.
Редактор Power Query відслідковує все, що ви робите з даними, записуючи та позначаючи кожне перетворення чи крок, що застосовуються до даних. Незалежно від того, чи є перетворення з'єднанням даних, видаленням стовпця, злиттям або зміною типу даних, кожне перетворення можна переглянути та змінити в розділі ЗАСТОСУВАНІ КРОКИ області Параметри запиту .
Існує безліч перетворень, які можна виконати в інтерфейсі користувача. Кожне перетворення записується як крок у фоновому режимі. Ви навіть можете змінити та написати власні кроки за допомогою мови Power Query M у Розширений редактор.
Всі перетворення, що застосовуються до підключень до даних, у сукупності є запитом, який є новим уявленням вихідного (і незмінного) джерела даних. Під час оновлення запиту кожен крок виконується автоматично. Запити замінюють необхідність вручну підключати та формувати дані в Excel.
Ви можете поєднати кілька запитів у книзі Excel, додавши або об'єднавши їх. Операції додавання та злиття виконуються для будь-якого запиту з табличною фігурою та не залежать від джерел даних, з яких надходять дані.
Додати Операція додавання створює новий запит, що містить усі рядки з першого запиту, за яким слідують усі рядки з другого запиту. Ви можете виконувати два типи операцій додавання:
- Проміжне додавання Створює новий запит для кожної операції додавання.
- Вбудоване додавання Додає дані до існуючого запиту до досягнення кінцевого результату.
Об'єднати Операція злиття створює новий запит на основі двох запитів.Цей запит містить усі стовпці з первинної таблиці, а один стовпець є посиланням навігації до пов'язаної таблиці. .
Існує два основні способи завантаження запитів у книгу:
- У Редактор Power Query можна використовувати команди Закрити та Завантажити у групі Закрити на вкладці Головна.
- В області Excel Workbook Querys (Select Querys & Connections) ви можете клацнути запит правою кнопкою миші та вибрати команду Завантажити в.
Ви також можете налаштувати параметри завантаження за допомогою діалогового вікна Параметри запиту (Виберіть параметри файлу >
параметри та параметри >
Параметри запиту), щоб вибрати спосіб перегляду даних і місце завантаження даних на аркуші або моделі даних (яка є реляційним джерелом даних декількох таблиць, що знаходяться в книзі).
Уже більше десяти років Power Query підтримується в Excel для Windows Тепер Excel розширює підтримку Power Query в Excel для Mac і додає підтримку Excel для Інтернету. Це означає, що ми робимо Power Query доступними на трьох основних платформах і демонструємо популярність і функціональність Power клієнтів Excel.
Інтеграція get & transform Data (тепер називається Power Query) в Excel за минулі роки пережила низку змін.
Excel 2010 та 2013 для Windows
В Excel 2010 для Windows ми вперше представили Power Query, і вона була доступна у вигляді безкоштовної надбудови, яку можна завантажити тут: Завантажити надбудову Power Query. Після включення функціональність Power Query була доступна на вкладці Power Query на стрічці.
Ми оновили Power Query, щоб стати основним інтерфейсом в Excel для імпорту та очищення даних. Ви можете отримати доступ до майстрів та засобів імпорту даних Power Query з групи Отримання & перетворення даних на вкладці Дані стрічки Excel.
Цей інтерфейс включає розширені функції імпорту даних, перестановку команд на вкладці Дані , нову панель "Запити & підключення ", а також можливість ефективної обробки даних шляхом сортування, зміни типів даних, поділу стовпців, агрегування даних і т.д."
Цей новий інтерфейс також замінив старі застарілі майстри імпорту даних у команді Data у групі Отримання зовнішніх даних . Однак доступ до неї, як і раніше, можна отримати з діалогового вікна Параметри Excel (Виберіть параметри >
файлу >
>
показати застарілі майстри імпорту даних).
Excel 2016 та 2019 для Windows
Ми додали той самий інтерфейс Get & Transform Data на основі технології Power Query, що й Microsoft 365.
Excel для Microsoft 365 для Mac
У 2019 році ми почали підтримку Power Query у Excel для Mac. З того часу ми додали можливість оновлювати запити Power Query із файлів TXT, CSV, XLSX, JSON та XML. Ми також додали можливість оновлювати дані з SQL Server та таблиць & діапазонів в поточній книзі.
У жовтні 2019 р. ми додали можливість оновлювати існуючі запити Power Query та використовувати VBA для створення та редагування нових запитів.
У січні 2021 р.додано підтримку оновлення Power Query запитів із джерел OData та SharePoint.
Примітка Підтримка Power Query у Excel 2016 та Excel 2019 для Mac відсутня.
Каталог даних старіння
За допомогою Каталогу даних можна переглянути загальні запити, а потім вибрати їх для завантаження, зміни або використання в поточній книзі. Ця функція поступово стала застарілою:
- 1 серпня 2018 року ми припинили підключення нових клієнтів до Каталогу даних.
- 3 грудня 2018 р. користувачі не змогли поділитися новими або оновленими запитами до Каталогу даних.
- 4 березня 2019 року Каталог даних перестав працювати. Після цієї дати ми рекомендуємо завантажити загальні запити, щоб ви могли продовжувати використовувати їх за межами Каталогу даних за допомогою параметра Відкрити у сфері завдань Мої запити Каталог даних.
Power Query старіння надбудови
На початку літа 2019 року ми офіційно зреклися Power Query надбудови, яка потрібна для Excel 2010 і 2013 для Windows. Як люб'язно, ви, як і раніше, можете використовувати надбудову, але це може змінитися пізніше.
Facebook з'єднувач даних припинено
Імпорт та оновлення даних із Facebook у Excel перестали працювати у квітні 2020 р. Усі підключення Facebook, створені до цієї дати, більше не працюють. Ми рекомендуємо якнайшвидше переглянути або видалити існуючі запити Power Query, які використовують з'єднувач Facebook, щоб уникнути непередбачених результатів.
Критичні оновлення Excel для Windows
З червня 2023 р. для Power Query в Excel для Windows потрібні такі компоненти:
- Power Query в Excel для Windows використовує платформу .NET Framework, але потрібна версія 4.7.2 або пізніша. Для отримання додаткових відомостей див.у розділі Оновлення платформи .NET Framework.
- Power Query в Excel для Windows потрібно, щоб середовище виконання WebView2 продовжувало підтримувати веб-з'єднувач даних (отримання даних з Інтернету). Щоб отримати додаткові відомості, див. Завантаження середовища виконання WebView2.
Навчальний посібник з excel power запит (отримання та перетворення)
Якщо ви один з тих людей, які багато працюють з даними, ви можете бути будь-ким ( бухгалтером , HR, аналітиком даних і т. д.), PowerQuery може стати вашим потужним інструментом.
Дозвольте мені перейти до справи: Power Query — це одна з найсучасніших навичок роботи з Excel, яку вам необхідно освоїти, і в цьому посібнику ви докладно вивчите PowerQuery і дізнаєтеся, як перетворювати дані за його допомогою.
Що таке Power Query в Excel
Power Query – це надбудова Excel, яку можна використовувати для ETL. Це означає, що ви можете отримувати дані з різних джерел, перетворювати їх, а потім завантажувати до електронної таблиці. Можна сказати, що POWER QUERY - це машина для очищення даних, оскільки вона має всі можливості для перетворення даних. Це відбувається в режимі реального часу і записує кожен крок.
Чому слід використовувати Power Query (переваги)?
Якщо у вас є це питання, ось моя відповідь для вас:
- Різні джерела даних : Ви можете завантажувати дані в потужний редактор запитів з різних джерел даних, таких як CSV, TXT, JSON і т.д.
- Легко перетворюйте дані. Зазвичай для перетворення даних ви використовуєте формули та зведені таблиці, але з POWER QUERY ви можете багато зробити всього за кілька клацань мишею.
- Це в режимі реального часу: напишіть запит один раз, і ви зможете оновлювати його щоразу, коли в даних відбуваються зміни, і він конвертує нові дані, які ви оновили.
Дозвольте мені поділитися прикладом:
Уявіть, що у вас є 100 файлів Excel, що містять дані зі 100 міст, і тепер ваш начальник хоче, щоб ви створили звіт з усіма даними із цих 100 файлів. ОК, якщо ви вирішите відкрити кожен файл вручну і скопіювати та вставити дані з цих файлів і вам знадобиться для цього мінімум годину.
Але з Power Query ви можете зробити це за лічені хвилини. Ви відчуваєте хвилювання? ДОБРИЙ.
Далі в цьому посібнику ви дізнаєтеся, як використовувати Power Query на багатьох прикладах, але спочатку вам необхідно зрозуміти його концепцію.
Концепція попиту на потужність
Щоб вивчити запит потужності, вам необхідно зрозуміти його концепцію, яка працює у 3 етапи:
1. Отримати дані
Power query дозволяє отримувати дані з різних джерел, таких як Інтернет, CSV, текстові файли, кілька книг з однієї папки та багато інших джерел, де ми можемо зберігати дані.
2. Перетворіть дані
Після того, як ви отримаєте дані в запит потужності, у вас з'явиться ціла низка опцій, які ви можете використовувати для їх перетворення та очищення. Він створює запити для всіх виконуваних вами кроків (послідовно один крок за іншим).
3. Завантажте дані
З розширеного редактора запитів ви можете завантажити перетворені дані на лист, або створити зведену таблицю або зведену діаграму, або створити підключення тільки для даних.
Де знаходиться Power Query (як встановити)?
Нижче ви можете побачити, як встановити Power Query Access у різних версіях Microsoft Excel.
Ексель 2007
Якщо ви використовуєте Excel 2007, вибачте , що PQ недоступний для цієї версії, тому вам необхідно виконати оновлення до latest version Excel (Excel для Office 365, Excel 2019, Excel 2016, Excel 2013, Excel 2010).
Ексель 2010 та Ексель 2013.
Для 2010 та 2013 років вам необхідно встановити надбудову окремо, яку ви можете завантажити по цим посиланням , і після встановлення ви отримаєте нову вкладку на стрічці Excel, як показано нижче:
- Насамперед скачайте надбудову звідси (офіційний сайт Microsoft).
- Як тільки файл завантажиться, відкрийте його та дотримуйтесь інструкцій.
- Після цього ви автоматично отримаєте вкладку Power Query на стрічці Excel.
Якщо з якихось причин ця вкладка POWER QUERY не з'явилася, не варто турбуватися про це. Ви можете додати його за допомогою опції «Надбудови COM».
- У параметрах "Надбудова" виберіть "Надбудови COM" та натисніть "GO".
- Після цього встановіть прапорець Microsoft Power Query for Excel.
Ексель 2016, 2019, Офіс 365.
Якщо ви використовуєте Excel 2016, Excel 2019 або маєте передплату на OFFICE 365, вона вже є на вкладці «Дані » у вигляді групи під назвою «GET & TRANSFORM» (мені подобається це ім'я, а вам?).
Excel для Mac
Якщо ви використовуєте Excel на Mac, боюся, для цього немає надбудови Power Request, і ви можете лише оновити існуючий запит , але не можете створити новий або навіть відредагувати запит ( ПОСИЛАННЯ ).
Редактор Power Query
Power Query має власний редактор, у якому ви можете отримати дані, виконати всі дії зі створення запитів, а потім завантажити їх до електронної таблиці. Щоб відкрити редактор Power Query, потрібно перейти на вкладку «Дані» та вибрати «Отримати та перетворити» ➜ «Отримати дані» ➜ «Запустити редактор Power Query».
Нижче наведено перший попередній перегляд редактора, який ви отримаєте, відкривши його.
Тепер давайте докладно розглянемо кожен розділ:
1. Стрічка
Давайте подивимося на всі доступні вкладки:
- Файл: на вкладці «Файл» можна завантажити дані, видалити редактор і відкрити параметри запиту.
- Головна : на вкладці ГОЛОВНА у вас є параметри для керування завантаженими даними, наприклад, видалення та переміщення стовпців та рядків.
- Перетворення : ця вкладка містить усі параметри, необхідні для перетворення та очищення даних, такі як об'єднання стовпців, транспонування тощо. буд.
- Додати стовпець : тут у вас є можливість додати нові стовпці до даних у розширеному редакторі.
- Подання : на цій вкладці можна змінити представлення розширеного редактора запитів та завантажені дані.
2. Застосовані кроки
У правій частині редактора знаходиться панель налаштувань запиту, яка включає ім'я запиту та всі кроки, які виконуються в послідовності.
Коли ви клацаєте правою кнопкою миші по кроці, у вас з'являється список опцій, які ви можете виконати, наприклад, перейменовувати, видаляти, редагувати, переміщати вгору або вниз і т.д. буд. і коли ви натиснете на крок, редактор перенесе вас до перетворення, виконаного на цьому кроці.
Подивіться нижче, де ви застосували всі п'ять кроків, і коли я натискаю на четвертий крок, я переходжу до перетворення на четвертому кроці, де імена стовпців не змінилися.
3. Запитання
На панелі запитів зліва перелічені всі запити, які є в книзі. По суті це місце, де ви можете керувати всіма запитами.
Клацнувши правою кнопкою миші ім'я запиту, ви побачите всі доступні параметри (копіювати, видалити, дублювати і т. д.).
Ви також можете створити новий запит, просто клацнувши правою кнопкою миші порожнє місце на панелі запитів і вибравши параметр джерела даних.
4. Панель формул
Як я вже сказав, щоразу, коли ви застосовуєте крок у редакторі, він генерує для цього кроку M-код, і ви можете побачити цей код у рядку формул. Можна просто натиснути на рядок формул, щоб відредагувати код.
Як тільки ви навчитеся використовувати код M, ви зможете створити крок, написавши код і просто натиснувши кнопку «FX», щоб ввести власний крок.
5. Огляд даних
Область попереднього перегляду даних виглядає як електронна таблиця Excel, але вона трохи відрізняється від звичайної електронної таблиці, в якій можна безпосередньо редагувати комірку або дані. Коли ви завантажуєте дані в редактор (ми зробимо трохи пізніше), він відображає всі стовпці з заголовками і іменами стовпців, а потім рядки з даними.
У верхній частині кожного стовпця можна побачити тип даних стовпця. Коли ви завантажуєте дані до редактора, Advanced Query автоматично застосовує правильний тип даних (майже кожного разу) до кожного стовпця.
Ви можете натиснути кнопку у верхньому лівому куті заголовка стовпця, щоб змінити тип даних, застосований до стовпця.Він містить список усіх типів даних, з яких можна отримати дані.
А в лівій частині заголовка стовпця є кнопка фільтра, яку можна використовувати для фільтрації значень стовпця. Примітка. Коли ви фільтруєте значення стовпця, розширений запит розглядає його як один крок і перераховує його у списку "Застосовані кроки".
Якщо правою кнопкою миші клацнути заголовок стовпця, ви побачите меню, що містить список параметрів, які можна використовувати для перетворення даних і використання одного з параметрів, а PQ зберігається як етап прикладного процесу. ні.
Джерела даних для Power Query
Найприємніше в Power Query те, що у вас є можливість отримувати дані з кількох джерел, перетворювати ці дані, а потім завантажувати їх в електронну таблицю. Натиснувши кнопку «Отримати дані» у розділі "Отримати та перетворити", ви побачите повний список джерел даних, які можна завантажити в редактор.
Тепер давайте подивимося на деякі джерела даних:
- З таблиці/діапазону : за допомогою цієї опції ви можете завантажувати дані до розширеного редактора запитів безпосередньо з активного аркуша.
- З книги : з іншої книги на вашому комп'ютері. Вам просто потрібно знайти цей файл за допомогою відкритого діалогового вікна і він автоматично отримає дані з цього файлу.
- З тексту/CSV : отримайте дані з текстового файлу або файлу, розділеного комами, потім можна завантажити їх в електронну таблицю.
- З папки : він бере всі файли з папки та підтримує дані у потужному редакторі запитів. (Див. це: Об'єднання файлів Excel з однієї папки).
- З Інтернету : За допомогою цього параметра ви отримуєте дані з веб-адреси.Уявіть, що у вас є файл, який зберігається в Інтернеті, або веб-сторінку, з якої потрібно отримати дані.
Як завантажити дані до редактора Power Query
Тепер давайте навчимося завантажувати дані у потужний редактор запитів. Тут у вас є список імен студентів та їх оцінок ( ПОСИЛАННЯ ).
Ви завантажуватимете дані безпосередньо з електронної таблиці, тому спочатку вам потрібно відкрити файл, а потім виконати наступні кроки:
- Спочатку застосуйте до даних таблицю Excel (навіть якщо ви цього не зробите, Excel зробить це за вас перед завантаженням даних до редактора PQ).
- Тепер виберіть комірку в таблиці та натисніть «З таблиці/діапазону» (вкладка «Отримати та перетворити дані»).
- Після натискання кнопки Excel підтвердить діапазон даних, щоб застосувати до нього таблицю Excel.
- На даний момент у вас є дані в редакторі Power Query і вони виглядають приблизно так, як показано нижче.
- Тут ви можете побачити:
- У рядку формул PQ згенерував код M для таблиці, яку ви завантажили в редактор.
- У лівій частині редактора є панель запитів, де знаходиться список запитів.
- Справа в налаштуваннях запиту є розділ «Застосовані кроки», в якому перелічені всі кроки. Примітка. Ви повинні думати, що ви не виконали жодного «Модифікованого типу», але є крок під назвою «Модифікований тип». Дозвольте мені розповісти вам ІНТЕЛЕКТ POWER QUERY , коли ви завантажуєте дані до редактора, він автоматично перевіряє та застосовує правильні типи даних для всіх стовпців.
Приклади Power Query (поради та рекомендації)
Ви можете навчитися виконувати деякі основні завдання, які ви зазвичай виконуєте, за допомогою функціональних формул в Excel, але за допомогою PowerQuery ви можете зробити це лише за кілька кліків:
1.Замінити значення
У вас є список значень і ви хочете замінити одне або кілька значень чимось іншим. Що ж, за допомогою Power Query Ви можете створити запит і замінити ці значення у найкоротший термін.
У списку нижче вам потрібно замінити моє ім'я Пуніт на Пуніт.
- Спочатку відредагуйте список у розширеному редакторі запитів.
- Після цього в редакторі Power Query перейдіть на вкладку "Перетворення" та натисніть "Замінити значення".
- Тепер у полі "Знайти значення" введіть "Puneet", а в полі "Замінити на" введіть "Punit" і натисніть "ОК".
- Як тільки ви натиснете "ОК", усі значення будуть замінені новими значеннями, і тепер натисніть "Закрити та завантажити", щоб завантажити дані в електронну таблицю.
2. Сортування даних
Як і при звичайному сортуванні, ви можете сортувати дані за допомогою PowerQuery, і я використовую той же список імен, який ви використовували на прикладі вище.
- Спочатку завантажте дані до потужного редактора запитів.
- На вкладці «Головна» є дві кнопки сортування (за зростанням та зменшенням).
- Натисніть одну з цих кнопок, щоб відсортувати.
3. Видалити стовпці
Припустимо, у вас є дані, і вам потрібно видалити з них кілька стовпців. Справа в тому, що вам доведеться видаляти ці стовпці щоразу, коли ви додаєте нові дані, вірно? Але запит потужності може подбати про це.
- Виберіть стовпець або кілька стовпців, які потрібно видалити.
Порада: Існує також опція «Видалити інші стовпці», за допомогою якої ви можете видалити всі невиділені стовпці.
4. Розділити стовпці
Як і у випадку з опцією «текст у стовпець», у запиті потужності є «Розділити стовпець». Дозвольте розповісти вам, як це працює.
- Виберіть стовпець і перейдіть на вкладку «Головна» ➜ «Перетворити» ➜ «Розділити стовпець» ➜ «По роздільнику».
- Виберіть кастом зі списку, що випадає, і введіть в нього «–».
- Тепер у вас є три різні варіанти поділу стовпця.
- Крайній лівий роздільник
- Крайній правий роздільник
- Кожна поява роздільника
Якщо у вас у комірці лише один роздільник, всі три будуть працювати однаково, але якщо у вас більше одного роздільника, вам слід вибрати відповідний.
5. Перейменуйте стовпець
Ви можете просто перейменувати стовпець, клацнувши правою кнопкою миші та вибравши «Перейменувати».
Порада : припустимо, у вас є запит на перейменування стовпця, а хтось інший перейменовує його помилково. Ви можете відновити це ім'я одним клацанням миші.
6. Стовпець, що дублюється
Power Query є проста можливість створити дублікат стовпця. Просто клацніть правою кнопкою миші стовпець, для якого ви хочете створити дублікат стовпця, а потім натисніть "Дублювати стовпець".
7. Транспонувати стовпець чи рядок
У запиті потужності транспонування – це дитяча гра. Так, лише один клік.
- Після завантаження даних до розширеного редактора запитів все, що вам потрібно зробити, це вибрати стовпець(и) або рядок(и).
- Перейдіть на вкладку "Перетворення" ➜ "Таблиця" ➜ "Транспонувати".
8. Замінити/видалити помилки
Зазвичай, щоб замінити або видалити помилки в Excel, можна використовувати опцію «Знайти і замінити» або код VBA. Але у PowerQuery це набагато простіше. Перегляньте стовпець нижче, де у вас є помилки, і ви можете видалити і замінити їх.
Клацнувши правою кнопкою миші по стовпцю, ви побачите обидва варіанти.
9. Змінити тип даних
У вас є дані у стовпці, але вони не в правильному форматі. Тому щоразу потрібно змінювати його формат.
- Спочатку відредагуйте дані у потужному редакторі запитів.
- Після цього виберіть стовпець і перейдіть на вкладку "Перетворення".
- Тепер у розділі «Тип даних» виберіть «Дата» як тип.
10. Додайте стовпець із прикладів
У розширеному запиті можна додати приклад стовпця, який насправді не є прикладом, пов'язаним з поточним стовпцем.
Дозволь мені навести приклад:
Уявіть, що вам потрібні назви днів зі стовпця дат. Замість використання формули або іншого варіанта ви можете використовувати «Додати стовпець з прикладів».
Ось як це зробити:
- Клацніть правою кнопкою миші стовпець та оберіть «Додати стовпець з прикладів».
- Тут ви отримаєте порожній стовпець. Натисніть першу комірку в стовпці, щоб отримати список значень, які ви можете вставити.
Бум! ваша нова колонка тут.
11. Змінюйте коробки
У вас є наступні варіанти зміни регістру тексту PowerQuery.
- Крихітний
- Верхній регістр
- Напишіть кожне слово з великої літери
Ви можете зробити це, клацнувши правою кнопкою миші стовпець і вибравши один із трьох варіантів вище. Або перейдіть на вкладку "Перетворення" ➜ "Текстовий стовпець" ➜ "Формат".
12. Обріжте та очистіть
Щоб очистити дані або видалити непотрібні пробіли, можна використовувати параметри TRIM і CLEAN у PowerQuery. Кроки прості:
- Клацніть правою кнопкою миші стовпець або оберіть усі стовпці, якщо у вас кілька стовпців.
- Перейдіть на вкладку «Перетворення» ➜ Текстовий стовпець ➜ Формат.
- ОБРЕЗАТИ: видалити прогалини в кінці та на початку осередку.
- ЧИСТИТИ: видалити недруковані символи з комірки.
13. Додайте префікс/суфікс
Отже, у вас є список значень і з цього списку ви хочете додати префікс/суфікс до кожного осередку.В Excel ви можете використовувати метод конкатенації, але PowerQuery є простий у використанні варіант для обох.
- Спочатку оберіть стовпець, до якого потрібно додати префікс/суфікс.
- Потім перейдіть на вкладку "Перетворення" ➜ "Текстовий стовпець" ➜ "Формат" ➜ "Додати префікс/Додати суфікс".
- Після того, як ви натиснете будь-який з варіантів, ви побачите діалогове вікно для введення тексту.
14. Вилучення значень
Якщо ви фанат формул, то я впевнений, що ви погоджуєтесь зі мною, що вилучення тексту або числа з комірки вимагає об'єднання різних функцій. Але PowerQuery вирішив багато з цих проблем. У вас є сім способів отримати значення з комірки.
15. Тільки дата чи час
Часто буває, що у вас є дата і час в одному осередку, але вам потрібне одне з них.
- Виберіть стовпець, у якому вказані об'єднані дата та час.
- Якщо ти хочеш:
- Дата : клацніть правою кнопкою миші ➜ Трансформувати ➜ Тільки дата.
- Час : клацніть правою кнопкою миші ➜ Трансформувати ➜ Тільки час.
16. Об'єднайте дату та час
Тепер ви знаєте, як розділити дату та час. Але далі потрібно знати, як їх комбінувати.
- Спочатку оберіть стовпець дати та натисніть «Тільки дата».
- Після цього виберіть обидва стовпці (Дата та час) і перейдіть на вкладку «Перетворення», а у групі «Стовпець дати та часу» перейдіть до «Дата» та натисніть «Об'єднати дату та час».
17. Округлені числа
Ось такі варіанти округлення чисел.
- Округлити у менший бік: округлити число у менший бік.
- Округлити: Округлити число.
- Округлення: Ви можете вибрати, наскільки округлити десяткові знаки.
- Виберіть стовпець і клацніть правою кнопкою миші ➜ Трансформувати ➜ Округлення.
- Округлити у менший бік: округлити число у менший бік.
- Округлити: Округлити число.
- Округлення: Ви можете вибрати, наскільки округлити десяткові знаки.
Примітка. Якщо вибрано опцію «Округлення №3», необхідно ввести кількість десяткових знаків для округлення.
18. Розрахунки
Є опції, які можна використовувати для виконання обчислень (їх багато). Усі ці параметри можна знайти на вкладці «Перетворення» (у групі «Числовий стовпець»).
- Базовий
- Статистика
- Вчений
- Тригонометрія
- Круглий
- Інформація
Щоб виконати будь-яке з цих обчислень, потрібно вибрати стовпець, а потім параметр.
19. Групувати за
Допустимо, у вас є великий набір даних і ви хочете створити зведену таблицю. Ось що вам потрібно зробити:
- На вкладці «Перетворення» натисніть кнопку «Згрупувати за», і ви отримаєте діалогове вікно.
- Тепер у цьому діалоговому вікні виберіть стовпець, який ви хочете згрупувати, і після цього додайте ім'я, виберіть операцію та стовпець, у якому ви маєте значення.
Примітка. У «Групувати за» також є додаткові параметри, які можна використовувати для створення багаторівневої групової таблиці.
20. Видаліть негативні значення
В одній зі своїх публікацій у блозі я перерахував сім методів видалення негативних знаків, і один із них – запит потужності. Клацніть правою кнопкою миші стовпець і перейдіть до параметра "Перетворення", потім натисніть "Абсолютне значення".
Це миттєво видаляє всі негативні знаки із значень.
Більше прикладів
Як завантажити дані в електронну таблицю
Після перетворення даних ви можете завантажити їх в електронну таблицю та використовувати для подальшого аналізу. На головній вкладці є кнопка «Закрити і завантажити», при натисканні на яку ви отримуєте список, що розкривається, що пропонує додаткові параметри:
- Виберіть, як ви хочете відображати ці дані в електронній таблиці.
- Малювання
- Звіт зведеної таблиці:
- Поворот таблиці
- Створити лише одне з'єднання
- Куди ви бажаєте помістити дані?
- Існуючий робочий лист
- Новий робочий лист.
- У той момент, коли ви натискаєте ОК, він додає новий аркуш з даними.
Більше прикладів для вивчення
Автоматично оновлювати запит
З усіх прикладів, які я навів тут, цей найважливіший.
- На вкладці "Дані" натисніть "Запити та з'єднання", і в правій частині вікна ви побачите панель "Запити та з'єднання".
- Тепер клацніть правою кнопкою миші запит, встановіть прапорець «Оновлювати кожні» та введіть хвилини.
Як використовувати формулу та функцію в Power Query
Так само, як ви можете використовувати функції та формули в електронній таблиці Excel, Advanced Query має власний список функцій, які ви можете використовувати.
У PQ вам потрібно додати новий стовпець, що настроюється, щоб додати функцію або формулу.
Візьмемо приклад: У даних нижче (вже в редакторі PQ) у вас є ім'я та прізвище ( ПОСИЛАННЯ СКАЧАТИ ) .
Уявіть, що вам потрібно об'єднати два імені та створити стовпець для повного імені. У цьому випадку ви можете ввести просту формулу для об'єднання двох імен.
- Спочатку перейдіть на вкладку «Додати стовпець» та натисніть «Стовпець користувача».
- Тепер у діалоговому вікні стовпця, що настроюється. введіть ім'я нового стовпця «Повне ім'я» або інше ім'я, яке ви хочете назвати для нового стовпця.
- Формула стовпця користувача - це місце, де вам потрібно ввести формулу. Отже, введіть нижче формулу :
[First Name]&" "&[Last Name]
- Коли ви вводите формулу в «Формулу стовпця, що настроюється», PQ перевіряє введену вами формулу і відображає повідомлення «Синтаксичні помилки не виявлені», а у разі помилки відображається повідомлення про помилку в залежності від типу помилки.
- Якщо ви ввели формулу і в ній немає помилок, просто натисніть кнопку ОК.
- Тепер у кінці даних у вас є новий стовпець, що містить значення двох стовпців (ім'я і прізвище).
Як використовувати функцію в Power Query
Так само ви також можете використовувати функцію при додаванні стовпця, що настроюється, і Power Query має величезний список функцій, які ви можете використовувати.
Давайте розберемося, як використовувати функцію, на простому та легкому прикладі. Я продовжую наведений вище приклад, де ми додали новий стовпець, який поєднує ім'я та прізвище.
Але тепер вам потрібно перетворити текст повного імені у цьому стовпці у верхній регістр. Ви можете використовувати функцію « Text.Upper » . Як випливає з назви, він перетворює текст на верхній регістр.
- Спочатку перейдіть на вкладку «Додати стовпець» і клацніть стовпець, що настроюється.
- Тепер у діалоговому вікні стовпця, що настроюється, введіть ім'я стовпця і формулу, наведену нижче, в полі формули стовпця, що настроюється:
Text.Upper([Full Name])
- І коли ви натискаєте ОК, створюється новий стовпець з усіма іменами у верхньому регістрі.
- Наступне - видалити старий стовпець і перейменувати новий стовпець. Отже, клацніть правою кнопкою миші перший стовпець і виберіть "Видалити".
Усього існує 700 функцій, які ви можете використовувати в запиті потужності при додаванні нового стовпця, і ось повний список, наданий Microsoft для цих функцій, ознайомтеся з ними.
Як змінити запит у PQ
Якщо ви хочете змінити запит, який вже є у вашій книзі, ви можете просто відредагувати його, а потім внести ці зміни. На вкладці "Дані" є кнопка " Запити та з'єднання».
Коли ви натискаєте цю кнопку, праворуч відкривається панель, де перелічені всі запити, які є у поточній книзі.
Ви можете клацнути правою кнопкою миші на ім'я запиту та вибрати "Редагувати", і ви отримаєте його в потужному редакторі запитів для редагування.
При редагуванні запиту ви можете бачити, що всі виконані вами кроки перераховані в розділі «Застосовані кроки», який ви також можете редагувати або виконувати нові кроки.
І як тільки ви закінчите вносити зміни, ви можете просто натиснути кнопку «Закрити та завантажити».
Експортні та імпортні зв'язки
Якщо у вас є з'єднання, яке ви використовували для запиту, і тепер ви хочете поділитися цим з'єднанням з кимось, ви можете експортувати це з'єднання як файл ODC.
У таблиці запитів є кнопка «Експорт з'єднання» , і коли ви натискаєте на неї, вона дозволяє зберегти з'єднання цього запиту у вашій системі.
А якщо ви хочете імпортувати з'єднання, яким користується хтось інший, ви можете просто перейти на вкладку «Дані» та в розділі «Отримати та перетворити» натиснути «Існуючі сполуки» .
Потім натисніть кнопку «Переглянути додаткові відомості», де ви зможете знайти файл підключення, до якого вам надали спільний доступ, та імпортувати його до своєї книги.
PowerQuery (код M)
Як я згадував раніше, для кожного кроку, який ви виконуєте у PowerQuery, він генерує код (у серверній частині), який називається M Code . На вкладці «Головна» є кнопка « Розширений редактор» для перегляду коду.
І коли ви натиснете на розширений редактор, він покаже вам редактор коду, і цей код виглядає так:
M — мова, чутлива до регістру , і, як і всі інші мови, він використовує змінні та вирази. Базова структура коду виглядає так: код починається з вираження LET.
У цьому коді ми маємо дві змінні і надані їм значення. Зрештою, щоб отримати значення, використовувався вираз IN. Тепер, коли ви натиснете «ОК», в результаті буде повернуто значення, надане змінною «Ім'я змінної».
Завітайте на цей ресурс, щоб дізнатися більше про мову Power Query.
В кінці
Power Query - це механізм перетворення даних, який можна використовувати для отримання даних з декількох джерел, очищення та перетворення цих даних, а потім їх подальшого використання в аналізі.
Ви не можете дозволити собі запобігти POWER QUERY. Якщо ви так думаєте, то багато речей, які ми робимо з функціями Excel або кодами VBA, можна автоматизувати за його допомогою, і я впевнений, що цей посібник надихне вас використовувати його все більше і більше.
