Excel Power Query
Для більш зрілої архітектури:
== Power Query і XML ==
Типові помилки:
* Extract — отримати інформаційні дані;
* Transform — перетворити інформаційні дані;
* Load — завантажити результат.; Але постійні бізнес-процеси, довідники, документи, права доступу, API, BI і джерело істини мають бути в [[K2 ERP]], а не в хаотичних Excel-файлах.;== Що переносити в K2 ERP ==
* очистити інформаційні дані;
* прибрати порожні рядки;
* змінити типи;
* перетворити структуру;
* підготувати звіт;
* зробити шаблон для повторної роботи.; Це корисно, якщо потрібно:
K2 ERP показує одне !; BAS
Power Query має змогу підключатися до зовнішніх джерел, з цієї причини потрібна обережність.; # Брати інформаційні дані з K2 ERP, API або BI-вітрини.; |- | Чи є собою санкційні ризики у BAS і 1С?; !; Таблиця результату:
Group By
Renamed Columns
Потрібно знати: як ілюстрація:Merge Queries
</syntaxhighlight> Типові ситуації: Спільне: CSV — один із найчастіших форматів для обміну даними.; |-
Для чого він потрібен?;
FilteredRows = Table.SelectRows(ChangedType, each [Amount] > 0) |
; Окремі продукти 1С і BAS внесені до відкритих переліків програмного забезпечення, забороненого до використання для окремих категорій організацій.; Excel-прайс постачальника → Power Query → перейменування колонок → очищення цін → порівняння з K2 ERP | ; Етап
|-
| продажі та реалізація.xlsx
| CSV з BAS
| Керівник продажів
| Замінити джерелом K2 ERP
|-
| Залишки.xlsx
| SQL BAS
| складський облік
| Перепідключити до BI-вітрини K2 ERP
|-
| фінансовий блок.xlsx
| Excel + ручні інформаційні дані
| Фінансовий директор
| Автоматизувати через K2 ERP
|-
| Прайси.xlsx
| Папка постачальників
| закупівельна діяльність
| Залишити як допоміжний інструмент
|-
| Міграція.xlsx
| BAS CSV + K2 ERP API
| Проєктна команда
| Використати для звірки
|}
Схема:
{| class="wikitable" style="width:100%;"
== Мова Power Query M ==
* клієнти;
* постачальники;
* зарплата;
* собівартість;
* маржа;
* фінансовий блок;
* банківські реквізити;
* персональні інформаційні дані;
* договори;
* залишки;
* ціни;
* API-ключі.; !; {
* [[K2]]
* [[K2 ERP]]
* [[ERP]]
* [[BI]]
* [[Power BI]]
* [[Tableau]]
* [[API]]
* [[SQL]]
* [[JSON]]
* [[XML]]
* [[CSV]]
* [[Data Warehouse]]
* [[Аналітична вітрина]]
* [[Дашборд]]
* [[KPI]]
* [[План-факт]]
* [[Інтеграція з K2 ERP]]
* [[Користувач K2 ERP]]
* [[Ролі K2 ERP]]
* [[Права доступу]]
* [[Журналювання]]
* [[Версія K2 ERP]]
* [[Оновлення K2 ERP]]
* [[ERP на власному сервері]]
* [[Хмарна ERP]]
* [[BAS]]
* [[1С]]
* [[Міграція з BAS]]
* [[Міграція з 1С]]
* [[Заміна BAS]]
* [[Заміна 1С]]
* [[Оновлення BAS]]
* [[Конфігурація BAS]]
* [[Користувач BAS]]
* [[Роль BAS]]
* [[Веб-клієнт BAS]]
* [[Клієнт-серверний режим BAS]]
* [[Файловий режим BAS]]
* [[Web-сервіси 1С]]
* [[JSON 1С]]
* [[Інтеграція з BAS]]
* [[Інтеграція з 1С]]
* [[Інтеграція через файли]]
* [[Інтеграція через XML]]
* [[Українське програмне забезпечення]]
* [[Автоматизація бізнесу]]
* [[Цифрова незалежність]]
* [[Деколонізація обліку]]
[[Категорія:Версія K2 ERP]]
* [https://learn.microsoft.com/uk-ua/power-query/ Документація Power Query на Microsoft Learn]
* [https://learn.microsoft.com/en-us/power-query/power-query-what-is-power-query What is Power Query — Microsoft Learn]
* [https://support.microsoft.com/uk-ua/office/power-query-%D0%B4%D0%BE%D0%B2%D1%96%D0%B4%D0%BA%D0%B8-%D0%B7-excel-2b433a85-ddfb-420b-9cda-fe0e60b82a94 Power Query у Excel — супровід Microsoft]
* [https://learn.microsoft.com/uk-ua/powerquery-m/ Power Query M formula language]
* [https://learn.microsoft.com/uk-ua/powerquery-m/power-query-m-language-specification Специфікація мови Power Query M]
* [https://erp.kyiv.ua Сайт K2 ERP]
* [https://wiki.erp.kyiv.ua Wiki K2 ERP]
* [https://cloud.corp2.eu хмарна інфраструктура K2 ERP]
* [https://cip.gov.ua/ua/statics/perelik-zaboronenogo-do-vikoristannya-programnogo-zabezpechennya-ta-komunikaciinogo-merezhevogo-obladnannya Перелік забороненого до використання програмного забезпечення на сайті Держспецзв’язку]
* [https://cip.gov.ua/ua/news/vidpovidi-na-poshireni-zapitannya-shodo-pereliku-zaboronenogo-programnogo-zabezpechennya-ta-obladnannya Роз’яснення Держспецзв’язку щодо переліку забороненого ПЗ]
* [https://www.president.gov.ua/documents/6012024-52009 Указ Президента України №601/2024]
* [https://zakon.rada.gov.ua/go/601/2024 Указ Президента України №601/2024 на сайті Верховної Ради України]
* [https://t.me/+uIdWI1W6vndkMTAy Telegram-канал K2 ERP]
* [https://t.me/+6jFwAZM6TQliNTdi Група обговорення функціоналу та пропозицій]
* [https://www.linkedin.com/company/k2erp/ LinkedIn K2]
{| class="wikitable" style="width:100%;"
Power Query не є собою ERP-системою.; Формули Excel
|-
| Товар А
| Січень
| 100
|-
| Товар А
| Лютий
| 120
|-
| Товар А
| Березень
| 90
|}
Правильний порядок:
== Приклад: банківська виписка ==
Таблиця Sales у Excel → Power Query → очищення → результат у новій таблиці
* звірки довідників;
* звірки залишків;
* аналізу продажів;
* аналізу закупівель;
* підготовки план-факту;
* перевірки міграції;
* імпорту прайсів;
* підготовки шаблонів завантаження;
* підключення до API;
* обробки CSV/JSON/XML;
* формування тимчасових звітів.; Менеджер
== Зовнішні посилання ==
Причини:
Power Query часто замінює прості макроси для підготовки даних.; У сучасних версіях Excel Power Query доступний через вкладку:
Power Query має змогу отримувати інформаційні дані через web-запити.;[[Категорія:JSON 1С]]
[[Категорія:Data Warehouse]]
* багато дій можна робити без програмування;
* кроки трансформації видно в редакторі;
* можна оновлювати інформаційні дані;
* легше підтримувати типові сценарії;
* менше ризику випадкових змін у формулах;
* можна працювати з різними джерелами.; | Усі Excel-файли Power Query, джерела даних, SQL, API, CSV, XML, JSON, папки, обробки й власників звітів.; !;</div>
[[Категорія:Excel]]
Power Query має змогу:
* звіти показують старі інформаційні дані;
* користувачі приймають рішення для бізнесу на основі неактуальної інформації;
* BAS/1С залишається прихованим джерелом істини;
* старі обробки продовжують працювати;
* санкційні ризики не усунені;
* Excel-звіти не збігаються з K2 ERP;
* інформаційні дані дублюються;
* джерело правди втрачається.; |-
| Що таке M language?;== Приклад: прайс постачальника ==
Під час переходу в K2 ERP такі залежності потрібно знайти.; Лютий Power Query має змогу допомогти обробити банківські файли.; </syntaxhighlight>
Але Power Query не повинен замінювати ERP, BI-систему або контрольоване джерело істини.; Результат Приклад: ] |
; ChangedType = Table.TransformColumnTypes(Source, {{"Date", type date}, {"Amount", type number}}),
</syntaxhighlight> </syntaxhighlight> У багатьох компаніях Power Query має змогу бути підключений до BAS або 1С.; Під час міграції можна використати Power Query.; Power Query Editor — це редактор, у якому користувач системи налаштовує кроки обробки даних.; Залишки K2 ERP CSV/API → Power Query </syntaxhighlight> Приклад: звірка залишків BAS і K2 ERPPower Query і права доступуДля критичних процесів краще використовувати ERP або контрольований BI.; Клієнти з BAS + Клієнти з K2 ERP → Звірка перенесення
Merge Queries — це об’єднання двох таблиць за ключем.; K2 ERP → контрольоване джерело даних → Power Query / BI → звіт </syntaxhighlight> Що таке Excel Power QueryPower Query і локальні шляхиAPI-сценарії мають бути: Простий приклад M-коду: |
;
Потрібно перенести або переосмислити: Power Query використовує мову формул M.; | Через CSV, Excel, JSON, XML, API, SQL-вітрини або BI-шар.; Потрібно розуміти: K2 ERP → Power Query → очищений Excel/CSV → Tableau Applied Steps — це список кроків, які Power Query виконує над даними.; рішення для бізнесу при міграції
|
Чи замінює Power Query ERP?; !;
|
; K2 ERP у цьому процесі має змогу стати основним джерелом контрольованих даних для Excel Power Query, Power BI, Tableau, BI, API, аналітичних вітрин, управлінських звітів і подальшого розвитку автоматизації бізнесу без залежності від старої екосистеми BAS / 1С.;</syntaxhighlight>
Power Query і актуалізація данихДжерела даних Power QueryPower Query-файли потрібно включати в аудит аналітики.; |-
| Що таке ETL?;
Продажі_Січень + Продажі_Лютий + Продажі_Березень → Продажі_Квартал Погано: |
; Найчастіші помилки:
Сценарій: M надає можливість описувати: Power Query комфортно використовувати для обробки прайсів.; як ілюстрація: Краще: Power Query має змогу допомагати готувати управлінські або допоміжні звіти, але не варто робити його єдиним інструментом для критичної регламентованої звітності.; Excel Power Query показує інше Залишки BAS CSV → Power Query Приклад сценарію: | ||||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| Товар А | 100 | 120 | 90 |
Ризики:
Навіть після переходу на K2 ERP старі Excel-файли можуть продовжувати читати BAS/1С.; Excel-файл
- джерела даних;
- формули KPI;
- правила очищення;
- бізнес-логіку звітів;
- структуру довідників;
- шаблони імпорту;
- контрольні звірки;
- аналітичні показники;
- інтеграційні сценарії;
- права доступу;
- регулярні звіти.; * таблиця Excel;
- файл Excel;
- CSV;
- TXT;
- XML;
- JSON;
- папка з файлами;
- web-адреса;
- SQL Server;
- PostgreSQL;
- MySQL;
- OData;
- API;
- SharePoint;
- OneDrive;
- Power BI dataset;
- Dataverse;
- інші джерела через конектори.; # Описати KPI.; K2 ERP + CRM + WMS + сайт → Data Warehouse → BI / Tableau / Power BI
!;
Power Query і API
</syntaxhighlight>
Power Query → Web.Contents → API K2 ERP → JSON → таблиця Excel
Можна автоматизувати:
Promoted Headers
| Extract | Отримання даних | Завантажити CSV із продажами |
| Transform | Перетворення даних | Прибрати зайві колонки, змінити типи, очистити дати |
| Load | Завантаження результату | Завантажити очищену таблицю в Excel |
Це корисно, якщо потрібно:
; Приклад:
Power Query і Data Warehouse
Applied Steps
Excel Power Query — це потужний інструмент для підготовки даних у Excel.; Не варто переносити:
"number": "SO-001",
Changed Type
* завантажити XML;
* розгорнути вузли;
* перетворити вкладені структури в таблиці;
* вибрати потрібні поля;
* об’єднати XML-файли.; # Не зберігати секрети у відкритому вигляді.; | Це інструмент Excel для імпорту, очищення, перетворення й завантаження даних.;[[Категорія:Заміна 1С]]
План продажів Excel + Факт продажів K2 ERP → Power Query → план-факт звіт
Під час переходу з [[BAS]] або [[1С]] у [[K2 ERP]] потрібно обов’язково перевірити всі Excel-файли з Power Query.;== Power Query і K2 ERP ==
== Power Query і макроси Excel ==
Папка → усі файли → Power Query → об’єднання → єдина таблиця
* використовувати контрольований API;
* обмежувати права токена;
* створювати окремого сервісного користувача;
* журналювати запити;
* не розсилати файл із ключами;
* регулярно змінювати ключі;
* не давати Power Query зайвий доступ.; Що означає
== Помилка: старі BAS-запити в Excel ==
Типові джерела:
K2 ERP → експорт CSV залишків → Power Query → очищення → звіт по складах
Це дуже корисно для BI.;[[Категорія:Користувач K2 ERP]]
K2 ERP API → Power Query → очищення JSON → таблиця Excel → звіт керівника
!;[[Категорія:Конфігурація BAS]]
!; Після Unpivot:
in
Перевага в з цієї причини, що користувач системи має змогу бачити логіку перетворення і повторно застосовувати її при оновленні даних.; # Описати трансформації.; Погані підходи:
[[Категорія:Бізнес-аналітика]]
Приклад:
* імпорт CSV;
* розділення призначення платежу;
* очищення дат;
* виділення ІПН;
* зіставлення контрагентів;
* групування платежів;
* підготовка до завантаження в ERP.;
</syntaxhighlight> Простий приклад: </syntaxhighlight>
- Товар А ключовий 100 100 0 Товар Б ключовий 50 48 -2 !;
<syntaxhighlight lang="text">
Removed Columns
[[Категорія:Дашборди]]
|-
| Excel Power Query
| Підготовка даних у Excel-файлах і таблицях
|-
| Power BI Power Query
| Підготовка даних для BI-моделей і дашбордів
|}
Номенклатура BAS → Power Query → очищення → список проблем → підготовка до K2 ERP
<syntaxhighlight lang="text">
[[Категорія:Power Query M]]
'''Підхід K2 ERP.''' Power Query можна використовувати для тимчасових звірок, імпорту, аналітики й контролю міграції.; Power Query сприяє готувати й аналізувати інформаційні дані, але ERP залишається системою обліку.;<syntaxhighlight lang="json">
Приклад до:
* Excel-файл вивантажується з BAS;
* CSV формується зовнішньою обробкою 1С;
* Power Query читає SQL-таблицю BAS;
* Power Query читає XML з BAS;
* Power Query читає JSON із web-сервісу 1С;
* бухгалтер оновлює Excel-звіт з BAS.; !; !; Перевірка
== Power Query і CSV ==
[[Категорія:Користувач BAS]]
== Advanced Editor ==
== Ризик прихованої залежності від BAS/1С ==
<syntaxhighlight lang="text">
!; Power Query має змогу бути корисним інструментом для міграції, звірок і тимчасових звітів, але цифрова незалежність залежить від джерела даних.; | Для автоматизації роботи з CSV, Excel, XML, JSON, SQL, API, папками, звітами й аналітичними даними.; Основне призначення
Краще:
== Вступ ==
== Приклад: очищення номенклатури ==
[[Категорія:Оновлення K2 ERP]]
як ілюстрація:
Краще не редагувати результат запиту вручну, а змінювати логіку в Power Query або джерелі.; # Визначити джерело даних.; Приклад
== Power Query і міграція в K2 ERP ==
Наслідки:
Сценарій:
API K2 ERP → JSON → Power Query → таблиця замовлень → Excel-звіт
!; * джерела даних;
* трансформації;
* фільтри;
* об’єднання;
* функції;
* параметри;
* умови;
* обробку помилок;
* роботу зі списками;
* роботу з таблицями;
* роботу з JSON;
* роботу з XML;
* web-запити.;== Таблиця інвентаризації Power Query ==
* порівняння довідників;
* пошуку дублікатів;
* звірки залишків;
* звірки взаєморозрахунків;
* перевірки цін;
* перевірки серій;
* перевірки характеристик;
* очищення CSV;
* об’єднання файлів;
* контролю завантаження;
* формування протоколів помилок.;
</syntaxhighlight>
Power Query не повинен створювати нове “джерело істини” у файлі Excel.; Power Query має змогу бути корисний на ранніх етапах, але не повинен замінювати повноцінне сховище даних у великих компаніях.; Джерело 2 Схема: Під час міграції Power Query має змогу бути дуже корисним.; !; |- | Який центральний ризик?; Вхідні інформаційні дані
складський облік вірить старому BAS-звіту
[[Категорія:Excel Power Query]]
* переглянути інформаційні дані;
* видалити колонки;
* змінити типи;
* фільтрувати;
* сортувати;
* групувати;
* об’єднувати;
* розгортати вкладені структури;
* створювати обчислювані колонки;
* переглядати applied steps;
* відкривати Advanced Editor.; завдяки наявності '''Головне.''' Excel Power Query — це інструмент для отримання і перетворення даних у Excel.; * хто має доступ до файлу;
* де файл зберігається;
* чи є собою паролі;
* чи є собою API-токени;
* чи можна оновити інформаційні дані;
* чи можна експортувати результат;
* чи файл не розсилається поштою.; Якщо Power Query раніше отримував інформаційні дані з BAS або 1С, під час переходу на [[K2 ERP]] потрібно перевірити всі Excel-файли, запити, SQL-підключення, CSV/XML/JSON-вивантаження, API, обробки і ручні звіти, щоб не залишити BAS/1С прихованим джерелом даних.; Номенклатура
[[Категорія:Автоматизація бізнесу]]
{| class="wikitable" style="width:100%;"
== Power Query і регламентні звіти ==
Excel-файли з Power Query можуть містити чутливі інформаційні дані.;
</syntaxhighlight>
Якщо Power Query читає BAS/1С, залежність залишається.;
"date": "2026-05-15",
Unpivot Columns корисний, коли інформаційні дані зберігаються в “широкому” форматі.; # Не використовувати стару BAS/1С як джерело після міграції.; Кількість
як ілюстрація:
додатково використовуються розділи:
* актуальні інформаційні дані;
* контрольоване джерело;
* менше ручної роботи;
* зрозумілі звірки;
* контроль KPI;
* відмову від старих BAS-вивантажень;
* основу для BI;
* цифрову незалежність.; Power Query
Не варто зберігати API-ключі відкритим текстом в Excel-файлі.; Питання
<syntaxhighlight lang="text">
|-
| Іваненко
| 1 000 000
| 920 000
| 92%
|-
| Петренко
| 800 000
| 860 000
| 107,5%
|}
[[Категорія:Українське програмне забезпечення]]
</syntaxhighlight>
- імпорт даних;
- трансформації;
- M language;
- підготовка таблиць;
- очищення даних;
- об’єднання джерел.; Місяць
!; як ілюстрація:
Merged Queries
- аналітичних вітрин;
- звітних баз;
- data warehouse;
- проміжних таблиць;
- BI-шару;
- підготовлених вибірок.; складський облік
Power Query Editor
Правильний підхід. Excel Power Query має використовуватися для контрольованої підготовки й звірки даних, а джерелом істини мають бути K2 ERP, API, BI-вітрини або data warehouse з описаними правилами, доступами й відповідальними.; !; Power Query надає можливість користувачам отримувати інформаційні дані з файлів, таблиць Excel, CSV, XML, JSON, SQL-баз, web-адрес, API, папок, ERP-систем, CRM, BI-сховищ та інших джерел, а потім перетворювати їх у зручний формат для аналізу, звітів, зведених таблиць, BI або подальшої обробки.; * джерела даних;
- SQL-підключення;
- web-посилання;
- API;
- шляхи до файлів;
- папки обміну;
- CSV/XML/JSON;
- зовнішні обробки BAS;
- власників файлів;
- розклади актуалізація;
- критичні звіти;
- формули KPI;
- права доступу.; Power Query має змогу:
!;== Append Queries ==
Найгірший сценарій. організація переходить на K2 ERP, але керівництво продовжує користуватися Excel-файлами Power Query, які читають стару BAS/1С.; продажі та реалізація + Довідник номенклатури → продажі та реалізація з категоріями товарів
K2 ERP → BI-вітрина / Data Warehouse → Tableau
- Визначити задачу.;https://api.company.ua/orders?token=SECRET123
Якщо Power Query рахує показники, потрібно описати формули.; Різниця
- продажі та реалізація з ПДВ чи без ПДВ;
- маржа валова чи чиста;
- залишок фізичний чи доступний;
- дебіторка вся чи прострочена;
- план по відвантаженню чи оплаті;
- клієнти активні чи всі.;
Але для постійної BI-архітектури краще: Це корисно для:
!;!; Окремо варто відзначити перетворення, об’єднання і підготовки даних з різних джерел виступає ключовою рисою імпорту забезпечується через Excel Power Query.; Power Query доречний для:
Power Query надає можливість:
Power Query як ETL
Source
Він не повинен замінювати:
Power Query і цифрова незалежність
Перед переходом з BAS/1С у K2 ERP потрібно знайти всі Excel-файли з Power Query.; | Ні.; Потрібно перевірити:
- видалити колонки;
- перейменувати колонки;
- змінити тип даних;
- видалити порожні рядки;
- видалити дублікати;
- замінити значення;
- розділити колонку;
- об’єднати колонки;
- фільтрувати рядки;
- сортувати;
- групувати;
- об’єднати запити;
- додати обчислювану колонку;
- розгорнути таблицю;
- розгорнути JSON;
- розгорнути XML.;== Типові помилки Power Query ==
- звіт більше не відповідає джерелу;
- формули рахують неправильно;
- актуалізація перезапише ручні зміни;
- неможливо зрозуміти, що було змінено;
- керівник бачить неперевірені інформаційні дані.; У ньому можна:
Ризики:
- щоденні CSV-продажі;
- Excel-прайси постачальників;
- XML-документи;
- JSON-файли;
- банківські виписки;
- файли залишків.; Можна знайти:
!; як ілюстрація:
переважні аспекти Power Query: Merge по номенклатурі і складу → Різниця → Звіт звірки Краще використовувати аналітичні вітрини або API.; Power Query — це допоміжний інструмент підготовки й аналізу даних.; як ілюстрація:
- підключення до невідомих web-адрес;
- збережені логіни й паролі;
- API-ключі у файлі;
- доступ до SQL без обмежень;
- файли з чутливими даними;
- розсилання Excel-файлів;
- неконтрольоване актуалізація;
- обхід ERP-прав;
- залежність від локальних шляхів;
- відкриття файлів із ненадійних джерел.; Power Query часто використовують як ETL-інструмент.; Джерело Power Query
Помилка: немає опису KPI
Power Query і SQL
Приклад для K2 ERP:
Де знаходиться Power Query в Excel
Tableau має власні механізми підключення до даних, але Power Query має змогу використовуватися як проміжний інструмент.; Найпростіший сценарій — отримати інформаційні дані з таблиці Excel.; Для малого або середнього сценарію:
Небезпека Power Query у з цієї причини, що користувач системи має змогу після завантаження вручну змінити результат.;<syntaxhighlight lang="text">
K2 ERP → Power Query → Excel-звіт
== Що не варто переносити ==
<div style="border:3px solid #2e7d32; background:#e8f5e9; padding:14px; margin:16px 0;">
Найчастіші дії:
[[Категорія:Заміна BAS]]
[[Категорія:KPI]]
[[Категорія:Безпека]]
Group By надає можливість агрегувати інформаційні дані.;== Power Query і єдине джерело істини ==
Бухгалтер вірить ERP
як ілюстрація:
* порожні значення;
* дублікати;
* неправильні дати;
* текст замість чисел;
* зайві пробіли;
* різні формати номерів;
* некоректні валюти;
* відсутні артикули;
* відсутні категорії;
* помилкові залишки;
* незбіг між BAS і K2 ERP.; Інструмент
Power Query часто використовують як простий ETL-інструмент у Excel.; K2 ERP
Power Query має змогу:
* обхід прав ERP;
* навантаження на робочу базу;
* доступ до чутливих даних;
* неправильне трактування таблиць;
* залежність від внутрішньої структури;
* поломка запитів після актуалізація.; Товар
[[Категорія:Tableau]]
</syntaxhighlight>
Як правильно використовувати Power Query з K2 ERP
Помилка: Power Query замість ERP
Типові трансформації Power Query
<syntaxhighlight lang="text">
як ілюстрація:
!; Filtered Rows
== Power Query і API-ключі ==
Приклад JSON:
!; |-
| Що перевірити при міграції з BAS/1С?; | Це мова формул Power Query, якою описуються джерела й трансформації даних.; План
'''Цифрова незалежність.''' Excel Power Query має допомагати переходу на [[K2 ERP]], а не залишати компанію залежною від старих BAS/1С-запитів, ручних Excel-файлів і неофіційних джерел даних.; Січень
* ручні Excel-виправлення;
* дублікати довідників;
* старі BAS-запити;
* локальні шляхи;
* приховані API-ключі;
* неактуальні CSV;
* файли без власника;
* звіти без опису формул;
* хаотичні Power Query-запити;
* застарілі XML/JSON-обміни;
* підключення до санкційно ризикової BAS/1С.; З Excel Power Query не потрібно переносити самі хаотичні файли як основу процесу.;== Приклад міграційної звірки ==
* Excel-файл можна змінити вручну;
* запит має змогу зламатися;
* джерело має змогу бути неактуальним;
* формула має змогу бути не задокументована;
* користувач системи має змогу мати неправильну версію файлу.; Без Power Query така робота часто виконується вручну: копіювання, вставка, фільтри, формули, пошук і заміна, зведені таблиці, ручне видалення рядків, ручне об’єднання файлів.;[[Категорія:Web-сервіси 1С]]
<syntaxhighlight lang="text">
<syntaxhighlight lang="text">
У зв’язці з [[K2 ERP]] Power Query має змогу бути корисним для:
<syntaxhighlight lang="text">
<syntaxhighlight lang="text">
"orders": [
!;== Power Query і Power BI ==
* імпортувати інформаційні дані;
* видаляти зайві рядки;
* змінювати типи даних;
* перейменовувати колонки;
* об’єднувати таблиці;
* фільтрувати інформаційні дані;
* розділяти колонки;
* об’єднувати колонки;
* видаляти дублікати;
* групувати інформаційні дані;
* об’єднувати файли з папки;
* підключатися до SQL;
* отримувати інформаційні дані з web;
* працювати з JSON;
* працювати з XML;
* створювати повторювані сценарії обробки;
* оновлювати результат однією кнопкою.;== Power Query і звичайні формули Excel ==
Excel часто застосовують, коли потрібно в компаніях як універсальний інструмент для звітів, перевірок, планування, імпорту, експорту, звірок і ручної аналітики.; Відповідь
* Отримати інформаційні дані;
* Запити і підключення;
* Оновити все;
* З таблиці або діапазону;
* З тексту/CSV;
* З web;
* З бази даних;
* З папки;
* З XML;
* З JSON.; * коли натискали “Оновити”;
* чи оновлюються всі запити;
* чи доступне джерело;
* чи не змінилася структура файлу;
* чи не змінився API;
* чи не змінився пароль;
* чи не зламався шлях до папки;
* чи не змінилися назви колонок.;== Power Query і якість даних ==
* звіт не відповідає новій ERP;
* старі інформаційні дані змішуються з новими;
* керівники бачать неправильну аналітику;
* BAS залишається активною;
* санкційні ризики не усунені;
* міграція фактично не завершена.; | Extract, Transform, Load — отримати інформаційні дані, перетворити їх і завантажити результат.; задача
!; {| class="wikitable" style="width:100%;"
Приклад:
[[Категорія:Кібербезпека]]
* імпортувати CSV;
* визначити роздільник;
* змінити кодування;
* перетворити типи колонок;
* прибрати зайві рядки;
* об’єднати багато CSV-файлів із папки.; Товар
* бухгалтер отримує CSV із банку;
* менеджер отримує Excel-прайс постачальника;
* керівник хоче звіт по продажах;
* складський облік вивантажує залишки;
* аналітик об’єднує інформаційні дані з ERP і CRM;
* фінансовий директор готує план-факт;
* організація мігрує з BAS у [[K2 ERP]];
* потрібно порівняти старі й нові довідники;
* потрібно очистити дублікати номенклатури;
* потрібно завантажити інформаційні дані з API.; У результаті нова ERP вже функціонує, але рішення для бізнесу приймаються за старими даними.; '''Excel Power Query''' — це інструмент для підключення до джерел даних, їх очищення, трансформації й завантаження результату в Excel.;== Power Query і BAS/1С ==
[[Категорія:Power Query]]
* спільну папку;
* SharePoint;
* OneDrive з контрольованим доступом;
* API;
* BI-вітрину;
* серверний шлях;
* документований каталог обміну.; Групування
* змінити складну логіку;
* додати параметри;
* створити функцію;
* оптимізувати запит;
* виправити помилку;
* скопіювати запит;
* підключити API;
* обробити вкладений JSON;
* зробити умовну логіку.; |-
| Як Power Query має змогу працювати з [[K2 ERP]]?; C:\Users\Ivanenko\Desktop\sales.csv
Але пряме підключення до робочої ERP-бази потрібно робити обережно.; Power Query має змогу отримувати інформаційні дані з різних джерел.; Факт
CSV із продажами → Power Query → очищення колонок → фільтр по даті → таблиця Excel → зведена таблиця
|-
| Контрагенти
| BAS CSV
| K2 ERP API
| Merge по ЄДРПОУ
|-
| Номенклатура
| BAS Excel
| K2 ERP CSV
| Пошук дублікатів
|-
| Залишки
| BAS звіт
| K2 ERP звіт
| Порівняння кількості
|-
| Ціни
| Старий прайс
| K2 ERP ціни
| Пошук відхилень
|-
| Взаєморозрахунки
| BAS ОСВ
| K2 ERP заборгованість
| Порівняння сум
|}
XML часто застосовується в обмінах, старих інтеграціях і регламентованих форматах.; План має змогу бути в Excel, а факт — у K2 ERP.;== Power Query і Tableau ==
Ризики:
Для чого використовують Power Query
Результат:
Як не треба робити
У зв’язці з K2 ERP Power Query має змогу бути корисний для:
- документовані;
- захищені;
- версійовані;
- обмежені правами;
- журналювані;
- стабільні;
- погоджені з адміністратором.; У зв’язці з K2 ERP Excel Power Query має змогу використовуватися для підготовки управлінських звітів, тимчасових аналітичних файлів, імпорту довідників, перевірки залишків, звірки даних після міграції з BAS або 1С, підключення до API, обробки CSV/JSON/XML-вивантажень і створення проміжних BI-наборів.; # Переконатися, що джерело актуальне.; * імпорт прайсу;
- очищення назв;
- зміну типів;
- видалення порожніх рядків;
- зіставлення артикулів;
- розрахунок нових цін;
- підготовку файлу для завантаження.; # Перевірити актуалізація.; !; # Задокументувати власника файлу.; Він надає можливість імпортувати, очищати, об’єднувати, трансформувати й оновлювати інформаційні дані з різних джерел: Excel, CSV, XML, JSON, SQL, API, web, папок і аналітичних вітрин.; # Обмежити доступ до файлу.; "amount": 12500
Power Query застосовується не тільки в Excel, а й у Power BI.; Він користувачі можуть автоматизувати ручну роботу з файлами, звітами, CSV, XML, JSON, SQL і API, але не замінює ERP-систему.; </syntaxhighlight>
Коротко
Power Query і безпека
let }
FilteredRows
!; |}
Power Query і JSON
Папка XML-файлів → Power Query → розгортання структури → таблиця документів
- міграційних звірок;
- перевірки довідників;
- звірки залишків;
- підготовки прайсів;
- аналізу продажів;
- план-факт звітів;
- тимчасової аналітики;
- підключення до API;
- обробки CSV/XML/JSON;
- підготовки даних для BI.;</syntaxhighlight>
це інструмент у Microsoft Excel; додатково реалізовано очищення.; Джерело 1
Одна з найкорисніших функцій — об’єднання файлів з папки.;* швидкого прототипу;
* перевірки даних;
* разової підготовки;
* міграційної звірки;
* тимчасових Excel-джерел.; У іншого користувача такий файл не працюватиме.; | Power Query має змогу залишитися підключеним до старої BAS/1С і показувати неактуальні або ризикові інформаційні дані.;== Power Query і аудит ==
Різниця:
* підключення до старої BAS після міграції;
* локальні шляхи в запитах;
* відсутність власника файлу;
* ручні правки результату;
* незадокументовані формули;
* приховані API-ключі;
* прямий доступ до робочої SQL-бази;
* відсутність контролю прав;
* Excel-файл розсилається поштою;
* інформаційні дані не оновлюються;
* структура джерела змінилася;
* Power Query застосовується замість ERP.;
Див.; додатково
!; | Так.; Інакше стара BAS/1С має змогу залишитися прихованим джерелом даних навіть після запуску нової ERP.; !; Виконання
Power Query використовують для:
* дублікати назв;
* порожні артикули;
* різні одиниці виміру;
* зайві пробіли;
* різні регістри;
* старі товари;
* номенклатуру без групи;
* товари без штрихкоду;
* товари без категорії.;
<syntaxhighlight lang="text"> Приклад:
Приклад: план-факт
Ризики:
Погано:
- отримати JSON із web-запиту;
- розгорнути списки;
- розгорнути записи;
- перетворити вкладені поля в таблицю;
- очистити інформаційні дані;
- завантажити результат в Excel.;<syntaxhighlight lang="powerquery">
Керівник вірить Excel Після переходу в K2 ERP користувач системи має змогу продовжувати натискати “Оновити” у старому Excel-файлі, який читає BAS.; Якщо Power Query читає K2 ERP, API або контрольовану BI-вітрину, організація отримує:
Power Query можна використати для пошуку проблем у довіднику.;
Advanced Editor надає можливість редагувати M-код напряму.; !; Для постійних процесів інформаційні дані мають зберігатися й контролюватися в K2 ERP, а Power Query має бути допоміжним інструментом для підготовки, звірки або аналізу.; Березень
Power Query не замінює формули Excel, але вирішує інші задачі.; !;== Інвентаризація Power Query перед міграцією ==
Power Query і папка з файлами
інформаційні дані → Отримати інформаційні дані / Get Data
Power Query і ручні правки
- хто створив файл;
- хто ним користується;
- звідки інформаційні дані;
- як часто оновлюється;
- чи є собою чутливі інформаційні дані;
- чи є собою доступ до BAS/1С;
- чи є собою API-ключі;
- чи є собою ручні правки;
- чи потрібен цей звіт після переходу в K2 ERP.; ETL означає:
Його можна використовувати для: Приклад: як ілюстрація: |- | Замовлення | По клієнту | Сума продажів по клієнтах |- | Залишки | По складу | Загальний залишок по складу |- | Оплати | По даті | Денний рух коштів |}
З урахуванням санкційних, юридичних і кібербезпекових ризиків BAS та 1С, Power Query-аудит має бути частиною ширшої BI-міграції: потрібно перевірити Excel-звіти, SQL-запити, CSV/XML/JSON-файли, API, старі обробки, ручні вивантаження й усі джерела, які впливають на управлінські рішення для бізнесу.; {| class="wikitable" style="width:100%;" {{SEO