Delimitované dáta – extrahovanie záznamov pomocou LuckyTemplates

Ukážem vám, ako extrahovať záznamy zo zlúčených oddelených údajov vo viacerých stĺpcoch . Tým sa vaše údaje prevedú do formátu, ktorý je vhodnejší na analýzu.

Existuje niekoľko spôsobov, ako to urobiť. Ale v tomto návode sa zameriam na riešenie navrhnuté Ankitom, ktorý je súčasťou našej komunity LuckyTemplates. Celé video tohto návodu si môžete pozrieť v spodnej časti tohto blogu.

Obsah

• Pomocou funkcie Text.Split
• Pomocou List.Zip
• Čistenie dát
• Záver

Pomocou funkcie Text.Split

Pre toto riešenie použijemextrahovať záznamy z delimitovaných údajov.

Nižšie je uvedená tabuľka Príležitosti v rámci Power Query .

Ako vidíte, existuje viacero hodnôt spojených do jedného záznamu. V 2. riadku sú napríklad zobrazené 4 rôzne hodnoty. Tieto 4 hodnoty sú zlúčené v stĺpcoch Konkurenti a Konkurenti .

Prvá vec, ktorú urobím, je použiť Text.Split .

Text.Split vráti zoznam po rozdelení textovej hodnoty na základe špecifického oddeľovača.

Ak chcete začať používať Text.Split , skopírujem oddeľovač použitý v tejto tabuľke.

Všimnete si, že keď kliknem na túto položku v tabuľke, hodnota záznamu sa zobrazí v dolnej časti. Odtiaľ len skopírujem oddeľovač.

Keď to urobím, pridám vlastný stĺpec.

Meno zatiaľ meniť nemusím. Potrebujem len urobiť Text.Split v časti Custom Column Formula .

Tento vzorec potrebuje textovú hodnotu. Takže si vyberiem stĺpec Konkurenti na pravej table a automaticky sa pridá do vzorca.

Potrebujem aj oddeľovač zadaný ako text. Takže do úvodzoviek vložím oddeľovač, ktorý som predtým skopíroval. Potom pridám zátvorku.

Keď stlačím OK, skončím s objektom zoznamu.

Pri pohľade na prvý zoznam sa v stĺpcoch Konkurenti a Sumy zobrazuje iba jeden záznam. Ak teda kliknem na prázdne miesto vedľa objektu zoznamu v stĺpci Vlastné, zobrazí sa v spodnej časti aj jedna hodnota.

Pre druhý záznam mám zoznam, ktorý obsahuje štyri hodnoty. To zodpovedá 4 hodnotám zobrazeným aj v stĺpcoch Konkurenti a Sumy .

Pomocou List.Zip

Teraz, keď som rozdelil zoznam, ďalším krokom je pridanie zodpovedajúcej hodnoty zo stĺpca Sumy konkurentov. Idem na to List.Zip .

Predstavte si List.Zip ako skutočný zips. Berie zoznam zoznamov a kombinuje položky.

Pri pohľade na príklad nižšie si predstavte prvý zoznam (1 a 2) ako zelenú stopu na zipse.

Potom si predstavte 3 a 4 ako červenú stopu.

Keď sa použije List.Zip , v strede sa spoja. Takže 1 z 1. skupiny sa spojí s 3 z 2. skupiny. To isté sa deje pre 2 a 4. Je to vidieť na výstupe, kde sú dve nové skupiny, ktoré sa vytvorili.

Môžu nastať situácie, keď budú mať príslušné zoznamy rôznu dĺžku . To znamená, že ak sa zoznamy skombinujú, vrátia sa chýbajúce údaje . Na vytvorenie presných párov napriek nedostatku údajov sa pridá nulová hodnota .

Keď sa vrátim k tabuľke Príležitosti , pridám ďalší vlastný stĺpec.

Tentokrát do vzorca pridám stĺpec Sumy konkurentov .

Chystám sa pridať aj List.Zip .

Kontrola nižšie ukazuje, že neboli zistené žiadne chyby.

Ale keď kliknem na OK a pozriem sa na vytvorený nový stĺpec, stále sa mi zobrazuje chyba.

Kliknutím na biele miesto vedľa chyby zobrazím podrobnosti. Ukazuje to, že dostávam chybu, pretože som vrátil nejaké argumenty okolo zoznamov.

Pohľad na vzorec ukazuje, že táto syntax, v ktorej sa odkazoval na stĺpec Konkurenti, vrátila zoznam.

Ale syntax pre stĺpce Competitor Amounts vrátila aj zoznam.

Pripomínajúc si, čo robí List.Zip, vezme jeden zoznam zoznamov a skombinuje ich .

Keď vidím, že sa zobrazujú dva zoznamy, potrebujem inicializátor zoznamov, aby sa tieto zoznamy spojili . Preto pridávam tieto kučeravé zátvorky.

Akonáhle pridám uzatváraciu zloženú zátvorku na koniec, stačí stlačiť enter. To mi poskytne objekt zoznamu.

Teraz, keď kliknem na stranu 1. záznamu, zobrazí sa jeden objekt zoznamu. To je správne, keď vidíme, že v časti Konkurentské sumy je tam skutočne jedna hodnota.

Ak urobím to isté s druhým záznamom, zobrazí sa objekt zoznamu obsahujúci 4 zoznamy. To sa tiež zhoduje s počtom hodnôt v oddelených údajoch v časti Konkurentské sumy.

Na rozbalenie údajov do nových riadkov použijem bočné šípky napravo od nadpisu stĺpca Vlastné.

Venujte pozornosť druhému záznamu, keď sa rozbalím do nových riadkov.

Uvidíte, že všetky hodnoty z tohto záznamu sa rozšíria nadol.

A keď kliknem na stranu na biele miesto, vidím obsah tohto vnoreného zoznamu. Keďže 1. záznam má iba jednu hodnotu pre každý stĺpec, zobrazujem rovnaké údaje aj v spodnej časti.

Ale keď kliknem na 2. záznam, uvidíte, že zobrazujem iba 1. zo 4 hodnôt uvedených v mojich ohraničených údajoch.

Na treťom zázname zobrazujem kombináciu druhých hodnôt.

Takže opäť použijem šípky do strany. Ale tentoraz vyberiem hodnoty z tohto zoznamu.

Dám tomu vlastný oddeľovač.

Ako oddeľovač použijem dvojitú rúrku. Tým by sa mal objasniť rozdiel medzi hodnotami vo vymedzených údajoch.

Po stlačení tlačidla OK sa hodnoty spoja priamo do stĺpca Vlastné.

Čistenie dát

Stĺpce Konkurent a Sumy konkurenta už nepotrebujem, takže tieto stĺpce odstránim.

Čo sa týka stĺpca Vlastné, rozdelím ho.

Na karte transformácie vyberiem položku Rozdeliť stĺpec. Rozdelím to oddeľovačom.

V okne dám ako základ dvojitú rúru.

Po stlačení tlačidla OK sa údaje rozdelia do samostatných stĺpcov.

Tieto nové stĺpce len premenujem, aby bolo ľahšie zistiť, o čom sú údaje. Opäť to nazvem stĺpec Konkurent.

Potom nazvem druhý stĺpec stĺpec Čiastka.

Je zrejmé, že stĺpec Čiastka obsahuje čísla. Ak sa však pozriete na ikonu predstavujúcu typ údajov, ukazuje, že namiesto čísel mám text. Tak to začnem opravovať.

Žijem v Európe, takže ako oddeľovač tisícok namiesto čiarky používame bodku. Na vyčistenie použijem Nahradiť hodnoty.

V okne stačí vložiť čiarku do položky Value To Find a bodku do položky Nahradiť čím.

Keď stlačím tlačidlo OK, všetky čiarky budú teraz zobrazovať bodky.

Ďalšia vec, ktorú urobím, je odstránenie znaku dolára pred hodnotami. Opäť použijem nástroj Nahradiť hodnoty. Tentokrát nechám priestor pre Nahradiť s prázdnou.

Po stlačení tlačidla OK sa v hodnotách zobrazia iba čísla bez meny.

Keď sa pozriem do môjho stĺpca, zobrazí sa tu aj záznam s dvojitou pomlčkou. Keďže by to mohlo vrátiť možnú chybu, zbavím sa tej dvojitej pomlčky.

Dvojčiarku vymením za prázdnu.

A keď stlačím OK, mám správny formát pre všetky položky v stĺpci Čiastka.

Teraz môžem kliknúť na ikonu pre typ údajov a vybrať celé číslo.

Záver

Ako som už spomenul, hodnoty môžete z údajov s oddeľovačmi extrahovať rôznymi spôsobmi. Ale pre mňa je tento prístup jedným z najjednoduchších a najrýchlejších spôsobov, ako to urobiť.

Keďže nápad na toto riešenie som dostal od jedného z členov spolkomunity, to tiež ukazuje, aké dôležité jeje. Každý problém môžete skutočne vidieť z mnohých rôznych uhlov pohľadu. Odtiaľ si môžete vybrať riešenie, o ktorom si myslíte, že by pre vás fungovalo najlepšie.

Všetko najlepšie,

Melissa

***** Učíte sa LuckyTemplates? *****