Modul Power Query: Ako zaobchádzať s nahromadenými údajmi

V tomto návode vám ukážem, ako môžete transformovať chaotické naskladané údaje do organizovanejšieho formátu. Existuje celkom jednoduchý spôsob, ako to urobiť, a je to ten, ktorý bude neuveriteľne užitočný a bude mať veľa rôznych aplikácií. Budeme používať modul Power Query. Celé video tohto návodu si môžete pozrieť v spodnej časti tohto blogu.

Moja žena prišla do kancelárie a povedala, že skopírovala veľa adries z pozvánky v kalendári a skončilo to prilepením v tomto formáte. Mala 28 e-mailových adries a mien, ktoré skončili v takom podivnom, skladanom formáte. Bol to zoznam mien s množstvom úvodzoviek, čiarok, nevyžiadanej pošty a e-mailových adries. Chcela vedieť, ako použiť Power Query, aby to prenieslo do lepšieho formátu.

Poďme teda do Power Query a uvidíme nespracované údaje. Ponechajme nespracované údaje nedotknuté a len na ne odkazujeme. Tento referenčný súbor budeme nazývať TestData .

Prvá vec, ktorú urobíme, je pridať index a na tieto účely je úplne jedno, či ide o index založený na nule alebo index založený na jednotke. Pre túto ukážku použijeme len index založený na nule. Je ľahké kliknúť bez toho, aby ste museli prechádzať ďalšími možnosťami.

Toto bude zahŕňať pivot, pretože pivot preberá riadky a mení ich na stĺpce. V tomto prípade chceme zobrať túto sadu riadkov a zmeniť ich na tri stĺpce – jeden pre krstné meno, jeden pre priezvisko a jeden pre e-mailovú adresu.

Budeme ich otáčať v stĺpcoch, ale musíme mať spôsob, ako pivot vedieť, ktorý prvok ide do ktorého stĺpca. Tu prichádza na rad Power Query Modulo.

Obsah

• Spracovanie naskladaných údajov pomocou modulu Power Query
• Záver

Spracovanie naskladaných údajov pomocou modulu Power Query

Modulo nám dáva zvyšok, keď delíme konkrétne číslo. Ak chcete získať prístup k modulu Modulo, prejdite na kartu Pridať stĺpec, potom nájdeme položku Štandardné, kliknite na šípku rozbaľovacej ponuky a prejdite nadol.

Keď klikneme na Modulo, zobrazí sa nám kontextové okno, ktoré sa pýta na požadovaný počet stĺpcov. V tomto prípade chceme tri stĺpce.

Ak vydelíme tri tromi, dostaneme jednotku so zvyškom nula. Tri ku štyrom dáva zvyšok jedna, tri ku piatim zvyšok 2 a potom tri ku šiestim nám dáva zvyšok nula. Takže vytvára tento pekný opakujúci sa vzor, ​​ktorý môžeme použiť pre náš pivot.

Ale predtým, ako to urobíme, poďme vyčistiť dáta. Môžeme to urobiť dvoma spôsobmi. Jedným z nich je, že môžeme jednoducho nahradiť hodnoty. Napríklad pre úvodzovky môžeme jednoducho vložiť citáciu a potom ju nahradiť ničím. Vezme a vyčistí tie úvodzovky.

Ďalším spôsobom, ako to urobiť, keď to môžeme všetci naraz vyčistiť a uložiť niektoré kroky, je použiť funkciu s názvom Text.Remove . Najprv pridáme vlastný stĺpec a potom uvedieme všetky hodnoty, ktorých sa chceme zbaviť.

Ako vidíte, všetko sa tým pekne a jednoducho vyčistí. A tak sa teraz môžeme zbaviť tohto počiatočného stĺpca a potom presunúť tento vlastný stĺpec.

Teraz môžeme urobiť náš pivot čisto. Vezmeme Modulo, prejdeme na Transform a potom Pivot Column a ideme sa otáčať na Custom, ale nechceme, aby sa agregoval. Takže vyberieme Neagregovať a klikneme na OK.

Práve ho nahrádzame týmito hodnotami v stĺpci. Potom nám vytvorí tieto tri stĺpce. Vidíme tam priezvisko, meno a e-mailovú adresu, ale nie je to presne požadovaný formát.

Aby sme to vyriešili, môžeme vziať tieto stĺpce a potom urobiť Fill Up .

A teraz máme tieto ďalšie riadky kvôli opakujúcemu sa vzoru Modulo.

Toho sa môžeme ľahko zbaviť odstránením striedavých riadkov. Môžeme to urobiť vo voľbe Odstrániť riadky.

Môžeme povedať, že prvý riadok na odstránenie sú dva. Potom odstráňte druhý a tretí riadok a potom si ponechajte štvrtý a urobte to ako opakujúci sa vzor.

Teraz môžete vidieť, že sme pekne vyčistili všetky správne údaje a stále máme 28 pôvodných e-mailových adries.

Teraz sa môžeme zbaviť stĺpca Index a podľa toho premenovať hlavičky stĺpcov.

Záver

Môžete vidieť, že pomocou triku Modulo a potom otočením údajov a trochou čistenia je to celkom jednoduché. Keď to pochopíte, bude to trvať asi len minútu, kým to celé urobíte.

Dúfajme, že vám to prinieslo niekoľko ďalších nápadov a nástrojov. Môžete si tiež pozrieť môj ďalší tutoriál . V tomto návode sa ďalej pozrieme na niektoré veci, ktoré sme prebrali v tomto návode, a tiež sa pozrieme na to, ako môžeme tento proces automatizovať opakovateľným spôsobom pomocou vlastných funkcií. Ukážem vám niekoľko skvelých trikov na ladenie vašich vlastných funkcií.

Dúfam, že vám to pomohlo. Celé video si môžete pozrieť nižšie a ďalší súvisiaci obsah nájdete na nižšie uvedených odkazoch.

Všetko najlepšie!