Примерна колона – Изкуствен интелект в Power Query

Един от най-гледаните видеоклипове в канала ми в YouTube е видеоклип за Flash Fill в Microsoft Excel. Същността на този инструмент е, че ако трябва по някакъв начин да трансформирате вашите изходни данни, тогава просто трябва да започнете да въвеждате резултата, който искате да получите в съседната колона. След няколко ръчно въведени клетки (обикновено 2-3 са достатъчни), Excel ще "разбере" логиката на трансформациите, от които се нуждаете и автоматично ще продължи това, което сте въвели, завършвайки цялата монотонна работа вместо вас:

Квинтесенцията на ефективността. Магическият бутон „направи го правилно“, който всички обичаме толкова много, нали?

Всъщност има аналог на такъв инструмент в Power Query - там се нарича Колона от примери (Колона от Примери). Всъщност това е малък изкуствен интелект, вграден в Power Query, който може бързо да се учи от вашите данни и след това да ги трансформира. Нека разгледаме по-подробно неговите възможности в няколко практически сценария, за да разберем къде може да ни бъде полезен при реални задачи.

Пример 1. Лепене/изрязване на текст

Да кажем, че имаме такава „умна“ таблица в Excel с данни за служителите:

Заредете го в Power Query по стандартния начин – с бутона От таблица/обхват етикет Дата (Данни — от таблица/обхват).

Да предположим, че трябва да добавим колона с фамилни имена и инициали за всеки служител (Иванов SV за първия служител и т.н.). За да разрешите този проблем, можете да използвате един от двата метода:

• щракнете с десния бутон върху заглавието на колоната с изходните данни и изберете командата Добавете колона от примери (Добавете колона от примери);

• изберете една или повече колони с данни и в раздела Добавяне на колона изберете екип Колона от примери. Тук, в падащия списък, можете да посочите дали всички или само избрани колони трябва да бъдат анализирани.

Тогава всичко е просто - в колоната, която се появява вдясно, започваме да въвеждаме примери за желаните резултати, а изкуственият интелект, вграден в Power Query, се опитва да разбере логиката ни на трансформация и да продължи сам:

Между другото, можете да въведете правилните опции във всяка клетка на тази колона, т.е. не непременно отгоре надолу и в ред. Освен това можете лесно да добавяте или премахвате колони от анализа по-късно, като използвате квадратчетата за отметка в заглавната лента.

Обърнете внимание на формулата в горната част на прозореца – това е, което Smart Power Query създава, за да получим резултатите, от които се нуждаем. Това, между другото, е основната разлика между този инструмент и Незабавно попълване в Excel. Моменталното попълване работи като „черна кутия” – те не ни показват логиката на трансформациите, а просто дават готови резултати и ние ги приемаме за даденост. Тук всичко е прозрачно и винаги можете абсолютно ясно да разберете какво точно се случва с данните.

Ако видите, че Power Query е „схванал идеята“, тогава можете спокойно да натиснете бутона OK или клавишна комбинация Ctrl+Въведете – ще бъде създадена персонализирана колона с формула, измислена от Power Query. Между другото, по-късно може лесно да се редактира като обикновена ръчно създадена колона (с командата Добавяне на колона – Персонализирана колона), като щракнете върху иконата на зъбно колело вдясно от името на стъпката:

Пример 2: Падеж като в изречения

Ако щракнете с десния бутон върху заглавието на колоната с текст и изберете командата Трансформация (Трансформиране), тогава можете да видите три команди, отговорни за промяната на регистъра:

Удобно и готино, но в този списък, например, на мен лично винаги ми е липсвал още един вариант - случай като в изреченията, когато главната буква (главна) става не първата буква във всяка дума, а само първата буква в клетката, и останалата част от текста, когато Това се показва с малки (малки) букви.

Тази липсваща функция е лесна за изпълнение с изкуствен интелект Колони от примери – просто въведете няколко опции за Power Query, за да продължите в същия дух:

Като формула тук Power Query използва куп функции Текст.Горна и Текст.Долно, конвертиране на текст съответно в главни и малки букви и функции Текст.Старт и Текст.Ср – аналози на функциите на Excel LEFT и PSTR, способни да извличат подниз от текста отляво и от средата.

Пример 3. Пермутация на думи

Понякога при обработката на получените данни се налага пренареждане на думите в клетките в дадена последователност. Разбира се, можете да разделите колоната на отделни колони с думи чрез разделителя и след това да я залепите обратно в посочения ред (не забравяйте да добавите интервали), но с помощта на инструмента Колона от примери всичко ще бъде много по-лесно:

Пример 4: Само числа

Друга много важна задача е да извадите само числа (числа) от съдържанието на клетката. Както преди, след като заредите данни в Power Query, отидете на раздела Добавяне на колона – Колона от примери и попълнете няколко клетки ръчно, така че програмата да разбере какво точно искаме да получим:

Бинго!

Отново си струва да погледнете горната част на прозореца, за да се уверите, че Query е генерирал правилно формулата – в този случай тя съдържа функция Текст. Изберете, който, както можете да се досетите, извлича дадените символи от изходния текст според списъка. Впоследствие този списък, разбира се, може лесно да се редактира в лентата с формули, ако е необходимо.

Пример 5: Само текст

Подобно на предишния пример можете да изтеглите и обратно – само текста, като изтриете всички цифри, препинателни знаци и т.н.

В този случай се използва вече противоположна по смисъл функция – Text.Remove, която премахва символи от оригиналния низ по зададен списък.

Пример 6: Извличане на данни от буквено-цифрова каша

Power Query може да помогне и в по-трудни случаи, когато трябва да извлечете полезна информация от буквено-цифровата каша в клетка, например да вземете номера на сметката от описанието на целта на плащането в банково извлечение:

Имайте предвид, че генерираната от Power Query формула за преобразуване може да бъде доста сложна:

За по-лесно четене и разбиране, той може да бъде преобразуван в много по-разумна форма с помощта на безплатна онлайн услуга. Форматиране на Power Query:

Много удобно нещо - уважение към създателите!

Пример 7: Преобразуване на дати

Инструмент Колона от примери може да се приложи и към колони за дата или дата и час. Когато въведете първите цифри на дата, Power Query полезно ще покаже списък с всички възможни опции за преобразуване:

Така че можете лесно да конвертирате оригиналната дата във всеки екзотичен формат, като например „година-месец-ден“:

Пример 8: Категоризация

Ако използваме инструмента Колона от примери към колона с числови данни, работи по различен начин. Да предположим, че имаме резултати от тестове на служители, заредени в Power Query (условни резултати в диапазона 0-100) и използваме следната условна градация:

• Мастърс – тези, които са отбелязали повече от 90
• Експерти – точки от 70 до 90
• Потребители – от 30 до 70
• Начинаещи – тези, които са получили по-малко от 30

Ако добавим колона от примерите към списъка и започнем да подреждаме тези градации ръчно, тогава много скоро Power Query ще подхване нашата идея и ще добави колона с формула, където операторите са вложени един в друг if ще бъде внедрена логика, много подобна на това, от което се нуждаем:

Отново не можете да натиснете ситуацията до края, но щракнете върху OK и след това коригирайте праговите стойности, които вече са във формулата – по-бързо е по този начин:

Заключения

Със сигурност инструмент Колона от примери не е „магическо хапче“ и рано или късно ще има нестандартни ситуации или особено пренебрегвани случаи на „колхоз“ в данните, когато Power Query ще се провали и няма да може да изработи това, което искаме правилно за нас. Въпреки това, като помощно средство, това е много добро. Освен това, като изучавате генерираните от него формули, можете да разширите знанията си за функциите на езика M, които винаги ще ви бъдат полезни в бъдеще.

• Разбор на текст с регулярни изрази (RegExp) в Power Query
• Търсене на размит текст в Power Query
• Flash Fill в Microsoft Excel