Innehåll
En av de mest sedda videorna på min YouTube-kanal är en video om Flash Fill i Microsoft Excel. Kärnan i detta verktyg är att om du på något sätt behöver omvandla dina källdata, behöver du bara börja skriva det resultat du vill få i den intilliggande kolumnen. Efter flera manuellt inskrivna celler (vanligtvis räcker 2-3), kommer Excel att "förstå" logiken i de transformationer du behöver och automatiskt fortsätta vad du har skrivit, och slutföra allt monotont arbete för dig:
Kvintessensen av effektivitet. Den magiska "gör det rätt"-knappen som vi alla älskar så mycket, eller hur?
Faktum är att det finns en analog till ett sådant verktyg i Power Query – där kallas det Kolumn från exempel (Kolumn från exempel). Faktum är att detta är en liten artificiell intelligens inbyggd i Power Query som snabbt kan lära sig av din data och sedan omvandla den. Låt oss ta en närmare titt på dess kapacitet i flera praktiska scenarier för att förstå var den kan vara användbar för oss i verkliga uppgifter.
Exempel 1. Limning/klippning av text
Låt oss säga att vi har en sådan "smart" tabell i Excel med data om anställda:
Ladda den i Power Query på standard sätt – med knappen Från tabell/sortiment fliken Data (Data – från tabell/intervall).
Anta att vi behöver lägga till en kolumn med efternamn och initialer för varje anställd (Ivanov SV för den första anställde, etc.). För att lösa detta problem kan du använda en av två metoder:
- högerklicka på kolumnrubriken med källdata och välj kommandot Lägg till kolumn från exempel (Lägg till kolumn från exempel);
- välj en eller flera kolumner med data och på fliken Lägger till en kolumn välja ett lag Kolumn från exempel. Här kan du i rullgardinsmenyn ange om alla eller endast valda kolumner behöver analyseras.
Sedan är allt enkelt – i kolumnen som visas till höger börjar vi ange exempel på önskade resultat, och den artificiella intelligensen inbyggd i Power Query försöker förstå vår transformationslogik och fortsätta vidare på egen hand:
Förresten, du kan ange rätt alternativ i alla celler i denna kolumn, dvs inte nödvändigtvis uppifrån och ned och i rad. Du kan också enkelt lägga till eller ta bort kolumner från analysen senare genom att använda kryssrutorna i namnlisten.
Var uppmärksam på formeln högst upp i fönstret – det här är vad smart Power Query skapar för att få de resultat vi behöver. Detta är förresten den grundläggande skillnaden mellan detta verktyg och Omedelbar fyllning i Excel. Omedelbar fyllning fungerar som en "svart låda" – de visar oss inte logiken i transformationerna, utan ger helt enkelt färdiga resultat och vi tar dem för givet. Här är allt transparent och du kan alltid helt klart förstå exakt vad som händer med datan.
Om du ser att Power Query "fångade idén", kan du säkert trycka på knappen OK eller kortkommandon ctrl+ange – en anpassad kolumn med en formel uppfunnen av Power Query kommer att skapas. Förresten, det kan senare enkelt redigeras som en vanlig manuellt skapad kolumn (med kommandot Lägga till en kolumn – Anpassad kolumn) genom att klicka på kugghjulsikonen till höger om stegnamnet:
Exempel 2: Case som i meningar
Om du högerklickar på kolumnrubriken med text och väljer kommandot Transformation (Omvandla), då kan du se tre kommandon som är ansvariga för att ändra registret:
Bekvämt och coolt, men i den här listan, till exempel, har jag personligen alltid saknat ett alternativ till – skiftläge som i meningar, när versaler (versal) inte blir den första bokstaven i varje ord, utan bara den första bokstaven i cellen, och resten av texten när Detta visas med gemener (små) bokstäver.
Denna saknade funktion är lätt att implementera med artificiell intelligens Kolumner från exempel – ange bara ett par alternativ för att Power Query ska fortsätta i samma anda:
Som en formel här använder Power Query en massa funktioner Text.Övre и Text.Lägre, konvertera text till versaler respektive gemener och funktioner Text.Start и Text.Mid – analoger till Excel-funktionerna VÄNSTER och PSTR, kan extrahera en delsträng från texten från vänster och från mitten.
Exempel 3. Permutation av ord
Ibland, när man bearbetar mottagna data, blir det nödvändigt att ordna om orden i cellerna i en given sekvens. Naturligtvis kan du dela upp kolumnen i separata ordkolumner av separatorn och sedan limma tillbaka den i angiven ordning (glöm inte att lägga till mellanslag), men med hjälp av verktyget Kolumn från exempel allt blir mycket lättare:
Exempel 4: Endast siffror
En annan mycket viktig uppgift är att bara dra ut siffror (siffror) från innehållet i cellen. Som tidigare, efter att ha laddat data i Power Query, gå till fliken Lägga till en kolumn – Kolumn från exempel och fyll i ett par celler manuellt så att programmet förstår exakt vad vi vill ha:
Bingo!
Återigen, det är värt att titta högst upp i fönstret för att se till att Query genererade formeln korrekt – i det här fallet innehåller den en funktion Text. Välj, som, som du kanske kan gissa, extraherar de givna tecknen från källtexten enligt listan. Därefter kan denna lista naturligtvis enkelt redigeras i formelfältet vid behov.
Exempel 5: Endast text
På samma sätt som i föregående exempel kan du dra ut och vice versa - bara texten, radera alla siffror, skiljetecken, etc.
I det här fallet används en funktion som redan har motsatt betydelse – Text.Remove, som tar bort tecken från den ursprungliga strängen enligt en given lista.
Exempel 6: Extrahera data från en alfanumerisk gröt
Power Query kan också hjälpa i svårare fall, när du behöver extrahera användbar information från den alfanumeriska gröten i en cell, till exempel få kontonumret från beskrivningen av betalningssyftet på ett kontoutdrag:
Observera att den Power Query-genererade konverteringsformeln kan vara ganska komplex:
För att underlätta läsning och förståelse kan den konverteras till en mycket mer sansad form med hjälp av en gratis onlinetjänst. Power Query Formatter:
Mycket praktiskt – respekt för skaparna!
Exempel 7: Konvertera datum
Verktyget Kolumn från exempel kan även tillämpas på kolumner för datum och datum. När du anger de första siffrorna i ett datum kommer Power Query att visa en lista med alla möjliga konverteringsalternativ:
Så du kan enkelt konvertera det ursprungliga datumet till vilket exotiskt format som helst, till exempel "år-månad-dag":
Exempel 8: Kategorisering
Om vi använder verktyget Kolumn från exempel till en kolumn med numeriska data fungerar det annorlunda. Anta att vi har medarbetartestresultat inlästa i Power Query (villkorliga poäng i intervallet 0-100) och vi använder följande villkorliga gradering:
- Masters – de som fick fler än 90 poäng
- Experter – fick poäng från 70 till 90
- Användare – från 30 till 70
- Nybörjare – de som fick mindre än 30 poäng
Om vi lägger till en kolumn från exemplen till listan och börjar ordna dessa graderingar manuellt, kommer Power Query snart att ta upp vår idé och lägga till en kolumn med en formel, där operatorer kapslade in i varandra if logik kommer att implementeras, mycket lik vad vi behöver:
Återigen, du kan inte trycka på situationen till slutet, utan klicka på OK och korrigera sedan tröskelvärdena redan i formeln – det går snabbare så här:
Slutsatser
Helt klart ett verktyg Kolumn från exempel är inte ett "magiskt piller" och förr eller senare kommer det att finnas icke-standardiserade situationer eller särskilt försummade fall av en "kollektiv gård" i data, när Power Query kommer att misslyckas och inte kommer att kunna räkna ut vad vi vill rätt för oss. Men som hjälpverktyg är det väldigt bra. Dessutom, genom att studera formlerna han genererade, kan du utöka dina kunskaper om funktionerna i M-språket, vilket alltid kommer att vara användbart i framtiden.
- Analysera text med reguljära uttryck (RegExp) i Power Query
- Luddrig textsökning i Power Query
- Flash Fyll i Microsoft Excel