• Adgang i Excel er via Hent og transformer data sektionen i Data fanen. I Power BI findes det i Eksterne data afsnittet Hjem fanen.
• Indlæsning af CSV'er:Import af en CSV via Get &Transform gør det muligt at rense den og gøre den "snævrere" eller "bredere" for at hjælpe med datapivotering. Disse instruktioner kan gemmes og derefter gentages til fremtidig import.
• Håndtering af tekststrenge:Som en væsentlig forbedring i forhold til Tekst til kolonner funktionalitet i Excel, Get &Transform kan hurtigt analysere og adskille kombinerede tekst- og talstrenge i separate kolonner.
• Forskellige datakilder:Med en bred vifte af inputfiler accepteret, er det muligt at arbejde med forskellige kilder og samtidig bevare ensartet og normaliseret outputkvalitet.
• Tilpasning med kode:M-sproget er den funktionelle kode, der bruges i Get &Transform, og det er muligt at skrive tilpassede forespørgsler til flere skræddersyede anmodninger.

I denne tidsalder med datasøer og databaser i petabyte-skala er det chokerende, hvor ofte jeg stadig modtager data i form af CSV-, tekst- og Excel-filer. Mens moderne analyser fokuserer på banebrydende fremskridt inden for maskinlæringsalgoritmer, er det daglige arbejde med dataanalyse stadig en manuel proces med at finde, kompilere og skændes forskellige datatyper.

For finansanalytikeren kommer data ofte som et Excel-regneark, men lige så ofte er det et datadump i en CSV eller en forespørgsel i en SQL-database. Nogle gange er dataene arrangeret i et forvirrende layout eller har ikke alle de nødvendige komponenter til analyse. Tid brugt på at skrubbe disse data er værdifuld tid spildt for analytikeren, men til tider accepteres denne opgave som et nødvendigt onde, der skal tolereres.

Hvad gør Get &Transform?

En løsning på dette almindelige problem er faktisk ret tilgængelig:Excel og Power BI har et helt sæt af datatransformationsværktøjer, som få brugere kender til, kaldet Get &Transform (tidligere kendt som Power Query). Ved at bruge dens indlejrede ekstraktions-, transformations- og indlæsningsfunktionalitet (ETL) kan finansanalytikere problemfrit linke til deres datakilder og få indsigt hurtigere.

Når vi samler data op for at indlæse i Excel eller Power BI, skal vi normalt udføre nogle transformationer af dataene. Nogle eksempler på datamanipulation vil omfatte:

• Fjerner kolonner,
• Filtrering af data,
• Gruppering af data,
• Pivotering/ophævelse af pivotering af data,
• Opdeling af strenge i understrenge,
• Udtrækning af søgeord fra strenge,
• Tilføjelse af rækker fra en anden tabel, og
• Samling af todimensionelle tabeller.

I diagrammet nedenfor ser vi, at Get &Transform udfører denne kedelige rolle med at forbehandle dataene, før de indlæses.

Hvorfor skal du bruge Get &Transform?

Hvorfor er det umagen værd at lære at bruge Get &Transform? Nå, når jeg ser på, hvad jeg personligt har brugt denne funktion til, har den tilbudt mig et formbart sæt værktøjer til:

• Indlæsning af en hel mappe med tekstfiler i en enkelt datatabel
• Konvertering af eksporterede regnskabsfiler til et fordøjeligt layout
• Indlæser millioner af salgsrækker direkte i Power Pivot
• Grupper daglige data til håndterbare månedlige resultater, før de importeres til Excel
• Splejsning af data fra en anden tabel ved at forbinde på matchende kolonner

Generelt, når jeg modtager nye data, vil jeg udforske dem ved hjælp af Get &Transform, før jeg indlæser dem i Power Pivot. Dette giver mig mulighed for at se, hvilke transformationer der kan være nødvendige og hurtigt udføre nogle pivoter og grupperinger på dataene for at formulere en ramme for analyse. I mange tilfælde vil jeg på dette stadium opleve, at jeg har brug for mere data, eller at der er dataproblemer. Ved at bruge en Excel-baseret platform kan jeg hurtigt iterere med min datakilde for at finde disse dataanomalier.

I sidste ende vil beslutningen om at blive i Excel eller flytte dataanalysen til en anden platform afhænge af publikum og analysens repeterbarhed og distribution. Hvis mine kunder kun bruger Excel, så vil jeg næsten altid bruge Get &Transform til at indlæse dataene, Power Pivot til at udføre analysen og Excel til at producere pivottabellerne og diagrammerne. For klienten vil dette føles problemfrit, da det hele er placeret i Excel.

Men hvis min klient:

1. Ønsker at bruge et andet visualiseringsværktøj,
2. Har flere brugere, der vil opdatere dataene, eller
3. Har behov for at anvende maskinlæringsmodeller,

Så vil jeg udelukkende bruge Get &Transform til den indledende dataudforskning og derefter flytte det tunge løft til R.

Sådan får du adgang til Get &Transform i Excel eller Power BI

I tidligere versioner af Excel var Power Query et tilføjelsesprogram, der kunne installeres for at hjælpe med ETL-funktioner. I Excel 2016 og Power BI er disse værktøjer dog tættere integreret. I Excel 2016 kan de tilgås via Data fanen og derefter Hent og transformer data afsnit.

I Power BI findes funktionaliteten på Hjem fanen i Eksterne data afsnit.

I denne artikel foregår mine eksempler i Power BI, men grænsefladen er næsten identisk med Excels. Jeg vil påpege forskellene, når de opstår, så selvstudiet burde give mening for begge typer brugere.

1. Indlæser CSV-filer

For at hjælpe denne vejledning har jeg lavet et par eksempler på salgsdata for en fiktiv forhandler, der sælger udendørsudstyr og -tøj. I hvert af disse eksempler vil dataene blive produceret på forskellige måder for at demonstrere realistiske metoder til datadumps.

Som et indledende eksempel vil vi se dataene præsenteret som et stort datadump i en CSV-fil. Den komplicerende faktor er, at dataene præsenteres med flere kolonner, der repræsenterer forskellige butikker. Vi vil ideelt set gerne importere og transformere dataene til et mere anvendeligt layout.

Nedenfor er et skærmbillede af, hvordan den rå CSV ser ud:

Hvorfor vil vi ændre dette? For at drage fordel af de relationsmuligheder, der er mulige i disse applikationer. Vi vil se dette udspille sig længere fremme i diskussionen.

Lad os indtil videre antage, at vi skal se dataene som en "snævrere og højere" struktur snarere end en "bredere og kortere". Det første trin er at indlæse CSV'en; derefter vil vi begynde at "unpivot" dataene.

Som du kan se, er den endelige struktur af dataene smallere end de oprindelige data og meget længere. Et andet punkt er, at når vi klikker på forskellige handlinger, genererer værktøjet i højre side en liste over anvendte trin, der bruges til at bygge forespørgslen. Det er vigtigt at forstå, at dette foregår i baggrunden, da det vil blive gentaget senere.

Get &Transform ser ud og opfører sig på samme måde mellem Power BI og Excel for det meste. Men i Excel, efter at du har klikket på Luk og indlæs , er der en ekstra prompt. I figuren nedenfor kan vi skifte mellem, om vi ønsker at indlæse dataene i:

1. En tabel i Excel,
2. En pivottabel oprettet mod dataene,
3. Et pivotdiagram oprettet mod dataene, eller
4. "Opret kun en forbindelse."

Derudover får vi også mulighed for at føje disse data til datamodellen eller ej. . Hvis du markerer dette felt, indlæses dataene i en Power Pivot-tabel. Hvis vi skal analysere dataene i Power Pivot, anbefaler jeg, at du vælger Opret kun en forbindelse og sørg derefter for, at Tilføj disse data til datamodellen mulighed er valgt. Hvis dataene er inden for Excel-rækkegrænsen, og vi foretrækker at udføre vores analyse i Excel, skal du blot vælge Tabel .

I det næste klip vil vi se, at grunden til, at vi formaterede dataene til at være lange og tynde, er, at vi er i stand til at analysere salget ikke kun efter butik, men også efter region og stat. For at udføre denne opgave importerer vi en tabel, der kortlægger hver butik til en region og stat. Vi vil se nedenfor, at vi hurtigt kan oprette rapporter, der viser salg efter disse forskellige grupperinger.

Du kan forestille dig, hvordan denne type kapacitet til datatransformation i Excel eller Power BI effektivt kan anvendes i alle tilfælde, hvor vi har dynamiske grupperinger af data, såsom:

• Rulning af daglige data i uger, måneder og kvartaler;
• Grupper salgspersonale i afdelinger og regioner; eller
• Kortlægning af SKU'er til produkttyper.

Mens denne artikel omhandler CSV og andre Excel-filer, håndterer Get &Transform en lang række datatyper. Når en forespørgsel er oprettet, kan den opdateres over tid, efterhånden som dataene ændres.

2. Håndtering af tekststrenge

For at demonstrere Get &Transforms evne til at manipulere strenge, oprettede jeg et andet datasæt, der efterligner en tekstfil, der viser regnskabstransaktioner fra en virksomheds hovedbog (GL).

Læg mærke til, hvordan kontonummer og navn vises i samme streng? I Power BI kan vi nemt parse kontonummer og navn i separate felter.

I denne video kan du se, at efter at jeg delte kolonnen, gættede værktøjet på, at den nye venstre side af Konto-feltet skulle være et tal, og det opretter et "Ændret Type1"-trin. Da vi i sidste ende vil have dette felt som en streng, kan vi gå videre og slette trinnet manuelt under de anvendte trin.

Dernæst tager vi de samme data og opretter en kontoplan med tilknytninger til kontokategorier.

Hvorfor skulle vi gennemgå alle disse trin for at kortlægge nogle få kontonumre? En rigtig hovedbog kan være hundreder eller endda tusindvis af konti. Denne hurtige kortlægningsforespørgsel, som vi har vist, vil skalere til det niveau uden yderligere arbejde.

3. Arbejde med forskellige datakilder

Get &Transform understøtter mange forskellige datakilder. Selvom det ikke er en udtømmende liste, er der nogle eksempler nedenfor:

Personligt har jeg kun prøvet omkring halvdelen af ​​forbindelserne på ovenstående liste. Hvert af de stik, jeg har brugt, har været ret robuste; Jeg er kommet fra rådata til indsigt uden en tyngende mængde arbejde. Lige så vigtigt fungerer det som en validator mellem forskellige datakilder og sikrer, at slutresultaterne har et normaliseret niveau af kvalitetskontrol.

4. Tilpasning af kode med M-sproget

I baggrunden genererer Get &Transform kode, hver gang vi klikker på en knap i værktøjet eller foretager et valg. Nedenfor er et eksempel på, hvordan du får adgang til koden til den kontotilknytningsforespørgsel, vi oprettede:

Koden bruger et funktionelt sprog ved navn M, som automatisk genererer til grundlæggende brugstilfælde. For mere kompliceret datastrid kan vi dog redigere og skrive vores egen kode. I de fleste tilfælde vil jeg kun foretage mindre ændringer af denne kode. I mere komplicerede transformationer kan jeg skrive det meste af koden fra bunden til at iscenesætte midlertidige tabeller eller for at udføre mere komplicerede joinforbindelser.

Grænserne for Get &Transform

Excel har en tendens til at nå sine grænser, når du prøver at eksportere mere end en million rækker. I de tilfælde, hvor jeg har transformeret millioner af rækker med Get &Transform, er den eneste måde at sende ugrupperede rækker ud via kedelige hacks eller løsninger. Jeg har også fundet ud af, at Get &Transform-forespørgsler kan være ustabile at implementere til flere brugere, især hvis du bruger flere datakilder og joinforbindelser. I disse tilfælde vil jeg altid bruge R til at implementere den duplikerbare datastrid. Endelig er Excel ikke bygget til mere avanceret datamodellering. Du kan udføre lineære regressioner ret hurtigt, men derudover bliver du nødt til at bruge en mere stringent platform.

Når alt det er sagt, finder jeg ud af, at Excel er det, de fleste af mine kunder er mest komfortable med. Excel er stadig det vigtigste værktøj i en finansanalytikers arsenal. Ved at inkorporere Get &Transform-funktionaliteten bliver Excel og Power BI endnu mere kraftfulde gennem den række af datakilder, som de kan acceptere.