M-код в пользовательских столбцах Power Query | LuckyTemplates

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

Возможность создавать столбцы в Power Query с помощью кода M расширяет ваши возможности для решения более сложных задач.

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

Я преследовал несколько целей, когда собирал эту проблему. На чем мы хотим сфокусировать проблему и какими должны быть основные цели?

А в этой их было трое.

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

Вторая цель особенно актуальна для людей, которые не привыкли работать на 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 и перейдем к этой таблице, первое, что мы хотим сделать, это Группировать по .

Мне всегда нравилось переходить на Advanced просто потому, что он обеспечивает немного лучший интерфейс, даже если то, что мы делаем, довольно просто.

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

У нас будет еще один столбец с именем Count , и он нам нужен, когда мы собираемся Count Rows . Нам нужно найти количество карточек в каждой стопке. Затем нажимаем ОК.

Затем мы можем нажать на это пустое место в таблице, и мы получим только один промах.

Но тогда для тех, которые имеют несколько подсчетов, вы увидите, что мы получаем все записи.

Далее мы хотим отфильтровать те (1). Итак, мы, по сути, выбрасываем все те кучи, у которых есть только одно имя. Выберите 1 и нажмите ОК.

И теперь мы дошли до 14 многократных обладателей наград. И теперь мы дошли до 14 многократных обладателей наград. Если мы возьмем и расширим это, мы можем убрать Name , потому что у нас уже есть Name, и просто оставить year_film .

Теперь у нас осталось 30 рядов, потому что некоторые из них будут иметь несколько выигрышей. И мы можем даже избавиться от столбца Count , потому что мы уже выбросили единицы только с одним.

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

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

И он покажет вам один и два.

Итак, мы хорошо отсортированы, и теперь мы хотим иметь индекс или ранжирование для каждого, а затем иметь возможность брать второй из каждой стопки.

Включение кода Table.AddIndexColumn M

Итак, теперь мы вернулись к нашему подходу «Узнал с помощью Google», где мы можем просто выполнить поиск по рейтингу или индексу, но мы также можем обратиться к Справочному руководству Microsoft . Я создал внешнее средство, которым постоянно пользуюсь, которое просто перенаправляет вас из LuckyTemplates в Справочное руководство Microsoft.

В Справочном руководстве Microsoft все сгруппировано по функциям, и в нем есть поле фильтра, где вы можете искать все, что вам нужно. В этом случае мы вводим Rank или Index в поле фильтра. Это ничего не дает нам для ранга, но для индекса у нас есть эта таблица Table.AddIndexColumn , которая актуальна и применима к тому, что мы ищем. Также приведены некоторые примеры использования этой функции.

Итак, давайте перейдем к Power Query и перегруппируем нашу таблицу по Актрисе.

При этом мы получаем группу из 14 победителей по таблице. И если мы зайдем в любую из этих таблиц, скажем, Фрэнсис МакДорманд, мы увидим, что таблица отсортирована по дате.

И затем, вот большой шаг, который заключается в добавлении пользовательского столбца Power Query и вводе нашего M-кода. У нас будет Table.AddIndexColumn , затем добавьте поле AllData . Мы назовем наш новый столбец (как текст) здесь как Index , и мы начнем наш индекс с единицы (1) и увеличим его на единицу (1).

И мы получаем этот идеальный индекс здесь.

Теперь мы можем удалить здесь наш столбец AllData, так как все собрано в Custom . Затем мы можем просто развернуть столбец Custom и убрать Name, потому что здесь у нас уже есть Name. При этом у нас есть пронумерованный индекс для каждого из них.

Наконец, мы можем сделать то же, что и в Count, а именно отфильтровать все те, которые находятся во втором выигрыше.

И вот мы идем. Вот наше решение.

Визуализация с использованием пользовательского визуального элемента Queryon Timeline

Здесь мы хотим сделать еще несколько вещей, и все они основаны на реализации Queryon Visual . Мы собираемся выполнить слияние и просто добавим URL-адрес изображения.

Итак, мы объединим запросы, подтянем эту таблицу «Актрисы» и объединим имя . Мы получаем совпадение от 14 до 14 строк, и это хорошо.

А затем в столбце «Актрисы» мы просто расширимся и расширимся до URL-адреса.

И теперь у нас есть этот столбец с URL-адресами.

Есть еще одна вещь, которая приходит со знанием временной шкалы Queryon. Лучше всего Queryon работает с полными датами, а не с годами . Итак, это то, что приходит только с экспериментами, но мы можем взять столбец Year и создать столбец From Examples . Здесь мы хотим просто превратить годы в даты.

Это улучшит форматирование. Это то, что я не ожидал, что вы сразу поймете, но это приходит с практикой с Queryon. Мы нажимаем OK, и это становится значением даты. Мы можем удалить индексную таблицу, потому что она нам больше не нужна, а затем мы можем изменить имя столбца year_film на Year .

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

Возвращаясь к Power Query для визуализации, стоит упомянуть, что Microsoft недавно внесла некоторые изменения в способ сертификации пользовательских визуализаций . В рамках этой сертификации они ужесточили использование внешних URL-изображений.

Если вы на самом деле используете версию Queryon Timeline, которая находится в источнике приложения (версия 1.05), вы не сможете получить эти URL-изображения. Итак, теперь у вас есть возможность использовать эту версию 1.04, сохраняя URL-изображения, где на их сайте есть способ встроить двоичные 64-изображения непосредственно в файл PBIX.

Теперь я упомянул изменение этого года в поле даты, поэтому нам нужно связать это с нашей моделью данных.

Давайте просто изменим его с one to one на one to many и Single direction на Cross filter, а затем сделаем это отношение активным .

Далее, для поля URL-адреса изображения нам нужно убедиться, что в категории «Данные» оно отображается не как «Без категории», а как « URL-адрес изображения» .

Теперь, когда у нас все хорошо подготовлено, мы просто перетаскиваем поля Name , Date и URL . Остальное становится очень простой очисткой в ​​параметрах формата.

Таблица Power Query: как динамически объединять столбцы
Как объединять запросы в LuckyTemplates
Извлекать значения из записей и списков в Power Query

Заключение

Надеюсь, я заинтриговал вас добавлением M-кода в свой репертуар. Я показал вам, как создать концептуальную модель и реализовать подход «Учись с помощью Google». Я провел вас через этапы включения кода M в настраиваемый столбец и визуализации результатов с помощью Queryon Timeline.

Надеюсь, вы нашли это полезным. Посмотрите полное видео ниже, чтобы получить более подробную информацию об учебном пособии, и ознакомьтесь с приведенными ниже ссылками для получения дополнительной информации.

Всего наилучшего!