In dit tijdperk van data lakes en petabyte-schaal databases, is het schokkend hoe vaak ik nog steeds gegevens ontvang in de vorm van CSV-, tekst- en Excel-bestanden. Terwijl moderne analyses zich richten op de allernieuwste vooruitgang in algoritmen voor machinaal leren, is de dagelijkse sleur van gegevensanalyse nog steeds een handmatig proces van het vinden, samenstellen en ruziën van ongelijksoortige gegevenstypen.
Voor de financieel analist komen gegevens vaak binnen als een Excel-spreadsheet, maar net zo vaak is het een gegevensdump in een CSV of een query in een SQL-database. Soms zijn de gegevens in een verwarrende lay-out gerangschikt of hebben ze niet alle benodigde componenten voor analyse. Tijd die wordt besteed aan het opschonen van deze gegevens is kostbare tijdverspilling voor de analist, maar soms wordt deze taak geaccepteerd als een noodzakelijk kwaad dat moet worden getolereerd.
Een oplossing voor dit veelvoorkomende probleem is eigenlijk vrij toegankelijk:Excel en Power BI hebben een hele reeks tools voor gegevenstransformatie waarvan maar weinig gebruikers op de hoogte zijn, genaamd Get &Transform (voorheen bekend als Power Query). Door de ingebouwde ETL-functionaliteit (Extract, Transform and Load) te gebruiken, kunnen financiële analisten naadloos verbinding maken met hun gegevensbronnen en sneller tot inzichten komen.
Als we gegevens verzamelen om in Excel of Power BI te laden, moeten we meestal enkele transformaties in de gegevens uitvoeren. Enkele voorbeelden van gegevensmanipulatie zijn:
In het onderstaande diagram zien we dat Get &Transform deze vervelende rol vervult van het voorbewerken van de gegevens voordat deze worden geladen.
Waarom is het de moeite waard om Get &Transform te leren gebruiken? Welnu, als ik kijk waar ik deze functionaliteit persoonlijk voor heb gebruikt, heeft het me een kneedbare set tools geboden voor:
Over het algemeen zal ik, wanneer ik nieuwe gegevens ontvang, deze verkennen met Get &Transform voordat ik deze in Power Pivot laad. Dit stelt me in staat om te zien welke transformaties nodig kunnen zijn en snel een aantal pivots en groeperingen op de gegevens uit te voeren om een raamwerk voor analyse te formuleren. In veel gevallen zal ik in dit stadium merken dat ik meer gegevens nodig heb of dat er gegevensproblemen zijn. Door een op Excel gebaseerd platform te gebruiken, kan ik snel met mijn gegevensbron itereren om deze gegevensafwijkingen te vinden.
Uiteindelijk zal de beslissing om in Excel te blijven of de data-analyse naar een ander platform te verplaatsen afhangen van het publiek en de herhaalbaarheid en distributie van de analyse. Als mijn klanten alleen Excel gebruiken, zal ik bijna altijd Get &Transform gebruiken om de gegevens te laden, Power Pivot om de analyse uit te voeren en Excel om de draaitabellen en grafieken te produceren. Voor de klant zal dit naadloos aanvoelen omdat het allemaal in Excel is ondergebracht.
Als mijn cliënt echter:
Dan zal ik Get &Transform alleen gebruiken voor de eerste gegevensverkenning en dan het zware werk naar R verplaatsen.
In eerdere versies van Excel was Power Query een invoegtoepassing die kon worden geïnstalleerd om te helpen met ETL-functies. In Excel 2016 en Power BI zijn deze hulpprogramma's echter nauwer geïntegreerd. In Excel 2016 zijn ze toegankelijk via de Data tabblad en vervolgens de Gegevens ophalen en transformeren sectie.
In Power BI bestaat de functionaliteit op de Home tabblad, in de Externe gegevens sectie.
In dit artikel vinden mijn voorbeelden plaats in Power BI, maar de interface is bijna identiek aan die van Excel. Ik zal de verschillen aangeven wanneer ze zich voordoen, zodat de tutorial voor beide soorten gebruikers logisch moet zijn.
Om deze tutorial te helpen, heb ik een paar voorbeelden van verkoopgegevens gemaakt voor een fictieve detailhandelaar die outdooruitrusting en kleding verkoopt. In elk van deze voorbeelden worden de gegevens op verschillende manieren geproduceerd om realistische methoden voor gegevensdumps te demonstreren.
Als een eerste voorbeeld zullen we de gegevens zien die worden gepresenteerd als een grote gegevensdump in een CSV-bestand. De complicerende factor is dat de gegevens worden gepresenteerd met meerdere kolommen die verschillende winkels vertegenwoordigen. Idealiter zouden we de gegevens willen importeren en omzetten in een meer bruikbare lay-out.
Hieronder is een screenshot van hoe de onbewerkte CSV eruit ziet:
Waarom zouden we dit willen veranderen? Om te profiteren van de relatiemogelijkheden die mogelijk zijn in deze toepassingen. We zullen dit later in de discussie zien gebeuren.
Laten we voorlopig aannemen dat we de gegevens moeten zien als een "smallere en hogere" structuur, in plaats van een "bredere en kortere" structuur. De eerste stap is het laden van de CSV; dan zullen we beginnen met het "unpivoteren" van de gegevens.
Zoals u kunt zien, is de uiteindelijke structuur van de gegevens smaller dan de oorspronkelijke gegevens, en veel langer. Een ander punt is dat, terwijl we op verschillende acties klikken, de tool aan de rechterkant een lijst met toegepaste stappen genereert die zijn gebruikt om de query te bouwen. Het is belangrijk om te begrijpen dat dit op de achtergrond gebeurt, omdat het later opnieuw zal worden bekeken.
Get &Transform ziet eruit en gedraagt zich voor het grootste deel op dezelfde manier tussen Power BI en Excel. Echter, in Excel, na het klikken op Sluiten en laden , is er nog een extra prompt. In de onderstaande afbeelding kunnen we schakelen tussen of we de gegevens willen laden in:
Daarnaast krijgen we ook de optie om al dan niet Deze gegevens aan het gegevensmodel toe te voegen . Als u dit vakje aanvinkt, worden de gegevens in een Power Pivot-tabel geladen. Als we de gegevens in Power Pivot gaan analyseren, raad ik aan om Alleen een verbinding maken te kiezen en zorg er vervolgens voor dat de Deze gegevens toevoegen aan het gegevensmodel optie is geselecteerd. Als de gegevens binnen de Excel-rijlimiet vallen en we onze analyse liever in Excel uitvoeren, kies dan gewoon Tabel .
In de volgende clip zullen we zien dat de reden waarom we de gegevens lang en dun hebben opgemaakt, is dat we de verkoop niet alleen per winkel, maar ook per regio en staat kunnen analyseren. Om deze taak te volbrengen, importeren we een tabel die elke winkel toewijst aan een regio en staat. We zullen hieronder zien dat we snel rapporten kunnen maken die verkopen weergeven op basis van deze verschillende groepen.
U kunt zich voorstellen hoe dit type mogelijkheid voor gegevenstransformatie in Excel, of Power BI, krachtig kan worden toegepast in elk geval waarin we dynamische gegevensgroeperingen hebben, zoals:
Terwijl dit artikel CSV en andere Excel-bestanden behandelt, behandelt Get &Transform een breed scala aan gegevenstypen. Nadat een query is gemaakt, kan deze na verloop van tijd worden vernieuwd als de gegevens veranderen.
Om het vermogen van Get &Transform om strings te manipuleren te demonstreren, heb ik een andere dataset gemaakt die een tekstbestand nabootst met boekhoudtransacties uit het grootboek van een bedrijf (GL).
Merk op hoe het rekeningnummer en de naam in dezelfde string verschijnen? In Power BI kunnen we het rekeningnummer en de naam moeiteloos ontleden in aparte velden.
In deze video kun je zien dat nadat ik de kolom had gesplitst, de tool vermoedde dat de nieuwe linkerkant van het veld Account een getal zou moeten zijn, en het creëert een stap "Gewijzigd type1". Omdat we dit veld uiteindelijk als een string willen, kunnen we doorgaan en de stap handmatig verwijderen onder de toegepaste stappen.
Vervolgens gebruiken we dezelfde gegevens en maken we een rekeningschema met toewijzingen aan accountcategorieën.
Waarom zouden we al die stappen doorlopen om een paar rekeningnummers in kaart te brengen? Een echt grootboek kan honderden of zelfs duizenden rekeningen zijn. Deze snelle mapping-query, zoals we hebben aangetoond, zou zonder extra werk naar dat niveau worden geschaald.
Get &Transform ondersteunt veel verschillende gegevensbronnen. Hoewel het geen uitputtende lijst is, zijn hieronder enkele voorbeelden:
Tekstbestand Excel Facebook Adobe Analytics Google Analytics Salesforce Azure Redshift Spark SQL Server SAP HANA Teradata Google BigQueryPersoonlijk heb ik slechts ongeveer de helft van de verbindingen in de bovenstaande lijst geprobeerd. Elk van de connectoren die ik heb gebruikt, is redelijk robuust; Ik ben van ruwe data naar inzichten gekomen zonder al te veel werk. Even belangrijk is dat het dient als validator tussen ongelijksoortige gegevensbronnen en ervoor zorgt dat de eindresultaten een genormaliseerd kwaliteitscontroleniveau hebben.
Op de achtergrond genereert Get &Transform code elke keer dat we op een knop in de tool klikken of een selectie maken. Hieronder ziet u een voorbeeld van hoe u toegang zou krijgen tot de code voor de accounttoewijzingsquery die we hebben gemaakt:
De code gebruikt een functionele taal met de naam M, die automatisch wordt gegenereerd voor basisgebruiksscenario's. Voor meer gecompliceerde gegevensruzie kunnen we echter onze eigen code bewerken en schrijven. In de meeste gevallen zal ik alleen kleine wijzigingen in deze code aanbrengen. Bij meer gecompliceerde transformaties kan ik de meeste code helemaal opnieuw schrijven om tijdelijke tabellen te stagen, of voor het uitvoeren van meer gecompliceerde joins.
Excel heeft de neiging om zijn limieten te bereiken wanneer u meer dan een miljoen rijen probeert te exporteren. In de gevallen waarin ik miljoenen rijen heb getransformeerd met Get &Transform, is de enige manier om niet-gegroepeerde rijen te verzenden via vervelende hacks of tijdelijke oplossingen. Ik heb ook ontdekt dat Get &Transform-query's onstabiel kunnen zijn om voor meerdere gebruikers te implementeren, vooral als u meerdere gegevensbronnen en joins gebruikt. In die gevallen zal ik altijd R gebruiken om het dupliceren van gegevensgeruzie in te zetten. Ten slotte is Excel niet gebouwd voor meer geavanceerde gegevensmodellering. Je kunt vrij snel lineaire regressies uitvoeren, maar verder moet je een meer rigoureus platform gebruiken.
Dat gezegd hebbende, merk ik dat de meeste van mijn klanten zich het prettigst voelen bij Excel. Excel is nog steeds het belangrijkste hulpmiddel in het arsenaal van een financieel analist. Door de integratie van de Get &Transform-functionaliteit worden Excel en Power BI nog krachtiger door de reeks gegevensbronnen die ze kunnen accepteren.