Пример колона – Вештачка интелигенција во Power Query

Едно од најгледаните видеа на мојот канал на YouTube е видео за Flash Fill во Microsoft Excel. Суштината на оваа алатка е дека ако треба некако да ги трансформирате вашите изворни податоци, тогаш само треба да започнете да го пишувате резултатот што сакате да го добиете во соседната колона. По неколку рачно напишани ќелии (обично 2-3 се доволни), Excel ќе ја „разбере“ логиката на трансформациите што ви се потребни и автоматски ќе го продолжи она што сте го напишале, завршувајќи ја целата монотона работа за вас:

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

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

Пример 1. Лепење/сечење текст

Да речеме дека имаме таква „паметна“ табела во Excel со податоци за вработените:

Вчитајте го во Power Query на стандарден начин - со копчето Од Табела/Опсег табот податоци (Податоци - од табела/опсег).

Да претпоставиме дека треба да додадеме колона со презимиња и иницијали за секој вработен (Иванов СВ за првиот вработен итн.). За да го решите овој проблем, можете да користите еден од двата методи:

• кликнете со десното копче на насловот на колоната со изворните податоци и изберете ја командата Додадете колона од примери (Додадете колона од примери);

• изберете една или повеќе колони со податоци и на јазичето Додавање колона изберете тим Колона од примери. Овде, во паѓачката листа, можете да одредите дали треба да се анализираат сите или само избраните колони.

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

Патем, можете да ги внесете точните опции во кои било ќелии од оваа колона, односно не мора од горе-надолу и во низа. Исто така, можете лесно да додавате или отстранувате колони од анализата подоцна користејќи ги полињата за избор во насловната лента.

Обрнете внимание на формулата на горниот дел од прозорецот - ова го создава паметниот 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 може да биде доста сложена:

За полесно читање и разбирање, може да се претвори во многу поздрава форма користејќи бесплатна онлајн услуга. Форматер за напојување:

Многу корисна работа – почит до креаторите!

Пример 7: Конвертирање на датуми

Алатка Колона од примери може да се примени и на колоните за датум или датум време. Кога ќе ги внесете првите цифри од датумот, Power Query ќе прикаже список со сите можни опции за конверзија:

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

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

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

• Мастерс – оние кои постигнале повеќе од 90
• Експерти – постигнати од 70 до 90
• Корисници - од 30 до 70
• Почетници - оние кои постигнале помалку од 30

Ако додадеме колона од примерите на списокот и почнеме рачно да ги распоредуваме овие градации, тогаш многу наскоро Power Query ќе ја земе нашата идеја и ќе додаде колона со формула, каде што операторите се вгнездуваат еден во друг. if логиката ќе биде имплементирана, многу слична на она што ни треба:

Повторно, не можете да ја притиснете ситуацијата до крај, туку кликнете на OK а потоа поправете ги вредностите на прагот веќе во формулата - побрзо е вака:

Заклучоци

Секако алатка Колона од примери не е „волшебна пилула“ и, порано или подоцна, ќе има нестандардни ситуации или особено занемарени случаи на „колективна фарма“ во податоците, кога Power Query ќе пропадне и нема да може да го реши она што го сакаме правилно за нас. Сепак, како помошна алатка, таа е многу добра. Плус, со проучување на формулите што тој ги генерира, можете да го проширите вашето знаење за функциите на јазикот М, што секогаш ќе ви се најде во иднина.

• Парсирање на текст со регуларни изрази (RegExp) во Power Query
• Нејасно пребарување на текст во Power Query
• Flash Пополнете Microsoft Excel