Columna d'exemple: intel·ligència artificial a Power Query

Un dels vídeos més vistos al meu canal de YouTube és un vídeo sobre Flash Fill a Microsoft Excel. L'essència d'aquesta eina és que si necessiteu transformar d'alguna manera les vostres dades d'origen, només heu de començar a escriure el resultat que voleu obtenir a la columna adjacent. Després de diverses cel·les escrites manualment (normalment 2-3 són suficients), Excel "entendrerà" la lògica de les transformacions que necessiteu i continuarà automàticament el que heu escrit, completant tot el treball monòton per a vosaltres:

La quintaessència de l'eficiència. El botó màgic "fes-ho bé" que tant ens agrada, oi?

De fet, hi ha un anàleg d'aquesta eina a Power Query: allà s'anomena Columna d'exemples (Columna d'exemples). De fet, es tracta d'una petita intel·ligència artificial integrada a Power Query que pot aprendre ràpidament de les vostres dades i després transformar-les. Mirem més de prop les seves capacitats en diversos escenaris pràctics per entendre on ens pot ser útil en tasques reals.

Exemple 1. Enganxar/retallar text

Suposem que tenim una taula tan "intel·ligent" a Excel amb dades sobre els empleats:

Carregueu-lo a Power Query de la manera estàndard: amb el botó De Taula/Range llengüeta dades (Dades: de la taula/interval).

Suposem que hem d'afegir una columna amb cognoms i inicials per a cada empleat (Ivanov SV per al primer empleat, etc.). Per resoldre aquest problema, podeu utilitzar un dels dos mètodes:

• Feu clic amb el botó dret a l'encapçalament de la columna amb les dades d'origen i seleccioneu l'ordre Afegeix una columna d'exemples (Afegiu una columna d'exemples);

• seleccioneu una o més columnes amb dades i a la pestanya Afegint una columna triar un equip Columna d'exemples. Aquí, a la llista desplegable, podeu especificar si s'han d'analitzar totes o només les columnes seleccionades.

Aleshores, tot és senzill: a la columna que apareix a la dreta, comencem a introduir exemples dels resultats desitjats i la intel·ligència artificial integrada a Power Query intenta entendre la nostra lògica de transformació i continuar per si mateixa:

Per cert, podeu introduir les opcions correctes a qualsevol cel·la d'aquesta columna, és a dir, no necessàriament de dalt a baix i en fila. A més, podeu afegir o eliminar columnes de l'anàlisi més endavant mitjançant les caselles de selecció de la barra de títol.

Fixeu-vos en la fórmula de la part superior de la finestra: això és el que crea Smart Power Query per obtenir els resultats que necessitem. Aquesta, per cert, és la diferència fonamental entre aquesta eina i Ompliment instantani en Excel. L'ompliment instantani funciona com una "caixa negra": no ens mostren la lògica de les transformacions, sinó que simplement donen resultats ja fets i els donem per fets. Aquí tot és transparent i sempre podeu entendre clarament què està passant exactament amb les dades.

Si veieu que Power Query "va captar la idea", podeu prémer el botó amb seguretat OK o drecera de teclat Ctrl+Enter – es crearà una columna personalitzada amb una fórmula inventada per Power Query. Per cert, més tard es pot editar fàcilment com una columna creada manualment (amb l'ordre Afegir una columna: columna personalitzada) fent clic a la icona d'engranatge a la dreta del nom del pas:

Exemple 2: Cas com en oracions

Si feu clic amb el botó dret a l'encapçalament de la columna amb text i seleccioneu l'ordre Transformació (Transformar), llavors podeu veure tres ordres responsables de canviar el registre:

Convenient i genial, però en aquesta llista, per exemple, personalment sempre m'ha faltat una opció més: cas com en les frases, quan les majúscules (majúscules) no es converteixen en la primera lletra de cada paraula, sinó només en la primera lletra de la cel·la, i la resta del text quan Això es mostra en minúscules (petites).

Aquesta característica que falta és fàcil d'implementar amb intel·ligència artificial Columnes d'exemples – només cal que introduïu un parell d'opcions perquè Power Query continuï amb el mateix esperit:

Com a fórmula aquí, Power Query utilitza un munt de funcions Text.Superior и Text.Inferior, convertint el text a majúscules i minúscules, respectivament, i funcions Text.Inici и Text. Mitjà – anàlegs de les funcions d'Excel LEFT i PSTR, capaços d'extreure una subcadena del text des de l'esquerra i del mig.

Exemple 3. Permutació de paraules

De vegades, quan es processen les dades rebudes, es fa necessari reordenar les paraules de les cel·les en una seqüència determinada. Per descomptat, podeu dividir la columna en columnes de paraules separades pel separador i després enganxar-la de nou en l'ordre especificat (no us oblideu d'afegir espais), però amb l'ajuda de l'eina Columna d'exemples tot serà molt més fàcil:

Exemple 4: només números

Una altra tasca molt important és treure només números (nombres) del contingut de la cel·la. Com abans, després de carregar dades a Power Query, aneu a la pestanya Afegir una columna - Columna d'exemples i ompliu un parell de cel·les manualment perquè el programa entengui què volem obtenir exactament:

Bingo!

Un cop més, val la pena mirar la part superior de la finestra per assegurar-se que Query ha generat la fórmula correctament; en aquest cas, conté una funció Text. Seleccioneu, que, com podeu endevinar, extreu els caràcters donats del text font segons la llista. Posteriorment, aquesta llista, per descomptat, es pot editar fàcilment a la barra de fórmules si cal.

Exemple 5: només text

De manera similar a l'exemple anterior, podeu treure i viceversa: només el text, eliminant tots els números, signes de puntuació, etc.

En aquest cas, s'utilitza una funció que ja té un significat oposat: Text.Remove, que elimina caràcters de la cadena original segons una llista determinada.

Exemple 6: Extracció de dades d'una farineta alfanumèrica

Power Query també pot ajudar en casos més difícils, quan necessiteu extreure informació útil de les farinetes alfanumèriques d'una cel·la, per exemple, obtenir el número de compte de la descripció de la finalitat del pagament en un extracte bancari:

Tingueu en compte que la fórmula de conversió generada per Power Query pot ser força complexa:

Per facilitar la lectura i la comprensió, es pot convertir en una forma molt més sana mitjançant un servei en línia gratuït. Power Query Formatter:

Una cosa molt útil: respecte als creadors!

Exemple 7: conversió de dates

Eina Columna d'exemples també es pot aplicar a les columnes de data i hora. Quan introduïu els primers dígits d'una data, Power Query mostrarà una llista de totes les opcions de conversió possibles:

Així, podeu convertir fàcilment la data original a qualsevol format exòtic, com ara "any-mes-dia":

Exemple 8: categorització

Si fem servir l'eina Columna d'exemples a una columna amb dades numèriques, funciona de manera diferent. Suposem que tenim els resultats de les proves dels empleats carregats a Power Query (puntuació condicional entre 0 i 100) i fem servir la següent gradació condicional:

• Màsters: aquells que van obtenir més de 90
• Experts: puntuacions de 70 a 90
• Usuaris: de 30 a 70
• Principiants: aquells que han obtingut menys de 30 punts

Si afegim una columna dels exemples a la llista i comencem a organitzar aquestes gradacions manualment, molt aviat Power Query recollirà la nostra idea i afegirà una columna amb una fórmula, on els operadors s'imbricaran entre ells. if s'implementarà una lògica molt semblant a la que necessitem:

De nou, no podeu prémer la situació fins al final, sinó fer clic a sobre OK i, a continuació, corregiu els valors de llindar que ja estan a la fórmula; és més ràpid d'aquesta manera:

Conclusions

Sens dubte, una eina Columna d'exemples no és una "píndola màgica" i, tard o d'hora, hi haurà situacions no estàndard o casos especialment descuidats d'una "granja col·lectiva" a les dades, quan Power Query fallarà i no serà capaç d'esbrinar què volem. correctament per a nosaltres. Tanmateix, com a eina auxiliar, és molt bona. A més, estudiant les fórmules que va generar, podràs ampliar el teu coneixement de les funcions del llenguatge M, que sempre et resultarà útil en el futur.

• Anàlisi de text amb expressions regulars (RegExp) a Power Query
• Cerca de text difusa a Power Query
• Omplir Flash a Microsoft Excel