LuckyTemplates Power Query: удаление повторяющихся строк

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

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

Существует два различных типа транзакций: один для входящих и один для исходящих. Она также добавила счетчик движений. И исходя из этого, она сформулировала три сценария.

Первый сценарий основан на двух строках. Если они компенсируют друг друга, обе строки должны быть удалены. Если это не так, то следует сохранить обе строки.

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

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

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

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

Оглавление

• Добавление столбца с абсолютными количествами в Power Query
• Группировать по индексу ID и абсолютному количеству
• Преобразование вложенных таблиц в LuckyTemplates Power Query
  • Сортировка по типу движения и дате публикации
  • Создание промежуточного итога
  • Фильтрация строк по условию
• Заключение

Добавление столбца с абсолютными количествами в Power Query

Мы начнем с добавления столбца с абсолютными количествами. Выберите столбец «Количество» , затем на ленте или вкладке «Добавить столбец» перейдите к « Научные » и нажмите « Абсолютное значение» .

Я собираюсь переименовать этот столбец в строке формул.

Группировать по индексу ID и абсолютному количеству

Теперь мы можем группировать по ID-индексу и абсолютному количеству. Выберите ID Index , затем, удерживая нажатой клавишу Shift или Control, выберите множественный выбор, а затем нажмите Abs qty (абсолютное количество).

Вы найдете Group by на вкладке Transform.

Но вы также можете найти его на вкладке «Главная».

Нажмите на нее, и теперь мы собираемся добавить новый столбец. Это не «количество», а «сумма» количества. Итак, давайте переименуем его в Qty (количество) Balance . Это будет сумма столбца количества. Затем мы добавим еще одну агрегацию, но она будет специальной, поэтому мы выберем All Rows . Давайте также дадим этому столбцу имя ( AllRows ). Затем нажмите ОК.

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

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

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

Давайте переименуем этот столбец в Преобразования и добавим ноль (0) в качестве заполнителя. Затем нажмите ОК.

Преобразование вложенных таблиц в LuckyTemplates Power Query

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

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

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

Щелкните правой кнопкой мыши сбоку в пустом месте и выберите «Добавить как новый запрос» .

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

Сортировка по типу движения и дате публикации

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

Далее мы отсортируем дату публикации по возрастанию , убедившись, что если мы опускаем строки, это всегда будет выполняться в соответствии с принципом FIFO (первым пришел, первым ушел).

Создание промежуточного итога

Чтобы определить строки, которые мы собираемся удалить, я добавлю промежуточную сумму для отключения, и мы можем использовать для этой задачи List.FirstN . Эта функция создает список на основе другого списка, в котором основные элементы сохраняются на основе определенного числа или критерия.

Вернемся к нашему запросу. Нам нужны две вещи для List.FirstN. Во-первых, нам нужен этот список, и этот список является нашим столбцом «Количество» . Если я щелкну правой кнопкой мыши заголовок столбца Quantity, мы получим возможность Добавить как NewQuery .

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

Вторая часть, которая нам нужна для List.FirstN, — это число, указывающее, сколько номеров нужно сохранить из этого списка. Для этого мы можем использовать индекс, поэтому я собираюсь добавить столбец индекса из одного (1) для этой строки.

Таким образом, для номера 1 он сохранит номер в первой строке столбца «Количество» и так далее. И теперь мы можем подвести итог.

Давайте добавим пользовательский столбец и назовем его Текущая сумма . Здесь мы используем List.FirstN, и мы видим, что первым параметром был этот список, столбец Quantity. Нам нужно указать на последний шаг, и этот последний шаг — Added Index .

Мы определили столбец, который нам нужен, и это наш столбец Quantity. Наш счет находится в столбце Index.

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

Теперь все, что нам нужно сделать, это суммировать эти количества, и мы можем использовать Lists.Sum для этого. Итак, я собираюсь добавить это в строку формул. И я также собираюсь поставить Type .

Фильтрация строк по условию

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

Теперь мы можем удалить наши вспомогательные столбцы. Выберите «Индекс и промежуточный итог», затем нажмите «Удалить столбцы».

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

Теперь вернемся к нашему исходному запросу. Снова откройте расширенный редактор. И вместо заполнителя перейдем на новую строку. Я собираюсь использовать выражение « let », потому что оно может захватывать значения из промежуточных вычислений в переменных. Итак, «пусть», а затем перейдите на новую строку и вставьте мои преобразования. Также я объявлю Type .

Выделенный выше код указывает на нашу расширенную вложенную таблицу, которая размещена в столбце All Rows. Итак, я собираюсь указать на это, заменив этот выделенный код на AllRows .

Итак, наш столбец преобразования теперь имеет вложенные таблицы. Мы строим наш запрос на основе последней строки, верно? И это содержало четыре строки, когда мы начали, и содержало только две. У нас также есть эти количества, уравновешенные нулем, верно? И теперь у него есть пустой стол.

Мы можем удалить их, отфильтровав столбец Quantity Balance. Мы не хотим, чтобы баланс количества был равен нулю.

Затем я выберу свой столбец преобразования и удалю другие столбцы. Теперь я могу расширить свои вложенные таблицы преобразования. Не используйте исходное имя столбца в качестве префикса и нажмите «ОК».

На вкладке «Преобразование» выберите «Тип данных» , и все готово. Вот как вы очищаете транзакционные данные.

Основы Unpivot и Pivot в LuckyTemplates — Редактор запросов Ознакомьтесь с
моими практическими советами при использовании расширенного редактора LuckyTemplates
Расширенные преобразования в LuckyTemplates

Заключение

В этом руководстве я показал вам, как очищать транзакционные данные, в частности, удаляя повторяющиеся строки. Это отличная техника, которую вы можете использовать при использовании LuckyTemplates Power Query.

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

Ваше здоровье!

Мелисса