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

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

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

Існує два різних типи транзакцій: один для вхідних і один для вихідних. Вона також додала кількість рухів. І на основі цього сформулювала три сценарії.

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

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

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

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

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

Зміст

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

Додавання стовпця з абсолютними величинами в Power Query

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

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

Групуйте за ідентифікаційним індексом і абсолютною кількістю

Тепер ми можемо групувати за ідентифікаційним індексом і абсолютною кількістю. Виберіть ID Index , потім утримуйте shift або control для вибору кількох елементів, а потім клацніть Abs qty (абсолютна кількість).

Ви знайдете «Групувати за» на вкладці «Перетворення».

Але ви також можете знайти його на вкладці «Головна».

Натисніть на нього, і тепер ми збираємося додати новий стовпець. Це не «підрахунок», але це буде «сума» кількості. Отже, давайте перейменуємо його на Qty (quantity) Balance . Це буде сума стовпця кількості. А потім ми додамо інше об’єднання, але воно буде особливим, тому ми виберемо « Усі рядки» . Давайте також назвемо цей стовпець ( AllRows ). Потім натисніть OK.

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

Якщо ми подивимося на наступну вкладену таблицю (тому клацніть збоку в білому просторі наступного рядка), ми побачимо, що стовпець кількості не скасовує один одного, а баланс кількості більше нуля.

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

Давайте перейменуємо цей стовпець наі ми додамо нуль (0) як заповнювач. Потім натисніть OK.

Перетворення вкладених таблиць у LuckyTemplates Power Query

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

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

Тепер, якщо я клацну праворуч в останньому рядку, я побачу, що ця таблиця має чотири рядки. Отже, ми можемо використовувати це для побудови наших перетворень.

Клацніть правою кнопкою миші збоку в білому просторі та виберіть Додати як новий запит .

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

Сортування типу руху та дати проводки

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

Далі ми відсортуємо дату публікації за зростанням , переконавшись, що якщо ми пропускаємо рядки, це завжди виконуватиметься за принципом FIFO (першим прийшов, першим вийшов).

Створення поточного підсумку

Щоб визначити рядки, які ми збираємося видалити, я збираюся додати загальну суму для вимкнення, і ми можемо використовувати List.FirstN для цього завдання. Ця функція створює список на основі іншого списку, де верхні елементи зберігаються на основі певного числа або критерію.

Повернемося до нашого запиту. Нам потрібні дві речі для List.FirstN. По-перше, нам потрібен цей список, і цей список є нашим стовпцем кількості . Якщо я клацну правою кнопкою миші заголовок стовпця Quantity, ми отримаємо опцію Add as NewQuery .

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

Друга частина, яка нам потрібна для List.FirstN, — це число, яке визначає, скільки чисел залишити зі списку. Ми можемо використати для цього індекс, тому я збираюся додати стовпець індексу з одного (1) для цього рядка.

Отже, для числа 1 буде збережено число в першому рядку в стовпці «Кількість» і так далі. І тепер ми можемо підсумувати це.

Давайте додамо спеціальний стовпець і назвемо його Підсумок . Тут ми використовуємо List.FirstN і бачимо, що першим параметром був цей список, стовпець Quantity. Нам потрібно вказати на останній крок, і цим останнім кроком є ​​Added Index .

Ми визначили потрібний стовпець, і це наш стовпець "Кількість". Наш підрахунок у колонці Індекс.

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

Тепер все, що нам потрібно зробити, це підсумувати ці кількості, і ми можемо використовувати для цього Lists.Sum . Отже, я додам це в рядок формул. І я також збираюся поставити Type .

Фільтрування рядків на основі умови

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

Тепер ми можемо видалити наші допоміжні стовпці. Виберіть «Індекс» і «Загальна сума», а потім натисніть «Видалити стовпці».

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

Тепер давайте повернемося до нашого початкового запиту. Знову відкрийте розширений редактор. А замість заповнювача перейдемо на новий рядок. Я збираюся використовувати вираз « let », оскільки він може фіксувати значення з проміжних обчислень у змінних. Отже, «дозвольте», а потім перейдіть на новий рядок і вставте мої перетворення. Також я оголошу Type .

Виділений вище код вказує на нашу розгорнуту вкладену таблицю, яка розміщена в стовпці «Усі рядки». Отже, я збираюся вказати на це, замінивши цей виділений код на AllRows .

Отже, наш стовпець трансформації тепер має вкладені таблиці. Ми будуємо наш запит на основі останнього рядка, чи не так? І це містило чотири рядки, коли ми почали, і містило лише два. У нас також ця кількість збалансована нулем, чи не так? А тепер у нього порожній стіл.

Ми можемо видалити їх, відфільтрувавши стовпець «Кількісний баланс». Ми не хочемо, щоб кількісний баланс дорівнював нулю.

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

На вкладці «Перетворення» виберіть «Тип даних» , і все готово. І саме так ви очищаєте дані транзакцій.

Висновок

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

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

здоров'я!

Меліса