Код M у спеціальних стовпцях Power Query | LuckyTemplates

У сьогоднішньому блозі я збираюся представити рішення для LuckyTemplates, включаючи просту та узагальнювану структуру для включення коду M у спеціальний стовпець Power Query. Ви можете переглянути повне відео цього підручника внизу цього блогу.

Можливість створювати стовпці в Power Query за допомогою коду M збільшує вашу здатність вирішувати складніші проблеми.

Перш ніж перейти до вирішення, давайте розглянемо суть проблеми. Я надав великий набір даних від Kaggle за 93 роки даних Оскара. Ми хотіли виділити найкращу актрису, переможців і особливо тих жінок, які отримали більше ніж одну нагороду за найкращу жіночу роль, а потім візуалізувати за допомогою шкали часу Queryon рік, у якому вони отримали свою другу нагороду.

Поєднуючи цю проблему, у мене було кілька цілей. На чому ми хочемо зосередити проблему і які основні цілі?

А в цьому їх було троє.

Першим було навчитися або розвивати певну практику роботи з конструкцією Group By і All Rows . Це те, на чому я справді зосереджувався, оскільки воно досить просто вирішує широкий спектр проблем і часто усуває потребу в дуже складному DAX.

Друга ціль особливо призначена для людей, які не звикли працювати на M. Я хотів створити проблему, яка б трохи полегшила написання базового коду M. Я хотів змусити цей спосіб думати про те, як це розробляти базовий код M і процес його виконання.

Згідно з розділом у книзі Гіла Равіва про Power Query, близько 40% проблем у Power Query можна вирішити лише через інтерфейс користувача. Але це, з додаванням базового коду M і можливістю створювати спеціальні стовпці, насправді не так вже й складно. Ви можете збільшити цей відсоток понад 80%. Ви можете подвоїти свої здібності у вирішенні проблем за допомогою деяких основних простих навичок.

Третє, що я хочу зробити, це представити спеціальний візуал Queryon Timeline тим, хто з ним не знайомий. Це той, який я дійсно полюбив. Я використовував його в багатьох різних випадках, і я думаю, що це просто надає справді унікальний спосіб візуалізації даних.

Зміст

• Підхід до вирішення проблеми тижня №9
• Використання групи за в Power Query
• Включення Table.AddIndexColumn M Код
• Візуалізація за допомогою спеціального візуального елемента Queryon Timeline
• Висновок

Підхід до вирішення проблеми тижня №9

Розмірковуючи над цією проблемою, я хотів провести уявний експеримент. Що, якби замість файлу CSV я просто дав вам 93 аркуші паперу (по одному з переможницею за найкращу жіночу роль кожного року), як би виглядало рішення?

Можливо, ваше рішення виглядатиме приблизно так, як я маю нижче.

Я думаю, що кожен мав би відносно подібне рішення для цього. Коли ви отримаєте це концептуальне рішення, залишиться лише з’ясувати, як змусити Power Query виконувати кожен із цих кроків.

Отже, я просто візьму це як загальний план, а потім розповім, як вони втілюють це концептуальне рішення в щось у Power Query. Підхід, який ми використовуватимемо, Мелісса, наш постійний гуру Power Query, називає навчанням у Google.

Тепер, якщо ми повернемося до нашої концептуальної моделі, то перші два кроки включатимуть сортування листків у групи за актрисами та викидання будь-якої групи з лише одним листком. Отже, ми хочемо зробити підрахунок. Якщо ми не знаємо, як це зробити, ми можемо зайти в Google і спробувати знайти термін. У цьому прикладі я спробував «групувати в потужному запиті». Завдяки цьому ми можемо знайти все, що нам потрібно знати про те, як зробити це групування.

Використання групи за в Power Query

Тоді ми можемо перейти до Power Query і розпочати роботу нашого рішення. Що я зробив, так це те, що в цій таблиці Оскара я щойно переглянув і забрав файл CSV і відфільтрував переможців за найкращу жіночу роль кожного року. Отже, ми маємо лише 93 рядки лише з назви та року фільму.

Якщо ми зайдемо в Power Query і перейдемо до цієї таблиці, перше, що ми хочемо зробити, це Group By .

Мені завжди подобалося розширене лише тому, що воно забезпечує трохи кращий інтерфейс, навіть якщо те, що ми робимо, є досить простим.

Ми хочемо згрупувати за назвою , тоді давайте створимо стовпець AllData , і це будуть наші всі рядки. Замість того, щоб повертати лише агрегат, він повертатиме агрегацію, але всі рядки, які ми хочемо, залишатимуться недоторканими.

У нас буде ще один стовпець під назвою «Кількість» , і він нам потрібен, коли ми будемо рахувати рядки . Ми хочемо знайти кількість листків у кожній купці. Потім натискаємо OK.

Потім ми можемо натиснути на цей пробіл у таблиці, і ми отримаємо лише один листок.

Але тоді для тих, які мають кілька підрахунків, ви побачите, що ми отримуємо всі записи.

Далі ми хочемо відфільтрувати ті (1). Отже, ми фактично викидаємо на сміття всі ці купи, які мають лише одну назву. Виберіть 1 і натисніть OK.

І тепер у нас 14 лауреатів кількох нагород. І тепер у нас 14 лауреатів кількох нагород. Якщо ми візьмемо та розширимо це, ми зможемо вилучити Name , оскільки ми вже маємо Name тут і просто залишимо year_film .

Зараз ми скоротилися до 30 рядків, тому що деякі з них матимуть кілька виграшів. І ми можемо навіть позбутися стовпця «Кількість» , тому що ми вже викинули одиниці лише з одним.

Тепер ми переходимо до наступного етапу нашої концептуальної моделі, яка полягає в сортуванні листів у кожній групі за датою зростання, а потім у кожній групі вибираємо другий наступний день.

Сортувати легко. Ми можемо повернутися до Power Query і зробити aза актрисою, а потім за датою.

І він покаже вам один і два.

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

Включення Table.AddIndexColumn M Код

Отже, тепер ми повертаємося до нашого підходу «Навчилися в Google», коли ми можемо просто шукати за рейтингом або індексом, але ми також можемо перейти до Довідкового посібника Microsoft . Я створив зовнішній інструмент, яким постійно користуюся, який просто перекидає вас із LuckyTemplates у довідковий посібник Microsoft.

У довідковому посібнику Microsoft все згруповано за функціями та є поле фільтра, де ви можете шукати все, що вам потрібно. У цьому випадку ми вводимо ранг або індекс у полі фільтра. Це не дає нам нічого для Rank, але для Index ми маємо цей Table.AddIndexColumn , який є релевантним і застосовним до того, що ми шукаємо. Тут також наведено кілька прикладів використання цієї функції.

Отже, давайте перейдемо до Power Query і перегрупуємо нашу таблицю за актрисою.

Таким чином, ми отримуємо 14 груп переможців за таблицею. І якщо ми зайдемо в будь-яку з цих таблиць, наприклад, Френсіс Макдорманд, ми побачимо, що таблиця впорядкована за датою.

І ось великий крок, а саме додавання aспеціальний стовпець і введіть наш код M. Ми матимемо Table.AddIndexColumn , а потім додамо поле AllData . Ми назвемо наш новий стовпець (як текст) як Index , і ми почнемо наш індекс з одиниці (1) і збільшимо його на одиницю (1).

І ми отримуємо ідеальний індекс тут.

Тепер ми можемо видалити наш стовпець AllData тут, оскільки все зібрано в. Далі ми можемо просто розгорнути стовпець «Спеціальний», вилучити «Назва», тому що тут у нас також є «Назва». Таким чином, ми маємо нумерований індекс для кожного.

Нарешті, ми можемо зробити те, що ми робили з підрахунком, тобто відфільтрувати всіх, хто виграв другу команду.

І ось ми йдемо. Ось наше рішення.

Візуалізація за допомогою спеціального візуального елемента Queryon Timeline

Тут є ще кілька речей, які ми хочемо зробити, і всі вони базуються на реалізації Queryon Visual . Ми зробимо об’єднання та просто додамо URL-адресу зображення.

Тож ми об’єднаємо запити, витягнемо таблицю Actresses і об’єднаємо Name . Ми отримуємо 14-14 рядків, що збігаються, що добре.

А потім у стовпці «Актриси» ми просто розгорнемо його й розгорнемо до URL-адреси.

І тепер ми маємо цей стовпець із URL-адресами.

Є ще одна річ, яка приходить із знанням шкали часу Queryon. Queryon працює найкраще, коли має справу з повними датами, а не роками . Отже, це те, що приходить лише з експериментами, але ми можемо взяти стовпець Рік і створити стовпець із прикладів . Що ми хочемо зробити тут, це просто перетворити роки на дати.

Це покращить форматування. Це те, чого я не очікую, що ви дізнаєтеся відразу, але це приходить із попрактикацією в Queryon. Ми клацаємо «ОК», і це з’являється як значення дати. Ми можемо видалити таблицю індексів, оскільки вона нам більше не потрібна, а потім змінити назву стовпця year_film на Year .

Давайте тепер перейдемо до аспекту візуалізації, і я хочу висвітлити запис одного з учасників LuckyTemplates, Хосе, який справді зробив чудову роботу з візуалізації.

Повертаючись до Power Query для візуалізації, варто згадати, що Microsoft нещодавно внесла деякі зміни в спосіб сертифікації користувацьких візуальних елементів . У рамках цієї сертифікації вони посилили використання зовнішніх зображень URL-адрес.

Якщо ви справді використовуєте версію Queryon Timeline, яка є в програмному коді (версія 1.05), ви не зможете отримати ці URL-зображення. Отже, тепер у вас є можливість використовувати цю версію 1.04, зберігаючи URL-зображення, де на їхньому сайті є спосіб вставляти двійкові 64 зображення безпосередньо у файл PBIX.

Тепер я згадав про зміну цього року в поле дати, тому нам потрібно підключити це до нашої моделі даних.

Давайте просто змінимо його з «один до одного» на « один до багатьох » і одиничний напрямок у перехресному фільтрі, а потім зробимо це.

Далі для поля URL-адреси зображення потрібно переконатися, що в категорії «Дані» воно відображається не як Без категорії, а як.

Тепер, коли ми все добре підготували, ми просто перетягуємо поля Name , Date та URL . Решта стає дійсно простим очищенням у параметрах формату.

Висновок

Сподіваюся, я зацікавив вас додаванням коду М до свого репертуару. Я показав вам, як створити концептуальну модель і реалізувати підхід «Навчання за допомогою Google». Я провів вас через кроки з додавання коду M до спеціального стовпця та візуалізації результатів за допомогою Queryon Timeline.

Сподіваюся, ви знайшли це корисним. Перегляньте повне відео нижче, щоб дізнатися більше про підручник, і перегляньте посилання нижче, щоб отримати більше відповідного вмісту.

Все найкраще!