Povzetek

Kaj je Get & Transform?

• Get & Transform je orodje za pretvorbo podatkov za uporabo v programskih paketih Microsoft Excel in Power BI.
• Podatki pogosto prispejo v nestrukturiranih oblikah, zaradi česar je postopek ETL (ekstrahiranje, preoblikovanje in nalaganje) dolgočasen postopek ročnih rešitev.
• Get & Transform avtomatizira in pospeši postopek čiščenja in organiziranja takšnih surovih podatkov, kar na koncu pomaga pri analitični nalogi razkrivanja opazovanj in trendov.
• Nekaj ​​primerov funkcionalnosti, ki jih ponuja Get & Transform, so: odstranjevanje stolpcev, združevanje podatkov v skupine, razdeljevanje nizov v podniz in dodajanje vrstic iz druge tabele.
• Za vzdrževanje delovnih tokov znotraj Excelovega vesolja je Get & Transform odlično orodje, ki ga je mogoče enostavno razložiti in pokazati ustreznim zainteresiranim stranem.

Kako lahko uporabim Get & Transform?

• Dostop v Excelu je prek Pridobite in preoblikujte podatke v razdelku Podatki zavihek. V Power BI obstaja v Zunanji podatki odsek Domov zavihek.
• Nalaganje CSV-jev: Uvoz CSV-ja prek funkcije Get & Transform omogoča njegovo čiščenje in omogočanje, da je ožji ali širši za pomoč pri vrtenju podatkov. Ta navodila lahko shranite in nato ponovite za prihodnji uvoz.
• Ravnanje z besedilnimi nizi: Kot pomemben napredek v primerjavi z Besedilo v stolpce funkcionalnost v Excelu, Get & Transform lahko hitro razčleni in loči kombinirane besedilne in številčne nize v ločene stolpce.
• Različni viri podatkov: s široko paleto vhodnih datotek je mogoče delati z različnimi viri, hkrati pa ohranjati dosledno in normalizirano kakovost izhoda.
• Prilagajanje s kodo: Jezik M je funkcionalna koda, ki se uporablja v orodju Pridobi in preoblikuj, za več prilagojenih zahtev pa je mogoče napisati poizvedbe po meri.

V tej dobi podatkovna jezera in petabajtnih baz podatkov, pretresljivo je, kako pogosto še vedno prejemam podatke v obliki datotek CSV, besedila in Excela. Medtem ko se sodobna analitika osredotoča na vrhunski napredek algoritmov strojnega učenja, je vsakodnevno trdo delo Analiza podatkov je še vedno ročni postopek iskanja, prevajanja in prepiranja različnih vrst podatkov.

Podatki za finančnega analitika pogosto prispejo kot Excelova preglednica, a prav tako pogosto gre za izpis podatkov v CSV ali poizvedbo v zbirko podatkov SQL. Podatki so včasih razporejeni v zmedeno postavitev ali nimajo vseh potrebnih komponent za analizo. Čas, porabljen za čiščenje teh podatkov, je za analitika dragocen čas, vendar je včasih ta naloga sprejeta kot nujno zlo, ki ga je treba tolerirati.

Kaj naredi Get & Transform?

Rešitev tega pogostega problema je pravzaprav zelo dostopna: Excel in Power BI imata celoten nabor orodij za preoblikovanje podatkov, ki jih pozna le malo uporabnikov, imenovanih Pridobite in preoblikujte (prej znan kot Power Query). Uporaba vgrajene funkcije za izvleček, preoblikovanje in nalaganje (ETL) omogoča finančnim analitikom, da se brez težav povežejo s svojimi viri podatkov in hitreje pridejo do vpogledov.

Ko povežemo podatke za nalaganje v Excel ali Power BI, moramo ponavadi opraviti nekaj sprememb s podatki. Nekaj ​​primerov manipulacije s podatki bi vključevalo:

• Odstranjevanje stolpcev,
• Filtriranje podatkov,
• Združevanje podatkov v skupine,
• Vrtenje / premikanje podatkov,
• Razdelitev nizov v podniz,
• Pridobivanje ključnih besed iz nizov,
• Dodajanje vrstic iz druge tabele in
• Združevanje dveh dimenzijskih tabel.

V spodnjem diagramu vidimo, da Get & Transform opravlja to dolgočasno vlogo predhodne obdelave podatkov, preden so naloženi.

Zakaj bi morali uporabiti Get & Transform?

Zakaj se je vredno naučiti uporabljati orodje Get & Transform? No, ko pogledam, za kaj sem osebno uporabljal to funkcionalnost, mi je ponudil voljen nabor orodij za:

• Nalaganje celotne mape besedilnih datotek v eno podatkovno tabelo
• Pretvorba izvoženih računovodskih datotek v prebavljivo postavitev
• Nalaganje milijonov prodajnih vrstic neposredno v Power Pivot
• Dnevne podatke združite v obvladljive mesečne rezultate, preden jih uvozite v Excel
• Spajanje podatkov iz druge tabele s povezovanjem ujemajočih se stolpcev

Ko prejmem nove podatke, jih bom pred nalaganjem v Power Pivot raziskal s pomočjo funkcije Get & Transform. To mi omogoča, da vidim, katere preobrazbe so morda potrebne, in hitro izvedem nekaj pivotov in združevanja podatkov za oblikovanje okvira za analizo. Na tej stopnji bom v mnogih primerih ugotovil, da potrebujem več podatkov ali da obstajajo težave s podatki. Z uporabo platforme, ki temelji na Excelu, lahko s svojim virom podatkov hitro poiščem te podatkovne nepravilnosti.

Na koncu bo odločitev, da ostanemo v Excelu ali analizo podatkov premaknili na drugo platformo, odvisna od občinstva ter ponovljivosti in distribucije analize. Če moje stranke uporabljajo samo Excel, bom za nalaganje podatkov skoraj vedno uporabil Get & Transform, Power Pivot za izvedbo analize in Excel za izdelavo vrtilnih tabel in grafikonov. Za stranko bo to videti brezhibno, saj je vse nameščeno v Excelu.

Če pa moja stranka:

1. Želi uporabiti drugo orodje za vizualizacijo,
2. Ima več uporabnikov, ki bodo osveževali podatke, oz
3. Potrebuje modele strojnega učenja,

Potem bom Get & Transform uporabil izključno za začetno raziskovanje podatkov, nato pa dvignil težko težo R .

Kako dostopati do funkcije Get & Transform v Excelu ali Power BI

V prejšnjih različicah Excela je bil Power Query dodatek, ki ga je bilo mogoče namestiti za pomoč pri funkcijah ETL. Vendar pa so v Excelu 2016 in Power BI ta orodja tesneje integrirana. V Excelu 2016 so do njih dostopni prek Podatki , nato pa Pridobite in preoblikujte podatke odsek.

V Power BI funkcionalnost obstaja na Domov v zavihku Zunanji podatki odsek.

V tem članku so moji primeri v programu Power BI, vendar je vmesnik skoraj enak Excelovemu. Poudaril bom razlike, ko se pojavijo, zato bi morala biti vadnica smiselna za obe vrsti uporabnikov.

1. Nalaganje datotek CSV

Za pomoč pri tej vadnici sem ustvaril nekaj primerov prodajnih podatkov za izmišljenega trgovca, ki prodaja zunanjo opremo in oblačila. V vsakem od teh primerov bodo podatki pripravljeni na različne načine, da se prikažejo realistične metode odlaganja podatkov.

Kot prvi primer bomo videli podatke, ki so predstavljeni kot velik izpis podatkov v datoteko CSV. Zapleteni dejavnik je, da so podatki predstavljeni z več stolpci, ki predstavljajo različne trgovine. V idealnem primeru bi radi podatke uvozili in preoblikovali v uporabnejšo postavitev.

Spodaj je posnetek zaslona, ​​kako izgleda surov CSV:

Zakaj bi to radi spremenili? Da izkoristite zmožnosti odnosov, ki so možne v teh aplikacijah. To igro bomo videli v nadaljevanju razprave.

Za zdaj predpostavimo, da moramo na podatke gledati kot na „ožjo in višjo“ strukturo, ne pa kot na „širšo in krajšo“. Prvi korak je nalaganje CSV; nato bomo podatke začeli »odpirati«.

Kot lahko vidite, je končna struktura podatkov ožja od začetnih podatkov in veliko daljša. Druga točka je, da s klikom na različna dejanja orodje na desni strani ustvari seznam uporabljenih korakov, uporabljenih za izdelavo poizvedbe. Pomembno je razumeti, da se to dogaja v ozadju, saj bo pozneje ponovno pregledano.

Get & Transform izgleda in se večinoma vede podobno kot Power BI in Excel. Vendar v Excelu po kliku Zapri in naloži , obstaja en dodaten poziv. Na spodnji sliki lahko preklapljamo med tem, ali želimo podatke naložiti v:

1. Tabela v Excelu,
2. Vrtilna tabela, ustvarjena glede na podatke,
3. Vrtilni grafikon, ustvarjen glede na podatke, ali
4. 'Ustvari samo povezavo.'

Poleg tega imamo tudi možnost, ali Te podatke dodajte v podatkovni model . Če potrdite to polje, se podatki naložijo v Power Pivot tabela. Če bomo podatke analizirali v programu Power Pivot, svetujem izbiro Ustvari samo povezavo in nato poskrbite, da Te podatke dodajte v podatkovni model izbrana možnost. Če so podatki znotraj omejitve vrstice Excel in raje analiziramo v Excelu, potem samo izberite Tabela .

V naslednjem posnetku bomo videli, da je razlog, zakaj smo podatke formatirali kot dolge in suhe, ta, da lahko analiziramo prodajo ne samo po trgovinah, temveč tudi po regijah in državah. Za izpolnitev te naloge bomo uvozili tabelo, ki bo vsako trgovino preslikala v regijo in državo. Spodaj bomo videli, da lahko hitro ustvarimo poročila, ki prikazujejo prodajo teh različnih skupin.

Lahko si predstavljate, kako je to vrsto zmožnosti za pretvorbo podatkov v Excelu ali Power BI mogoče močno uporabiti v vseh primerih, ko imamo dinamične skupine podatkov, na primer:

• Zbiranje dnevnih podatkov v tedne, mesece in četrtletja;
• Združevanje prodajnega osebja v oddelke in regije; ali
• Preslikava SKU-jev na vrste izdelkov.

Medtem ko ta članek obravnava CSV in druge Excelove datoteke, Get & Transform obravnava široko paleto podatkovnih vrst. Ko je poizvedba ustvarjena, jo je mogoče sčasoma osvežiti, ko se podatki spreminjajo.

2. Ravnanje z besedilnimi nizi

Da bi dokazal sposobnost Get & Transform za manipulacijo z nizi, sem ustvaril še en nabor podatkov, ki posnema besedilno datoteko, ki prikazuje računovodske transakcije iz glavne knjige podjetja (GL).

Ste opazili, kako sta številka in ime računa prikazana v istem nizu? V Power BI lahko brez težav razčlenimo številko računa in ime v ločena polja.

V tem videoposnetku lahko vidite, da je orodje po razdelitvi stolpca uganilo, da mora biti nova leva stran polja Račun številka, in ustvari korak »Spremenjen tip1«. Ker si na koncu želimo to polje kot niz, lahko nadaljujemo in korak ročno izbrišemo pod uporabljenimi koraki.

Nato vzamemo iste podatke in ustvarimo kontni načrt s preslikavami v kategorije računov.

Zakaj bi šli skozi vse te korake, da bi preslikali nekaj številk računov? Prava glavna knjiga je lahko na stotine ali celo tisoče računov. Ta poizvedba za hitro preslikavo, kot smo pokazali, bi se razširila na to raven brez dodatnega dela.

3. Delo z različnimi viri podatkov

Get & Transform podpira veliko različnih virov podatkov. Spodaj je nekaj primerov, čeprav ni izčrpen seznam:

Besedilna datoteka Excel Facebook Adobe Analytics Google Analytics Prodajno silo Azure Redshift Iskra SQL Server SAP HANA Teradata Google BigQuery

Osebno sem preizkusil le približno polovico povezav na zgornjem seznamu. Vsak od priključkov, ki sem jih uporabil, je bil dokaj trden; Od surovih podatkov sem prišel do spoznanj brez obremenjujočega dela. Enako pomembno je, da služi kot potrjevalnik med različnimi viri podatkov in zagotavlja, da imajo končni izhodi normalizirano raven nadzora kakovosti.

4. Prilagajanje kode z jezikom M

V ozadju Get & Transform generira kodo vsakič, ko kliknemo gumb v orodju ali naredimo izbiro. Spodaj je primer, kako bi dostopali do kode za poizvedbo za preslikavo računa, ki smo jo ustvarili:

Koda uporablja funkcijski jezik z imenom M , ki se samodejno generira za osnovne primere uporabe. Vendar pa lahko za bolj zapleteno premeščanje podatkov uredimo in napišemo svojo kodo. V večini primerov bom to kodo le kdaj malo spremenil. V bolj zapletenih transformacijah lahko večino kode napišem od začetka do stopnje začasne mize ali za bolj zapleteno izvajanje pridruži .

Omejitve Get & Transform

Excel poskuša doseči svoje meje, ko poskusite izvoziti več kot milijon vrstic. V primerih, ko sem z Get & Transform preoblikoval na milijone vrstic, je edini način, kako razstaviti nerazvrščene vrstice, dolgočasni vdori ali rešitve. Ugotovil sem tudi, da je poizvedbe Get & Transform lahko nestabilne za uporabo pri več uporabnikih, zlasti če uporabljate več virov podatkov in združevanja. V teh primerih bom vedno uporabil R za uvajanje dvojnih prepirov podatkov. Končno, Excel ni zasnovan za naprednejše modeliranje podatkov. Linearne regresije lahko izvedete precej hitro, poleg tega pa boste morali uporabiti strožjo platformo.

Po vsem tem sem ugotovil, da je Excel tisto, s čimer je večina mojih strank najbolj zadovoljna. Excel je še vedno najpomembnejše orodje v arzenalu finančnega analitika. Z vključitvijo funkcije Get & Transform postaneta Excel in Power BI še bolj zmogljiva s pomočjo različnih virov podatkov, ki jih lahko sprejmeta.

Razumevanje osnov

Kakšen je koncept ETL?

Izvleček, pretvorba in nalaganje je postopek prenosa podatkov iz različnih virov v centralizirano podatkovno skladišče.

Za kaj se uporablja Power BI?

Power BI je Microsoftov del programske opreme za poslovno analitiko. Zagotavlja globoke zmogljivosti za vizualizacijo podatkov in ustvarjanje avtomatiziranih poročil in nadzornih plošč.

Za kaj se uporablja podatkovno jezero?

Podatkovno jezero je eno skladišče za vse vire podatkov znotraj organizacije. To lahko vključuje strukturirane in nestrukturirane žepe podatkov, ki jih je na koncu mogoče črpati in obdelati, kadar koli je to potrebno.