Перейти до вмісту

Excel Power Query

Матеріал з K2 ERP Wiki

Power Query здатна допомагати готувати управлінські або допоміжні звіти, але не варто робити його єдиним інструментом для критичної регламентованої звітності. як приклад: Потрібно знати: Схема:

План здатна бути в Excel, а факт — у K2 ERP. {| class="wikitable" style="width:100%;" Без словника KPI звіти можуть трактуватися по-різному. # Визначити джерело даних. "number": "SO-001",

Power Query і JSON

Приклад: ! Одна з найкорисніших функцій — об’єднання файлів з папки. 

* обхід прав ERP;
* навантаження на робочу базу;
* доступ до чутливих даних;
* неправильне трактування таблиць;
* залежність від внутрішньої структури;
* поломка запитів після оновлення версій. Але постійні бізнес-процеси, довідники, документи, права доступу, API, BI і джерело істини мають бути в [[K2 ERP]], а не в хаотичних Excel-файлах. як приклад:

[[Категорія:Excel Power Query]]

Папка XML-файлів → Power Query → розгортання структури → таблиця документів

* імпортувати CSV;
* визначити роздільник;
* змінити кодування;
* перетворити типи колонок;
* прибрати зайві рядки;
* об’єднати багато CSV-файлів із папки. Він дає можливість імпортувати, очищати, об’єднувати, трансформувати й оновлювати інформаційні дані з різних джерел: Excel, CSV, XML, JSON, SQL, API, web, папок і аналітичних вітрин. Окремі продукти [[1С]] і [[BAS]] внесені до відкритих переліків програмного забезпечення, забороненого до використання для окремих категорій організацій.<syntaxhighlight lang="text">

 ChangedType = Table.TransformColumnTypes(Source, {{"Date", type date}, {"Amount", type number}}),

* файл не знайдено;
* колонка не знайдена;
* тип даних неправильний;
* API повернув помилку;
* немає доступу;
* змінився пароль;
* змінився формат CSV;
* JSON має іншу структуру;
* XML має інші вузли;
* SQL-запит не виконується;
* таблиця перейменована;
* користувач системи не має прав. Джерело 2

== Power Query і права доступу ==

== Power Query і регламентні звіти ==

let

* завантажити XML;
* розгорнути вузли;
* перетворити вкладені структури в таблиці;
* вибрати потрібні поля;
* об’єднати XML-файли. План

Краще використовувати аналітичні вітрини або API. ! # Задокументувати власника файлу. # Перевірити оновлення версій. Excel Power Query показує інше

! Номенклатура

[[Категорія:JSON 1С]]

== Applied Steps ==

!== Зовнішні посилання ==

Power Query оптимізує знаходити проблеми якості даних.== Power Query і оновлення версій даних ==

! Merge по номенклатурі і складу → Різниця → Звіт звірки

Power Query здатна підключатися до зовнішніх джерел, тому потрібна обережність.
Це корисно, якщо потрібно:
== Power Query і API ==

[[Категорія:Web-сервіси 1С]]
<div style="border:3px solid #1565c0; background:#e3f2fd; padding:14px; margin:16px 0;">
== Мова Power Query M ==

Для більш зрілої архітектури:

Його можна використовувати для:

Unpivot Columns корисний, коли інформаційні дані зберігаються в “широкому” форматі.<syntaxhighlight lang="text">

<syntaxhighlight lang="text">
 FilteredRows
Під час переходу з [[BAS]] або [[1С]] у [[K2 ERP]] потрібно обов’язково перевірити всі Excel-файли з Power Query. це інструмент у [[Microsoft Excel]]; так само реалізовано очищення. Для малого або середнього сценарію:

C:\Users\Ivanenko\Desktop\sales.csv

* використовувати контрольований API;
* обмежувати права токена;
* створювати окремого сервісного користувача;
* журналювати запити;
* не розсилати файл із ключами;
* регулярно змінювати ключі;
* не давати Power Query зайвий доступ. Для критичних процесів краще використовувати ERP або контрольований BI. |-
| Що таке ETL?[[Категорія:Оновлення BAS]]

M дає можливість описувати:
Group By дає можливість агрегувати інформаційні дані. Групування
{{SEO
|title=Excel Power Query — імпорт даних, ETL, M language, CSV, XML, JSON, SQL, API, K2 ERP і міграція з BAS
|description=Excel Power Query: що це таке, як працює імпорт і перетворення даних в Excel, ETL, запити, M language, CSV, XML, JSON, SQL, API, інтеграція з K2 ERP, Power BI, Tableau, аналітика, типові помилки і міграція звітів з BAS та 1С.
|keywords=Excel Power Query, Power Query, Power Query Excel, Get & Transform, ETL Excel, M language, Power Query M, імпорт даних Excel, перетворення даних Excel, CSV Excel, XML Excel, JSON Excel, SQL Excel, API Excel, BI, Power BI, Tableau, K2 ERP, інтеграція з K2 ERP, міграція з BAS, міграція з 1С, заміна BAS, заміна 1С, українська ERP, санкції BAS, санкції 1С, цифрова незалежність
|image=https://erp.kyiv.ua
}}
! Після переходу в [[K2 ERP]] користувач системи здатна продовжувати натискати “Оновити” у старому Excel-файлі, який читає BAS. як приклад:

* звіт не відповідає новій ERP;
* старі інформаційні дані змішуються з новими;
* керівники бачать неправильну аналітику;
* BAS залишається активною;
* санкційні ризики не усунені;
* міграція фактично не завершена. Merged Queries

 ]

* звірки довідників;
* звірки залишків;
* аналізу продажів;
* аналізу закупівель;
* підготовки план-факту;
* перевірки міграції;
* імпорту прайсів;
* підготовки шаблонів завантаження;
* підключення до API;
* обробки CSV/JSON/XML;
* формування тимчасових звітів. * Extract — отримати інформаційні дані;
* Transform — перетворити інформаційні дані;
* Load — завантажити результат. У іншого користувача такий файл не працюватиме. як приклад:

* міграційних звірок;
* перевірки довідників;
* звірки залишків;
* підготовки прайсів;
* аналізу продажів;
* план-факт звітів;
* тимчасової аналітики;
* підключення до API;
* обробки CSV/XML/JSON;
* підготовки даних для BI. Power Query часто використовують як ETL-інструмент. # Описати трансформації. Power Query здатна отримувати інформаційні дані з різних джерел.<syntaxhighlight lang="text">

* джерела даних;
* трансформації;
* фільтри;
* об’єднання;
* функції;
* параметри;
* умови;
* обробку помилок;
* роботу зі списками;
* роботу з таблицями;
* роботу з JSON;
* роботу з XML;
* web-запити. Що означає

K2 ERP показує одне
<syntaxhighlight lang="json">
'''Power Query Editor''' — це редактор, у якому користувач системи налаштовує кроки обробки даних. {| class="wikitable" style="width:100%;"
Power Query здатна бути корисний на ранніх етапах, але не повинен замінювати повноцінне сховище даних у великих компаніях.== Power Query і цифрова незалежність ==
Power Query використовують для:
[[Категорія:Інтеграція]]
== Power Query і Data Warehouse ==
[[Категорія:Журналювання]]
Applied Steps — це список кроків, які Power Query виконує над даними. Power Query не повинен створювати нове “джерело істини” у файлі Excel. ETL означає:

* переглянути інформаційні дані;
* видалити колонки;
* змінити типи;
* фільтрувати;
* сортувати;
* групувати;
* об’єднувати;
* розгортати вкладені структури;
* створювати обчислювані колонки;
* переглядати applied steps;
* відкривати Advanced Editor. Приклад

!<syntaxhighlight lang="text">

<syntaxhighlight lang="text">

<syntaxhighlight lang="text">

* підключення до невідомих web-адрес;
* збережені логіни й паролі;
* API-ключі у файлі;
* доступ до SQL без обмежень;
* файли з чутливими даними;
* розсилання Excel-файлів;
* неконтрольоване оновлення версій;
* обхід ERP-прав;
* залежність від локальних шляхів;
* відкриття файлів із ненадійних джерел. # Переконатися, що джерело актуальне. # Визначити задачу. ! Power Query здатна:

* імпортувати інформаційні дані;
* видаляти зайві рядки;
* змінювати типи даних;
* перейменовувати колонки;
* об’єднувати таблиці;
* фільтрувати інформаційні дані;
* розділяти колонки;
* об’єднувати колонки;
* видаляти дублікати;
* групувати інформаційні дані;
* об’єднувати файли з папки;
* підключатися до SQL;
* отримувати інформаційні дані з web;
* працювати з JSON;
* працювати з XML;
* створювати повторювані сценарії обробки;
* оновлювати результат однією кнопкою. Виконання

== Group By ==

Під час міграції можна використати Power Query. Різниця

Power Query дає можливість цифровізувати ці дії. Типові помилки:

== Приклад: банківська виписка ==
[[Категорія:Права доступу]]
== Power Query і CSV ==

Power Query застосовується для не тільки в Excel, а й у [[Power BI]]. * порожні значення;
* дублікати;
* неправильні дати;
* текст замість чисел;
* зайві пробіли;
* різні формати номерів;
* некоректні валюти;
* відсутні артикули;
* відсутні категорії;
* помилкові залишки;
* незбіг між BAS і K2 ERP.[[Категорія:Автоматизація бізнесу]]
<syntaxhighlight lang="text">
 "orders": [

Ризики:

Потрібно контролювати:

Краще використовувати:

* Excel-файл вивантажується з BAS;
* CSV формується зовнішньою обробкою 1С;
* Power Query читає SQL-таблицю BAS;
* Power Query читає XML з BAS;
* Power Query читає JSON із web-сервісу 1С;
* бухгалтер оновлює Excel-звіт з BAS. |-
| Як Power Query здатна працювати з [[K2 ERP]]?

Power Query і Excel-таблиці

Power Query і ручні правки

Power Query здатна отримувати інформаційні дані через web-запити. Клієнти з BAS + Клієнти з K2 ERP → Звірка перенесення

Unpivot Columns

|- | Extract | Отримання даних | Завантажити CSV із продажами |- | Transform | Перетворення даних | Прибрати зайві колонки, змінити типи, очистити дати |- | Load | Завантаження результату | Завантажити очищену таблицю в Excel |}

У зв’язці з K2 ERP Power Query здатна бути корисним для: Або: 

! Power Query дає можливість користувачам отримувати інформаційні дані з файлів, таблиць Excel, CSV, XML, JSON, SQL-баз, web-адрес, API, папок, ERP-систем, CRM, BI-сховищ та інших джерел, а потім перетворювати їх у зручний формат для аналізу, звітів, зведених таблиць, [[BI]] або подальшої обробки. Можна цифровізувати:

! !== Advanced Editor ==
Потрібно перенести або переосмислити:
Наслідки:
</div>
Небезпека Power Query у тому, що користувач системи здатна після завантаження вручну змінити результат. Місяць
== Power Query і аудит ==
|-
| Контрагенти
| BAS CSV
| K2 ERP API
| Merge по ЄДРПОУ
|-
| Номенклатура
| BAS Excel
| K2 ERP CSV
| Пошук дублікатів
|-
| Залишки
| BAS звіт
| K2 ERP звіт
| Порівняння кількості
|-
| Ціни
| Старий прайс
| K2 ERP ціни
| Пошук відхилень
|-
| Взаєморозрахунки
| BAS ОСВ
| K2 ERP заборгованість
| Порівняння сум
|}

[[Категорія:SQL]]

Краще не редагувати результат запиту вручну, а змінювати логіку в Power Query або джерелі.

Power Query і помилки оновлення версій

Перевага в тому, що користувач системи здатна бачити логіку перетворення і повторно застосовувати її при оновленні даних. Power Query здатна підключатися до SQL-баз. Товар

Приклад: очищення номенклатури

</syntaxhighlight>

Ризики:

Найчастіші дії:

Цифрова незалежність. Excel Power Query має допомагати переходу на K2 ERP, а не залишати компанію залежною від старих BAS/1С-запитів, ручних Excel-файлів і неофіційних джерел даних. |- | Чи виступає як санкційні ризики у BAS і 1С? ! Джерело Power Query

Як правильно використовувати Power Query з K2 ERP

JSON часто застосовується для в API. * Документація Power Query на Microsoft Learn

API K2 ERP → JSON → Power Query → таблиця замовлень → Excel-звіт XML часто застосовується для в обмінах, старих інтеграціях і регламентованих форматах. Дія Power Query

Спільне:

Приклад: прайс постачальника

Таблиця результату: Продажі_Січень + Продажі_Лютий + Продажі_Березень → Продажі_Квартал У багатьох компаніях Power Query здатна бути підключений до BAS або 1С. задача

Типові ситуації:

! Джерело 1

  • отримати JSON із web-запиту;
  • розгорнути списки;
  • розгорнути записи;
  • перетворити вкладені поля в таблицю;
  • очистити інформаційні дані;
  • завантажити результат в Excel. # Використовувати Power Query як допоміжний інструмент, а не як ERP.

складський облік вірить старому BAS-звіту 

* джерела даних;
* SQL-підключення;
* web-посилання;
* API;
* шляхи до файлів;
* папки обміну;
* CSV/XML/JSON;
* зовнішні обробки BAS;
* власників файлів;
* розклади оновлення версій;
* критичні звіти;
* формули KPI;
* права доступу.<syntaxhighlight lang="text">

* бухгалтер отримує CSV із банку;
* менеджер отримує Excel-прайс постачальника;
* керівник хоче звіт по продажах;
* складський облік вивантажує залишки;
* аналітик об’єднує інформаційні дані з ERP і CRM;
* фінансовий директор готує план-факт;
* компанія-користувач мігрує з BAS у [[K2 ERP]];
* потрібно порівняти старі й нові довідники;
* потрібно очистити дублікати номенклатури;
* потрібно завантажити інформаційні дані з API.== Помилка: старі BAS-запити в Excel ==

Expanded Table
Power Query → Web.Contents → API K2 ERP → JSON → таблиця Excel
!== Що переносити в K2 ERP ==

Приклад сценарію:
== Power Query і XML ==
== Power Query і якість даних ==
! | Extract, Transform, Load — отримати інформаційні дані, перетворити їх і завантажити результат.<div style="border:3px solid #b71c1c; background:#ffebee; padding:14px; margin:16px 0;">
Power Query не виступає як ERP-системою. Питання

* таблиця Excel;
* файл Excel;
* CSV;
* TXT;
* XML;
* JSON;
* папка з файлами;
* web-адреса;
* SQL Server;
* PostgreSQL;
* MySQL;
* OData;
* API;
* SharePoint;
* OneDrive;
* Power BI dataset;
* Dataverse;
* інші джерела через конектори.== Коротко ==

Результат:

<syntaxhighlight lang="text">

* джерела даних;
* формули KPI;
* правила очищення;
* бізнес-логіку звітів;
* структуру довідників;
* шаблони імпорту;
* контрольні звірки;
* аналітичні показники;
* інтеграційні сценарії;
* права доступу;
* регулярні звіти. продажі та реалізація + Довідник номенклатури → продажі та реалізація з категоріями товарів

Ризики:
[[Категорія:Цифрова незалежність України]]
[[Категорія:CSV]]
<syntaxhighlight lang="text">
== Вступ ==
}
|-
| Товар А
| базовий
| 100
| 100
| 0
|-
| Товар Б
| базовий
| 50
| 48
| -2
|}

</syntaxhighlight>

  • дублікати назв;
  • порожні артикули;
  • різні одиниці виміру;
  • зайві пробіли;
  • різні регістри;
  • старі товари;
  • номенклатуру без групи;
  • товари без штрихкоду;
  • товари без категорії.== Power Query і міграція в K2 ERP ==

</syntaxhighlight> Power Query використовує мову формул M. Append Queries — це додавання рядків з однієї таблиці до іншої. * будувати критичну аналітику тільки на Excel-файлах;

  • залишати Power Query підключеним до BAS/1С після міграції;
  • зберігати API-ключі в Excel;
  • підключатися напряму до робочої SQL-бази без контролю;
  • використовувати локальні шляхи;
  • вручну правити результат запиту;
  • не документувати формули;
  • не контролювати доступ до файлів;
  • розсилати файли з чутливими даними;
  • не перевіряти джерело даних;
  • ігнорувати санкційні й кібербезпекові ризики BAS/1С. !

Причини: З Excel Power Query не потрібно переносити самі хаотичні файли як основу процесу. |- | Excel Power Query | Підготовка даних у Excel-файлах і таблицях |- | Power BI Power Query | Підготовка даних для BI-моделей і дашбордів |}

! | Так. Окремо варто відзначити перетворення, об’єднання і підготовки даних з різних джерел виступає ключовою рисою імпорту забезпечується через Excel Power Query. Формули Excel

K2 ERP → BI-вітрина / Data Warehouse → Tableau

  • змінити складну логіку;
  • додати параметри;
  • створити функцію;
  • оптимізувати запит;
  • виправити помилку;
  • скопіювати запит;
  • підключити API;
  • обробити вкладений JSON;
  • зробити умовну логіку. Перевірка
* коли натискали “Оновити”;
* чи оновлюються всі запити;
* чи доступне джерело;
* чи не змінилася структура файлу;
* чи не змінився API;
* чи не змінився пароль;
* чи не зламався шлях до папки;
* чи не змінилися назви колонок. * швидкого прототипу;
* перевірки даних;
* разової підготовки;
* міграційної звірки;
* тимчасових Excel-джерел. ! {| class="wikitable" style="width:100%;"

!== Приклад міграційної звірки ==

! Під час переходу в K2 ERP такі залежності потрібно знайти.== Power Query і єдине джерело істини == 

"amount": 12500

! Власник

  • продажі та реалізація з ПДВ чи без ПДВ;
  • маржа валова чи чиста;
  • залишок фізичний чи доступний;
  • дебіторка вся чи прострочена;
  • план по відвантаженню чи оплаті;
  • клієнти активні чи всі. !

Висновок

Excel Power Query — це інструмент для підключення до джерел даних, їх очищення, трансформації й завантаження результату в Excel. | Через CSV, Excel, JSON, XML, API, SQL-вітрини або BI-шар. # Обмежити доступ до файлу. так само використовуються розділи:

  • хто має доступ до файлу;
  • де файл зберігається;
  • чи виступає як паролі;
  • чи виступає як API-токени;
  • чи можна оновити інформаційні дані;
  • чи можна експортувати результат;
  • чи файл не розсилається поштою.</syntaxhighlight>

! Результат Приклад JSON:

Перед переходом з BAS/1С у K2 ERP потрібно знайти всі Excel-файли з Power Query. Погано:

Приклад:

Де знаходиться Power Query в Excel

конкурентні переваги Power Query:

Power Query доречний для:

Але пряме підключення до робочої ERP-бази потрібно робити обережно.== Power Query і макроси Excel == 

{

Не варто зберігати API-ключі відкритим текстом в Excel-файлі. | Power Query здатна залишитися підключеним до старої BAS/1С і показувати неактуальні або ризикові інформаційні дані. ! * очистити інформаційні дані;

  • прибрати порожні рядки;
  • змінити типи;
  • перетворити структуру;
  • підготувати звіт;
  • зробити шаблон для повторної роботи. Приклад:
критично про BAS і 1С. BAS та 1С мають санкційні, юридичні й кібербезпекові ризики в Україні.

Renamed Columns

Це інтуїтивно для об’єднання однакових файлів за періоди. У зв’язці з K2 ERP Power Query здатна бути корисний для: !</syntaxhighlight>

Power Query як ETL

  • щоденні CSV-продажі;
  • Excel-прайси постачальників;
  • XML-документи;
  • JSON-файли;
  • банківські виписки;
  • файли залишків. Лютий
  • звіти показують старі інформаційні дані;
  • користувачі приймають рішення для бізнесу на основі неактуальної інформації;
  • BAS/1С залишається прихованим джерелом істини;
  • старі обробки продовжують працювати;
  • санкційні ризики не усунені;
  • Excel-звіти не збігаються з K2 ERP;
  • інформаційні дані дублюються;
  • джерело правди втрачається. Простий приклад M-коду:

https://api.company.ua/orders?token=SECRET123 </syntaxhighlight> Power Query дає можливість: Power Query часто замінює прості макроси для підготовки даних. * звіт більше не відповідає джерелу;

  • формули рахують неправильно;
  • оновлення версій перезапише ручні зміни;
  • неможливо зрозуміти, що було змінено;
  • керівник бачить неперевірені інформаційні дані. Power Query не замінює формули Excel, але вирішує інші задачі. Excel-прайс постачальника → Power Query → перейменування колонок → очищення цін → порівняння з K2 ERP

як приклад:

План продажів Excel + Факт продажів K2 ERP → Power Query → план-факт звіт

Power Query і безпека

З урахуванням санкційних, юридичних і кібербезпекових ризиків BAS та 1С, Power Query-аудит має бути частиною ширшої BI-міграції: потрібно перевірити Excel-звіти, SQL-запити, CSV/XML/JSON-файли, API, старі обробки, ручні вивантаження й усі джерела, які впливають на управлінські рішення для бізнесу. |- | Що перевірити при міграції з BAS/1С? * продажі та реалізація по клієнтах;

  • залишки по складах;
  • сума оплат по днях;
  • кількість замовлень по менеджерах;
  • витрати по статтях;
  • маржа по категоріях.</syntaxhighlight>

Після Unpivot:

Якщо Power Query читає BAS/1С, залежність залишається. Кількість

  • спільну папку;
  • SharePoint;
  • OneDrive з контрольованим доступом;
  • API;
  • BI-вітрину;
  • серверний шлях;
  • документований каталог обміну. }

Tableau має власні механізми підключення до даних, але Power Query здатна використовуватися як проміжний інструмент.

Типові помилки Power Query

  • підключення до старої BAS після міграції;
  • локальні шляхи в запитах;
  • відсутність власника файлу;
  • ручні правки результату;
  • незадокументовані формули;
  • приховані API-ключі;
  • прямий доступ до робочої SQL-бази;
  • відсутність контролю прав;
  • Excel-файл розсилається поштою;
  • інформаційні дані не оновлюються;
  • структура джерела змінилася;
  • Power Query застосовується для замість ERP. Часта проблема — запит прив’язаний до локального шляху. K2 ERP → Power Query → очищений Excel/CSV → Tableau

Без Power Query така робота часто виконується вручну: копіювання, вставка, фільтри, формули, пошук і заміна, зведені таблиці, ручне видалення рядків, ручне об’єднання файлів. # Описати KPI. | Ні. Power Query

Погані підходи:

Power Query інтуїтивно використовувати для обробки прайсів. Березень

in

</syntaxhighlight>

Append Queries

Приклад до: 

Source = Excel.CurrentWorkbook(){[Name="Sales"]}[Content],

Типові трансформації Power Query

  • документовані;
  • захищені;
  • версійовані;
  • обмежені правами;
  • журналювані;
  • стабільні;
  • погоджені з адміністратором. як приклад:

Power Query і SQL

</syntaxhighlight>

Але Power Query не повинен замінювати ERP, BI-систему або контрольоване джерело істини.
Під час міграції Power Query здатна бути дуже корисним.== Power Query і Power BI ==

Source

! |-
| Який провідний ризик?[[Категорія:Українське програмне забезпечення]]
Сценарій:
Excel Power Query — це потужний інструмент для підготовки даних у Excel.<syntaxhighlight lang="text">

! Якщо Power Query раніше отримував інформаційні дані з BAS або 1С, під час переходу на [[K2 ERP]] потрібно перевірити всі Excel-файли, запити, SQL-підключення, CSV/XML/JSON-вивантаження, API, обробки і ручні звіти, щоб не залишити BAS/1С прихованим джерелом даних. |-
| Що таке M language? * аналітичних вітрин;
* звітних баз;
* data warehouse;
* проміжних таблиць;
* BI-шару;
* підготовлених вибірок. Менеджер

* порівняння довідників;
* пошуку дублікатів;
* звірки залишків;
* звірки взаєморозрахунків;
* перевірки цін;
* перевірки серій;
* перевірки характеристик;
* очищення CSV;
* об’єднання файлів;
* контролю завантаження;
* формування протоколів помилок.== Для чого використовують Power Query ==

* Excel-файл можна змінити вручну;
* запит здатна зламатися;
* джерело здатна бути неактуальним;
* формула здатна бути не задокументована;
* користувач системи здатна мати неправильну версію файлу.== Приклад: звірка залишків BAS і K2 ERP ==

'''Правильний підхід.''' Excel Power Query має використовуватися для контрольованої підготовки й звірки даних, а джерелом істини мають бути [[K2 ERP]], API, BI-вітрини або data warehouse з описаними правилами, доступами й відповідальними. Power Query здатна:
'''[[K2 ERP]]''' у цьому процесі здатна стати основним джерелом контрольованих даних для Excel Power Query, [[Power BI]], [[Tableau]], [[BI]], [[API]], аналітичних вітрин, управлінських звітів і подальшого розвитку автоматизації бізнесу без залежності від старої екосистеми [[BAS]] / [[1С]]. Інструмент

== Power Query Editor ==

Він не повинен замінювати:
 "date": "2026-05-15",
== Джерела даних Power Query ==

Найчастіші помилки:

* Отримати інформаційні дані;
* Запити і підключення;
* Оновити все;
* З таблиці або діапазону;
* З тексту/CSV;
* З web;
* З бази даних;
* З папки;
* З XML;
* З JSON. Можна знайти:
K2 ERP + CRM + WMS + сайт → Data Warehouse → BI / Tableau / Power BI
Найпростіший сценарій — отримати інформаційні дані з таблиці Excel. * видалити колонки;
* перейменувати колонки;
* змінити тип даних;
* видалити порожні рядки;
* видалити дублікати;
* замінити значення;
* розділити колонку;
* об’єднати колонки;
* фільтрувати рядки;
* сортувати;
* групувати;
* об’єднати запити;
* додати обчислювану колонку;
* розгорнути таблицю;
* розгорнути JSON;
* розгорнути XML. API-сценарії мають бути:

[[Категорія:Версія K2 ERP]]

* очищення даних;
* імпорту файлів;
* об’єднання таблиць;
* автоматизації звітів;
* підготовки даних для зведених таблиць;
* підготовки даних для [[Power BI]];
* підготовки даних для [[Tableau]];
* перевірки даних після міграції;
* обробки прайсів;
* завантаження банківських файлів;
* аналізу продажів;
* аналізу залишків;
* звірки контрагентів;
* очищення номенклатури;
* підключення до API;
* підключення до SQL;
* обробки JSON/XML.<syntaxhighlight lang="text">
Excel-файли з Power Query можуть містити чутливі інформаційні дані. Excel-файл

Power Query здатна допомогти обробити банківські файли. Товар
|-
| продажі та реалізація.xlsx
| CSV з BAS
| Керівник продажів
| Замінити джерелом K2 ERP
|-
| Залишки.xlsx
| SQL BAS
| складський облік
| Перепідключити до BI-вітрини K2 ERP
|-
| фінансовий блок.xlsx
| Excel + ручні інформаційні дані
| Фінансовий директор
| цифровізувати через K2 ERP
|-
| Прайси.xlsx
| Папка постачальників
| закупівельна діяльність
| Залишити як допоміжний інструмент
|-
| Міграція.xlsx
| BAS CSV + K2 ERP API
| Проєктна команда
| Використати для звірки
|}

K2 ERP → контрольоване джерело даних → Power Query / BI → звіт

Не варто переносити:
  • клієнти;
  • постачальники;
  • зарплата;
  • собівартість;
  • маржа;
  • фінансовий блок;
  • банківські реквізити;
  • персональні інформаційні дані;
  • договори;
  • залишки;
  • ціни;
  • API-ключі. Сценарій:

Ризик прихованої залежності від BAS/1С

  • довідники;
  • документи;
  • права доступу;
  • проведення;
  • складський обліковий облік;
  • фінансовий обліковий облік;
  • журналювання;
  • API;
  • контроль бізнес-процесів;
  • єдине джерело істини. |-
class="wikitable" style="width:100%;"

У сучасних версіях Excel Power Query доступний через вкладку:

Інвентаризація Power Query перед міграцією

Якщо Power Query рахує показники, потрібно описати формули.

Див. так само

</syntaxhighlight>

Потрібно розуміти:
Усі Excel-файли Power Query, джерела даних, SQL, API, CSV, XML, JSON, папки, обробки й власників звітів. Залишки BAS CSV → Power Query

інформаційні дані → Отримати інформаційні дані / Get Data Merge Queries — це об’єднання двох таблиць за ключем. Окремі продукти 1С і BAS внесені до переліків забороненого програмного забезпечення для окремих категорій організацій в Україні. Power Query оптимізує готувати й аналізувати інформаційні дані, але ERP залишається системою обліку. Основне призначення

Вхідні інформаційні дані

</syntaxhighlight>

Що не варто переносити

Що таке Excel Power Query

Залишки K2 ERP CSV/API → Power Query

Changed Type як приклад:

Товар А 100 120 90

Power Query і Tableau

</syntaxhighlight> як приклад: Потрібно перевірити: 

|-
| Товар А
| Січень
| 100
|-
| Товар А
| Лютий
| 120
|-
| Товар А
| Березень
| 90
|}

Excel часто застосовують, коли потрібно в компаніях як універсальний інструмент для звітів, перевірок, планування, імпорту, експорту, звірок і ручної аналітики. Відповідь
|-
| Що таке Excel Power Query? ![[Категорія:Аналітична вітрина]]

== Power Query і локальні шляхи ==

інформаційні дані в Power Query оновлюються не завжди автоматизовано. Він користувачі можуть цифровізувати ручну роботу з файлами, звітами, CSV, XML, JSON, SQL і API, але не замінює ERP-систему. Папка → усі файли → Power Query → об’єднання → єдина таблиця

* хто створив файл;
* хто ним користується;
* звідки інформаційні дані;
* як часто оновлюється;
* чи виступає як чутливі інформаційні дані;
* чи виступає як доступ до BAS/1С;
* чи виступає як API-ключі;
* чи виступає як ручні правки;
* чи потрібен цей звіт після переходу в K2 ERP. | Для автоматизації роботи з CSV, Excel, XML, JSON, SQL, API, папками, звітами й аналітичними даними. BAS
[[Категорія:K2 ERP]]

</syntaxhighlight>

Замовлення По клієнту Сума продажів по клієнтах
Залишки По складу Загальний залишок по складу
Оплати По даті Денний рух коштів
Це інструмент Excel для імпорту, очищення, перетворення й завантаження даних. # Не використовувати стару BAS/1С як джерело після міграції.</syntaxhighlight>

Правильний порядок: У ньому можна: </syntaxhighlight> K2 ERP → експорт CSV залишків → Power Query → очищення → звіт по складах Ризики:

Іваненко 1 000 000 920 000 92%
Петренко 800 000 860 000 107,5%

Advanced Editor дає можливість редагувати M-код напряму. через Головне. Excel Power Query — це інструмент для отримання і перетворення даних у Excel. У результаті нова ERP вже функціонує, але рішення для бізнесу приймаються за старими даними. # Перенести постійні звіти в BI або K2 ERP. Погано: Простий приклад: Filtered Rows 

Таблиця Sales у Excel → Power Query → очищення → результат у новій таблиці
Power Query-файли потрібно включати в аудит аналітики. K2 ERP

__TOC__

Приклад:

<syntaxhighlight lang="text">

Помилка: немає опису KPI

Power Query здатна бути корисним інструментом для міграції, звірок і тимчасових звітів, але цифрова незалежність залежить від джерела даних. # Брати інформаційні дані з K2 ERP, API або BI-вітрини. Січень 

FilteredRows = Table.SelectRows(ChangedType, each [Amount] > 0)

== Помилка: Power Query замість ERP ==
CSV — один із найчастіших форматів для обміну даними. ! |-
| Чи замінює Power Query ERP? ! ![[Категорія:Користувач K2 ERP]]

== Power Query і звичайні формули Excel ==

* актуальні інформаційні дані;
* контрольоване джерело;
* менше ручної роботи;
* зрозумілі звірки;
* контроль KPI;
* відмову від старих BAS-вивантажень;
* основу для BI;
* цифрову незалежність.<syntaxhighlight lang="text">
{| class="wikitable" style="width:100%;"
Бухгалтер вірить ERP
Приклад для [[K2 ERP]]:
[[Категорія:Безпека]]
K2 ERP → Power Query → Excel-звіт
як приклад:
== Таблиця інвентаризації Power Query ==
Приклад:
[[Категорія:Конфігурація BAS]]
Power Query здатна:

Навіть після переходу на K2 ERP старі Excel-файли можуть продовжувати читати BAS/1С.== Приклад: план-факт ==

Це корисно, якщо потрібно: K2 ERP API → Power Query → очищення JSON → таблиця Excel → звіт керівника Номенклатура BAS → Power Query → очищення → список проблем → підготовка до K2 ERP </syntaxhighlight>

Розрахунок у клітинці інтуїтивно Не основне призначення Очищення великої таблиці Можливо, але складно інтуїтивно Об’єднання 50 CSV-файлів Незручно Дуже інтуїтивно Повторюваний імпорт Ручна робота оновлення версій запиту Підключення до API Складно Можливо через web-запити Підготовка BI-даних Обмежено інтуїтивно

Power Query і API-ключі

Але для постійної BI-архітектури краще:

</syntaxhighlight>

  • багато дій можна робити без програмування;
  • кроки трансформації видно в редакторі;
  • можна оновлювати інформаційні дані;
  • легше підтримувати типові сценарії;
  • менше ризику випадкових змін у формулах;
  • можна працювати з різними джерелами. Це дуже корисно для BI. {
  • імпорт даних;
  • трансформації;
  • M language;
  • підготовка таблиць;
  • очищення даних;
  • об’єднання джерел. # Не редагувати результат вручну. |}
У зв’язці з K2 ERP Excel Power Query здатна використовуватися для підготовки управлінських звітів, тимчасових аналітичних файлів, імпорту довідників, перевірки залишків, звірки даних після міграції з BAS або 1С, підключення до API, обробки CSV/JSON/XML-вивантажень і створення проміжних BI-наборів. Краще: Power Query можна використати для пошуку проблем у довіднику. Різниця: 
Приклад:
'''Підхід K2 ERP.''' Power Query можна використовувати для тимчасових звірок, імпорту, аналітики й контролю міграції. Факт

'''Найгірший сценарій.''' компанія-користувач переходить на [[K2 ERP]], але керівництво продовжує користуватися Excel-файлами Power Query, які читають стару BAS/1С.[[Категорія:Користувач BAS]]

== Merge Queries ==

Це корисно для:
!== Power Query і K2 ERP ==

[[Категорія:Data Warehouse]]
Схема:
Типові джерела:
== Як не треба робити ==
як приклад:
Power Query — це допоміжний інструмент підготовки й аналізу даних.== Power Query і папка з файлами ==

[[Категорія:ETL]]

<syntaxhighlight lang="text">

[[Категорія:Інтеграція з K2 ERP]]
Керівник вірить Excel
CSV із продажами → Power Query → очищення колонок → фільтр по даті → таблиця Excel → зведена таблиця

[[Категорія:Заміна BAS]]

== Power Query і BAS/1С ==
</div>
Приклад:

Removed Columns

[[Категорія:Заміна 1С]]

!


  • ручні Excel-виправлення;
  • дублікати довідників;
  • старі BAS-запити;
  • локальні шляхи;
  • приховані API-ключі;
  • неактуальні CSV;
  • файли без власника;
  • звіти без опису формул;
  • хаотичні Power Query-запити;
  • застарілі XML/JSON-обміни;
  • підключення до санкційно ризикової BAS/1С. Для постійних процесів інформаційні дані мають зберігатися й контролюватися в K2 ERP, а Power Query має бути допоміжним інструментом для підготовки, звірки або аналізу. Етап

Але для складної автоматизації VBA або інші інструменти можуть усе ще використовуватися. рішення для бізнесу при міграції

  • імпорт прайсу;
  • очищення назв;
  • зміну типів;
  • видалення порожніх рядків;
  • зіставлення артикулів;
  • розрахунок нових цін;
  • підготовку файлу для завантаження. # Не зберігати секрети у відкритому вигляді. складський облік

Power Query часто використовують як простий ETL-інструмент у Excel. Якщо Power Query читає K2 ERP, API або контрольовану BI-вітрину, компанія-користувач отримує:

Краще:

  • імпорт CSV;
  • розділення призначення платежу;
  • очищення дат;
  • виділення ІПН;
  • зіставлення контрагентів;
  • групування платежів;
  • підготовка до завантаження в ERP. Інакше стара BAS/1С здатна залишитися прихованим джерелом даних навіть після запуску нової ERP. | Це мова формул Power Query, якою описуються джерела й трансформації даних. як приклад:
Promoted Headers
Отримано з https://wiki.kyiv.ua/index.php?title=Excel_Power_Query&oldid=2033