I denna tid av datasjöar och petabyte-skala databaser är det chockerande hur ofta jag fortfarande får data i form av CSV-, text- och Excel-filer. Medan modern analys fokuserar på banbrytande framsteg inom maskininlärningsalgoritmer, är det dagliga arbetet med dataanalys fortfarande en manuell process för att hitta, sammanställa och tvista om olika datatyper.
För finansanalytikern kommer data ofta som ett Excel-kalkylblad, men lika ofta är det en datadump i en CSV eller en fråga till en SQL-databas. Ibland är data ordnade i en förvirrande layout eller har inte alla nödvändiga komponenter för analys. Tid som ägnas åt att skrubba dessa data är värdefull tid som slösas bort för analytikern, men ibland accepteras denna uppgift som ett nödvändigt ont som ska tolereras.
En lösning på detta vanliga problem är faktiskt ganska lättillgänglig:Excel och Power BI har en hel uppsättning datatransformationsverktyg som få användare är medvetna om, som heter Get &Transform (tidigare känt som Power Query). Genom att använda dess inbäddade extrahera, transformera och ladda (ETL) funktionalitet gör det möjligt för finansanalytiker att sömlöst länka till sina datakällor och få insikter snabbare.
När vi tar upp data för att ladda in i Excel eller Power BI måste vi vanligtvis utföra vissa transformationer av data. Några exempel på datamanipulation kan vara:
I diagrammet nedan ser vi att Get &Transform utför denna tråkiga roll att förbehandla data innan den laddas.
Varför är det värt att lära sig hur man använder Get &Transform? Tja, när jag tittar på vad jag personligen har använt den här funktionen till, har den erbjudit mig en formbar uppsättning verktyg för:
När jag får ny data kommer jag i allmänhet att utforska den med hjälp av Get &Transform innan jag laddar in den i Power Pivot. Detta gör att jag kan se vilka transformationer som kan vara nödvändiga och snabbt utföra några pivoter och grupperingar på data för att formulera ett ramverk för analys. I många fall kommer jag i detta skede att upptäcka att jag behöver mer data, eller att det finns dataproblem. Genom att använda en Excel-baserad plattform kan jag snabbt iterera med min datakälla för att hitta dessa dataavvikelser.
I slutändan kommer beslutet att stanna i Excel eller flytta dataanalysen till en annan plattform att bero på publiken och analysens repeterbarhet och distribution. Om mina kunder bara använder Excel kommer jag nästan alltid att använda Get &Transform för att ladda data, Power Pivot för att utföra analysen och Excel för att producera pivottabellerna och diagrammen. För kunden kommer detta att kännas sömlöst eftersom allt är inrymt i Excel.
Men om min klient:
Sedan kommer jag att använda Get &Transform enbart för den inledande datautforskningen och sedan flytta det tunga lyftet till R.
I tidigare versioner av Excel var Power Query ett tillägg som kunde installeras för att hjälpa till med ETL-funktioner. Men i Excel 2016 och Power BI är dessa verktyg mer integrerade. I Excel 2016 kan de nås via Data fliken och sedan Hämta och transformera data avsnitt.
I Power BI finns funktionaliteten på Hem på fliken Externa data avsnitt.
I den här artikeln sker mina exempel i Power BI, men gränssnittet är nästan identiskt med Excels. Jag kommer att påpeka skillnaderna när de uppstår så handledningen bör vara vettig för båda typerna av användare.
För att hjälpa den här handledningen har jag skapat några exempel på försäljningsdata för en fiktiv återförsäljare som säljer utomhusutrustning och kläder. I vart och ett av dessa exempel kommer data att produceras på olika sätt för att visa realistiska metoder för datadumpning.
Som ett första exempel kommer vi att se data presenteras som en stor datadump i en CSV-fil. Den komplicerande faktorn är att data presenteras med flera kolumner som representerar olika butiker. Vi skulle helst vilja importera och omvandla data till en mer användbar layout.
Nedan är en skärmdump av hur den råa CSV-filen ser ut:
Varför skulle vi vilja ändra på detta? Att dra nytta av relationsmöjligheterna som är möjliga i dessa applikationer. Vi kommer att se detta spela ut längre fram i diskussionen.
För nu, låt oss anta att vi behöver se data som en "smalare och längre" struktur, snarare än en "bredare och kortare". Det första steget är att ladda CSV-filen; sedan kommer vi att börja "avpivota" data.
Som du kan se är den slutliga strukturen för data snävare än den ursprungliga data, och mycket längre. En annan sak är att när vi klickar på olika åtgärder genererar verktyget på höger sida en lista över tillämpade steg som används för att bygga frågan. Det är viktigt att förstå att detta pågår i bakgrunden, eftersom det kommer att återkomma senare.
Get &Transform ser ut och beter sig på samma sätt mellan Power BI och Excel för det mesta. Men i Excel, efter att ha klickat på Stäng och ladda , det finns ytterligare en uppmaning. I figuren nedan kan vi växla mellan om vi vill ladda data till:
Dessutom får vi också möjligheten att lägga till denna data i datamodellen eller inte. . Om du markerar den här rutan laddas data till en Power Pivot-tabell. Om vi ska analysera data i Power Pivot rekommenderar jag att du väljer Skapa endast en anslutning och se sedan till att Lägg till denna data i datamodellen alternativet är valt. Om data ligger inom Excel-radgränsen och vi föredrar att göra vår analys i Excel, välj bara Tabell .
I nästa klipp kommer vi att se att anledningen till att vi formaterade datan för att vara lång och smal är för att vi ska kunna analysera försäljningen inte bara per butik utan även per region och stat. För att utföra denna uppgift kommer vi att importera en tabell som mappar varje butik till en region och stat. Vi kommer att se nedan att vi snabbt kan skapa rapporter som visar försäljning efter dessa olika grupperingar.
Du kan föreställa dig hur den här typen av kapacitet för datatransformation i Excel eller Power BI på ett kraftfullt sätt kan tillämpas på alla fall där vi har dynamiska grupperingar av data, som:
Även om den här artikeln tar upp CSV- och andra Excel-filer, hanterar Get &Transform ett brett utbud av datatyper. När en fråga väl har skapats kan den uppdateras med tiden när data ändras.
För att demonstrera Get &Transforms förmåga att manipulera strängar skapade jag en annan datauppsättning som efterliknar en textfil som visar redovisningstransaktioner från ett företags huvudbok (GL).
Lägger du märke till hur kontonummer och namn visas i samma sträng? I Power BI kan vi enkelt analysera kontonumret och namnet i separata fält.
I den här videon kan du se att efter att jag delat kolumnen gissade verktyget att den nya vänstra sidan av kontofältet skulle vara ett nummer, och det skapar ett "Ändrad typ1"-steg. Eftersom vi i slutändan vill ha det här fältet som en sträng, kan vi gå vidare och ta bort steget manuellt under de tillämpade stegen.
Därefter tar vi samma data och skapar en kontoplan med mappningar till kontokategorier.
Varför skulle vi gå igenom alla dessa steg för att kartlägga några kontonummer? En riktig huvudbok kan vara hundratals eller till och med tusentals konton. Den här snabba kartläggningsfrågan, som vi har visat, skulle skalas till den nivån utan ytterligare arbete.
Get &Transform stöder många olika datakällor. Även om det inte är en uttömmande lista, finns nedan några exempel:
Textfil Excel Facebook Adobe Analytics Google Analytics Salesforce Azure Redshift Spark SQL Server SAP HANA Teradata Google BigQueryPersonligen har jag bara provat ungefär hälften av anslutningarna på listan ovan. Var och en av kontakterna jag har använt har varit ganska robusta; Jag har kommit från rådata till insikter utan en betungande mängd arbete. Lika viktigt är det att det fungerar som en validator mellan olika datakällor, vilket säkerställer att slutresultaten har en normaliserad nivå av kvalitetskontroll.
I bakgrunden genererar Get &Transform kod varje gång vi klickar på en knapp i verktyget eller gör ett urval. Nedan är ett exempel på hur du kommer åt koden för den kontomappningsfråga vi skapade:
Koden använder ett funktionellt språk som heter M, som automatiskt genereras för grundläggande användningsfall. Men för mer komplicerad datatvistning kan vi redigera och skriva vår egen kod. I de flesta fall kommer jag bara att göra mindre ändringar av den här koden. I mer komplicerade transformationer kan jag skriva det mesta av koden från början till temporära tabeller, eller för att utföra mer komplicerade sammanfogningar.
Excel tenderar att nå sina gränser när du försöker exportera mer än en miljon rader. I de fall där jag har förvandlat miljontals rader med Get &Transform, är det enda sättet att skicka ut ogrupperade rader via tråkiga hacks eller lösningar. Jag har också funnit att Get &Transform-frågor kan vara instabila att distribuera till flera användare, särskilt om du använder flera datakällor och kopplingar. I dessa fall kommer jag alltid att använda R för att distribuera den duplicerade datatvistelsen. Slutligen är Excel inte byggt för mer avancerad datamodellering. Du kan utföra linjära regressioner ganska snabbt, men utöver det måste du använda en mer rigorös plattform.
Med det sagt tycker jag att Excel är det som de flesta av mina kunder är mest bekväma med. Excel är fortfarande det viktigaste verktyget i en finansanalytikers arsenal. Genom att införliva Get &Transform-funktionen blir Excel och Power BI ännu mer kraftfulla genom de olika datakällor som de kan acceptera.