У цьому підручнику ви дізнаєтесь про Power Query Language Flow і деякі з його передових методів. Ви також дізнаєтеся, як легко трансформувати свої дані, щоб отримати найкращі результати. Перетворення даних оптимізує ваш звіт і зробить його переконливим.
Зміст
Встановіть типи даних без потоку мови Power Query
Мінімум перетворень, які ви завжди повинні виконувати, це перейменування та налаштування типів даних для ваших стовпців.
використовуються для класифікації значень у M . Значок поруч із назвою стовпця показує тип даних, призначений для поля. У цьому зразку таблиці ви можете побачити піктограму ABC123 поруч із назвами стовпців. Ця піктограма представляє будь-який тип даних, який вказує на те, що тип даних не було призначено цьому стовпцю.
Поза книгами ввімкнено автоматичне визначення типу. Це автоматично генерує кроки зміненого типу у ваших запитах. Навіть якщо це ввімкнено, завжди перевіряйте цеправильно вгадує типи даних для кожного зі стовпців у всіх ваших таблицях.
Щоб увімкнути або вимкнути цей параметр, виконайте такі дії.
Під параметром «Глобальне» ви можете вибрати один із трьох доступних варіантів визначення типу .
У параметрі «Поточний файл» ви можете перемикати параметри «Виявлення типу» залежно від глобальних налаштувань.
Встановити типи даних за допомогою вкладки «Перетворення».
Інтерфейс користувача пропонує кілька способів встановлення типів даних для ваших стовпців. У розділі «Будь-який стовпець» вкладки «Перетворення» ви знайдете кнопку «Визначити тип даних» .
Якщо вибрати всі стовпці та натиснути цю кнопку, буде автоматично встановлено типи даних для всіх стовпців. Однак, оскільки він сканує лише 200 верхніх рядків, вам все одно потрібно перевірити, чи Power Query має правильний тип даних.
Якщо вибрати стовпець із таблиці, ви побачите його тип даних у розділі Будь-який стовпець.
Якщо натиснути кнопку спадного меню та вибрати Текст, значок у вибраному стовпці зміниться з ABC123 на просто ABC .
Потім ви побачите крок «Змінений тип» на панелі «Застосовані кроки».
На вкладці «Домашня сторінка» ви також знайдете можливість установити тип даних.
Встановлення типів даних за допомогою заголовка стовпця
Інший спосіб — клацнути правою кнопкою миші заголовок і вибрати Змінити тип . Звідти ви можете вибрати відповідний тип даних для своїх стовпців.
Встановіть типи даних за допомогою значків стовпців
Найпоширеніший спосіб встановлення типів даних — клацнути піктограму поруч із назвою стовпця. Він покаже вам усі доступні типи даних.
У зразку таблиці встановіть тип даних стовпця OrderDate на Date .
Залежно від типу даних вибраного стовпця Power Query надає набір перетворень і параметрів, які застосовуються до цього конкретного типу даних на вкладці «Трансформація», вкладці « Додати стовпець » і в розділі «Розумний фільтр» .
Отже, якщо ви виберете стовпець дати та перейдете на вкладку «Трансформація», ви побачите параметри під кнопкою «Дата».
Однак якщо вибрати текстовий стовпець, параметри під кнопкою «Дата» більше не будуть доступні.
Для інтелектуального фільтрування текстові стовпці мають текстові фільтри, а стовпці дат — фільтри дат.
Для наступного стовпця змініть ім’я стовпця на Customer Key і тип даних на Whole Number .
Далі змініть тип даних стовпців каналу, коду валюти та коду складу на текст.
Після цього змініть ім’я індексу регіону доставки на ключ регіону доставки та його тип даних на ціле число . Потім виконайте те саме для стовпців Індекс опису продукту та Кількість замовлення .
Нарешті, для стовпців «Ціна за одиницю», «Загальний рядок» і «Вартість за одиницю» встановіть тип даних « Фіксоване десяткове число» .
Встановіть типи даних за допомогою Power Query Language Flow
Через зміни в стовпцях на панелі «Застосовані кроки» було створено багато кроків. Проблема полягає в тому, що через виконання одного типу перетворень кілька разів вибіркастає неефективним. Це те, чого вам слід уникати.
Щоб зробити ваш запит ефективним, спробуйте створити один крок, застосувавши це конкретне перетворення до всіх своїх стовпців, перш ніж створювати інший крок.
Скопіюйте запит на зразок таблиці та перейменуйте його в Best Practice . На панелі «Застосовані кроки» клацніть правою кнопкою миші перше перетворення, виконане в попередньому запиті, і виберіть «Видалити до кінця» . Потім підтвердьте видалення кроку в діалоговому вікні «Видалити крок» .
Крім того, ось кілька найкращих практик. По-перше, це створити параметри, які містять розташування джерела даних. Це полегшує вирішення проблем, коли ім’я файлу було змінено.
Щоб створити параметри, натисніть «Керувати параметрами» на вкладці «Домашня сторінка» та виберіть « Новий параметр» .
Інший спосіб — клацнути правою кнопкою миші на панелі запитів і вибрати «Новий параметр» .
Після цього з’явиться діалогове вікно керування параметрами . Назвіть параметр FileLocation і встановіть Type на Text. Для запропонованих значень установіть значення «Список значень» , щоб ви могли вставити свій рядок і змінити або додати кілька розташувань, між якими можна перемикатися.
Далі перейдіть до Провідника файлів і виберіть файл. Скопіюйте шлях і вставте параметри. Після завершення натисніть OK .
Поверніться до запиту Best Practice і клацніть крок Source на панелі Applied Steps. Потім змініть жорстко закодований шлях до файлу в рядку формул за допомогою FileLocation .
Видаліть непотрібні стовпці
Видаліть усі непотрібні стовпці, щоб заощадити місце та покращити продуктивність. Переносьте лише ті дані, які вам потрібні, оскільки додавати таблиці та стовпці набагато легше, ніж їх видаляти.
Найпростіший спосіб зробити це за допомогою кроку «Вибрати стовпці» на вкладці «Домашня сторінка». Якщо натиснути цю кнопку, з’явиться діалогове вікно, у якому можна вибрати стовпці, які потрібно зберегти. Ви повинні розробити та сформувати свої таблиці з певною метою, щоб найкраще відповідати аналізу, який ви збираєтеся виконати.
Звідти зніміть вибір стовпців, які вам не потрібні у вашій таблиці. Для цього зразка таблиці потужність стовпця OrderNumber висока. Найкраще зняти вибір цього стовпця, оскільки він впливає на розмір файлу та загальну продуктивність.
Якщо вам не потрібні дані про місцезнаходження для аналізу зразкового запиту, також краще зняти прапорець «Індекс регіону доставки» . Нарешті, оскільки в таблиці вже є ціна за одиницю та кількість, стовпець «Загальний рядок» не потрібен.
Після скасування вибору стовпців натисніть OK . Якщо ви хочете змінити вибір стовпців, просто зніміть значок шестірні біля назви кроку на панелі «Застосовані кроки».
Призначте відповідні типи даних для стовпців і обмежте рядки
Далі призначте типи даних для всіх стовпців. Виберіть усі стовпці та натисніть «Визначити тип даних» на вкладці «Перетворення».
Визначити тип даних автоматично визначає типи даних стовпців на основі сканування перших 200 рядків. Отже, перевірте та переконайтеся, що Power Query встановлює правильні типи даних.
Найкраще обмежити кількість рядків. Якщо ваш фінансовий рік починається 1 липня, ви можете пропустити дані за червень або встановити параметр.
Однак, якщо ви хочете мати можливість змінювати значення параметра в . Після того, як ви опублікуєте свій звіт, вам потрібно мати текстовий або десятковий тип.
Щоб упоратися з цим, створіть фільтр даних, натиснувши кнопку спадного списку в стовпці OrderDate. Далі натисніть «Фільтри дати» та виберіть «Після» .
У діалоговому вікні змініть перший параметр на наступне або дорівнює, а потім введіть дату. У цьому прикладі введено 1 липня 2014 року.
Після того, як ви натиснете OK , фільтр буде застосовано у вашій таблиці. Далі створіть параметр і назвіть його DatesFrom . Встановіть Тип на Текст і введіть дату в параметр Поточне значення.
Тепер поверніться до запиту Best Practice і замініть внутрішню #date на Date.From(DatesFrom) .
Натискання галочки без функції Date.From поверне помилку. Це тому, що DatesFrom є типом Text, тоді як поле має тип Date. Функція Date.From перетворює текст на дату.
Перейменуйте всі стовпці, які не будуть приховані в моделі даних. Ім’я має бути лаконічним, самоописуючим і зручним для користувача. Майте на увазі, що ви розробляєте модель даних для людей, які збираються використовувати ваш звіт.
Об’єднайте зайві кроки в потокі мови Power Query
Наступне, що потрібно зробити, це об’єднати зайві кроки (такі як перейменування, видалення та зміна типів даних стовпців). Більше того, такі кроки, як зміна порядку стовпців, є речами, на які слід звернути увагу під час пошуку надмірностей.
Таблиці, які ви завантажуєте в модель даних, ніколи не відображаються у вашому звіті. Це робить порядок стовпців нерелевантним.
Ще одна найкраща практика — перейменувати кроки на панелі «Застосовані кроки». Назви кроків є описовими та використовуються як змінні в коді M.
Імена, які містять пробіли або спеціальні символи, записуються в лапках. Це означає, що імена взяті в набір подвійних лапок і перед ними стоїть знак решетки або фунта, що ускладнює читання коду M. Ви можете пропускати пробіли або розміщувати між ними підкреслення.
Додавання додаткових деталей документації шляхом розміщення коментарів у вікні розширеного редактора також є найкращою практикою в Power Query. Ви також можете зробити це в Описі властивості кроку . Вони відображаються як анотації підказок, коли ви наводите курсор на крок зі знаком оклику на панелі «Застосовані кроки».
Знати, чому ви зробили певний вибір під час початкової розробки, надзвичайно корисно, коли через деякий час вам доведеться переглянути файл. Щоб додати деталі документації, клацніть правою кнопкою миші крок на панелі «Застосовані кроки» та виберіть «Властивості» .
З’явиться діалогове вікно «Властивості кроку» , у якому ви можете вказати причину фільтрації чи перетворення.
Упорядковуйте запити для кращого потоку мови Power Query
Однією з найпоширеніших найкращих практик у Power Query є впорядкування ваших запитів . Створіть папки для параметрів, функцій, проміжних запитів і запитів, які будуть завантажені в модель даних. У цьому прикладі виберіть запити FileLocation і DatesFrom і клацніть їх правою кнопкою миші. Потім виберіть «Перемістити до групи» та натисніть «Нова група» .
Далі додайте назву для вибраних запитів і натисніть OK .
Після групування ваших запитів панель запитів виглядатиме так.
Для всіх запитів проміжної обробки обов’язково вимкніть завантаження, знявши прапорець «Увімкнути завантаження».
Інша річ, яку варто обговорити в цьому підручнику, це мовний потік. Кожен із кроків на панелі «Застосовані кроки» перетворює значення, яке можна побачити, клацнувши їх.
Початкові дані надійшли та почалися на кроці навігації, а стовпці були вибрані. Потім було змінено типи даних і встановлено діапазон дат. Колонки також були перейменовані.
Усі кроки повертають значення типу таблиці. Якщо ви відкриєте вікно розширеного редактора, ви побачите вираз let і пропозицію in . Між ними є список кроків або імен змінних із присвоєними їм виразами.
Запит повертає все, що слідує за реченням in , яке посилається на останній крок у вашому списку змінних. Потім механізм M буде слідувати ланцюжку залежностей назад від пропозиції in , щоб усунути все непотрібне та, якщо це можливо, повернути перетворення до джерела.
Підсумок потоку мови Power Query
Якщо ви подивіться на формулу, ви побачите послідовність кроків, виконаних у запиті. Ви також побачите функції, які використовує на кожному кроці.
Першим кроком, який було зроблено в запиті, був вибір стовпця. Коли крок було виконано за допомогою інтерфейсу користувача, була викликана функція Table.SelectColumns . Першим параметром була таблиця, яка посилалася на назву змінної попереднього кроку. Потім він перерахував усі вибрані назви стовпців.
На другому кроці було перетворено типи стовпців, викликавши функцію Table.TransformColumnTypes . Його перший параметр називається результатом попереднього кроку. Потім він перерахував набір списків трансформацій.
На третьому кроці встановіть фільтр діапазону дат за допомогою функції Table.SelectRows . Його першим аргументом був запит табличного типу. У цьому прикладі він посилався на вихід кроку Change Type.
Останній крок перейменував стовпці за допомогою функції Table.RenameColumns . Результат попереднього кроку використовувався як його перший аргумент. Потім він перерахував набір списків перейменування.
Усі функції, які застосовуються через інтерфейс користувача, починаються зі слова Таблиця . Усі вони взяли табличне значення як перший параметр, а потім перетворили це значення.
Хоча код виглядає послідовним, оскільки кожен крок посилається на попередній крок, послідовність не потрібна. Якщо ви перейдете на крок назад, запит все одно виконуватиметься, оскільки механізм M завжди слідує ланцюжку залежностей.
Наявність інтерфейсу користувача зручно, але він завжди передбачає, що ви хочете перетворити результати попереднього перетворення. У більшості випадків це, ймовірно, буде правдою, а якщо це не так, вам доведеться вручну оновити це посилання в коді.
Висновок
Трансформація даних необхідна для того, щоб ваші дані були згруповані та впорядковані. Це пришвидшує розробку даних, оскільки ви можете легко відстежувати проблеми в мовному потоці Power Query і змінювати зміни у своєму звіті.
Меліса
LuckyTemplates — чудовий інструмент для фінансової звітності. Ось підручник про те, як створювати персоналізовані таблиці для вашої фінансової інформаційної панелі LuckyTemplates.
У цьому підручнику обговорюватиметься технологія Power Query Language Flow і те, як вона може допомогти створити гладкий і ефективний звіт із даними.
Я обговорю одну з моїх улюблених технік, пов’язаних із користувальницькими піктограмами LuckyTemplates, яка динамічно використовує власні піктограми у візуальних елементах LuckyTemplates.
У цьому блозі я покажу вам, як можна створювати таблиці LuckyTemplates за допомогою формули, яка поєднує функції UNION і ROW.
Дізнайтеся, як локальний шлюз даних дозволяє Power Automate отримувати доступ до настільних програм, коли користувач знаходиться поза комп’ютером.
У цьому блозі представлено функцію LuckyTemplates TOPN DAX, яка дає вам змогу отримувати унікальну статистику з ваших даних, допомагаючи вам приймати кращі маркетингові рішення.
Дізнайтеся про деякі фантастичні аналітичні прийоми, які ми можемо використовувати для моделювання даних у LuckyTemplates за допомогою таблиць підтримки DAX.
Тут ми зануримося в LuckyTemplates Advanced DAX і запровадимо логіку ранжирування, щоб отримати дуже унікальну інформацію. У цьому прикладі я також демонструю розгалуження міри.
У цьому блозі представлено нову функцію в LuckyTemplates — параметр аналізу «Що, якщо». Ви побачите, як це робить усе швидким і легким для аналізу сценарію.
Дізнайтеся, як визначити, чи добре зростає ваш дохід, перевіривши, чи зросла ваша маржа за допомогою розгалуження міри LuckyTemplates.
