Tabuľka dimenzií v textovom súbore: Riešenie Power Query

V tomto blogovom príspevku prejdem k problému č. 2 z prebiehajúceho Problému týždňa na stránke , kde sme diskutovali o tom, ako triediť chaotické údaje do čistej tabuľky dimenzií. Toto je druhá iterácia tejto novej iniciatívy, ktorú organizujeme v LuckyTemplates. Pre túto sériu som obzvlášť nadšený, pretože poskytuje každému šancu pravidelne trénovať. Celé video tohto návodu si môžete pozrieť v spodnej časti tohto blogu.

Prvú stredu v mesiaci sa koná výzva DAX a tretiu stredu výzva dopytu napájania .

Toto je skvelá príležitosť preskúmať, objaviť a naučiť sa nové veci o týchto jazykoch, ktoré musíte využiť v LuckyTemplates.

Na fóre LuckyTemplates nájdete kategóriu Problém týždňa.

Primárne zameranie je na proces a nie až tak na výsledok.

Ak sa vám podarí problém rozdeliť na menšie časti, budete môcť vyriešiť akýkoľvek problém, s ktorým sa stretnete.

Melissa de Korte

Brian predtým hovoril o technike nazývanej ladenie gumových kačíc . Ak vám to ušlo, určite si pozrite jeho video. Môže vám to pomôcť, keď sa pri týchto výzvach zaseknete.

Najprv sa pozrime na danú úlohu. Máme chaotický textový súbor, ktorý musíme transformovať na správnu tabuľku rozmerov.

Teraz, keď sa pozriem na dáta takto, väčšinou to bude všetko o čistení textu, odstraňovaní nechcených znakov, orezávaní, písaní veľkých písmen atď.

Musíme však tiež získať všetky riadky z týchto údajov zásobníka a premeniť ich na jeden riadok pre každú krajinu. Nazývam to zásobníkové dáta, pretože všetky názvy polí sa opakujú v jednom stĺpci pre každú krajinu.

Mám nejaké skúsenosti s Excelom a jedna z jeho najsilnejších funkcií je podľa mňa.

Obsah

• Kontingenčné tabuľky v Exceli
• Použitie Power Query na vytvorenie tabuľky dimenzií
• Vlastná funkcia čistenia textu
• Pivoting The Data
• Záver

Kontingenčné tabuľky v Exceli

Kontingenčné tabuľky mi umožňujú zobraziť údaje po segmentoch. V závislosti od toho, čo umiestnite do sekcie riadka, kontingenčná tabuľka zhustí každý výskyt tohto segmentu do jedného riadku.

Potom to môžete ešte viac rozdeliť presunutím polí do sekcie stĺpcov.

Vytvoril som príklad, ako to vyzerá. Tu máme takmer rovnaké údaje, aké sme mali v textovom súbore.

A na ďalšej stránke som vytvoril prázdnu kontingenčnú tabuľku.

Teraz som vám neukázal, že tu mám tri stĺpce namiesto dvoch, ktoré som vám už ukázal.

Pretiahnem svoj segment do riadkov.

Keďže stĺpec 1 mal názvy polí, presuniem ich do sekcie stĺpcov.

Stĺpec 2 obsahoval všetky hodnoty polí, takže ich presuniem do sekcie hodnôt.

Tu vidíme, že kontingenčné tabuľky nedokážu spracovať textové reťazce. Počíta ich, ale ukazuje, že pre každé pole máme jednu hodnotu.

Teraz sa pozrime na segment, ktorý som vytvoril. Vrátim sa teda k údajom a odkryjem svoj stĺpec. Môžete vidieť, že je to len indexové číslo, ktoré identifikuje každý zo samostatných blokov údajov, ktoré sú stále na sebe naskladané.

Takže pre mňa bude kľúčová transformácia kľúčom údajov, aby sa dostali späť do tohto tabuľkového formátu.

Na fóre som videl aj iné spôsoby, ako to riešiť. Existujú aj iné spôsoby, ako sa dostať k tabuľkovému formátu, okrem použitia pivotovej akcie a fungujú rovnako dobre. Ak vás to zaujíma, prejdite na fórum a začnite skúmať.

Použitie Power Query na vytvorenie tabuľky dimenzií

Pozrime sa na otázku napájania a preskúmame moje riešenie.

Osobne si myslím, že používateľské rozhranie robí skvelú prácu pri písaní väčšiny kódu M za nás. Takže mám tendenciu navrhovať svoje otázky pomocou používateľského rozhrania, ako sa len dá.

Keď dotaz vykoná to, čo som potreboval, prejdem do pokročilého editora a preskúmam kód M, aby som zistil, či ho môžem upraviť. Pozrime sa, ako to vyzerá.

Toto je moja základná skupina, ktorá je vytvorená pomocou používateľského rozhrania.

Môžete vidieť, že v použitých krokoch na pravej strane je veľa krokov.

Teraz to samo osebe nie je problém, ale len pri pohľade na tieto kroky môžete vidieť, že existuje veľa transformácií, ktoré možno zoskupiť.

Otvorme pokročilý editor.

Vidíme, že tento dotaz má 31 krokov.

K tomu istému dotazu, ktorý obsahuje 31 krokov, som pridal aj niekoľko komentárov, ale rozdelil som ho do sekcií.

Prvá vec, ktorú som urobil, bolo odstránenie kroku typu zmeny. Navrhujem vytvoriť vlastnú funkciu na vykonanie všetkých týchto krokov transformácie textu.

Je tu veľa komentárov, ale v tomto blogovom príspevku chcem zdôrazniť len dve veci. Prvou je vlastná funkcia na účely čistenia textu .

Druhým je kľúčový krok na premenu tohto typu údajov späť na správny tabuľkový formát .

Vlastná funkcia čistenia textu

Vráťme sa k počiatočným fázam zostavovania môjho dotazu, kde som mal všetky tieto zoskupené kroky na vyčistenie týchto textov: stĺpec 1 a zlúčený stĺpec.

Pridal som aj ďalší vlastný stĺpec. Jeho jediným účelom je vytvoriť vlastnú funkciu čistenia textu . Vyvolal som to na zlúčenom stĺpci.

Takto nemusím funkciu písať na jeden záťah, ale postupne ju postupne po jednotlivých krokoch budovať, pričom po kontrole výsledkov z predchádzajúceho kroku pridávam novú transformáciu.

Pozrime sa na M kód pre funkciu čistenia textu.

Ako vidíte, mám viacero krokov. Keď som vytváral túto textovú funkciu, prechádzal som tam a späť medzi dotazom, aby som sa pozrel na výsledky, aby som zistil, čo sa má zostaviť a čo ďalej opraviť.

S týmto výsledkom som vykonal všetky transformácie, ktoré som potreboval. Niektoré z funkcií M, ktoré som tu použil, boli poskytnuté používateľským rozhraním, keď som zostavil svoj úvodný dotaz, napríklad Text. Funkcia trimovania. Ostatné použité funkcie však neboli.

Ak ich nepoznáte, môžete si ich všetky vyhľadaťv príručke M receptúra ​​online. Toto je odkaz , kam musíte ísť.

Nájdete tu časť venovanú otázkam napájania a funkciám.

Ak rolujete nadol, nájdete sekciu o funkcii textu a každá sekcia začína prehľadom. V rámci mocenského dotazu a jazyka vzorca M je zoznam všetkých textových funkcií.

Ak hľadáte konkrétnu transformáciu, môžete ju vyhľadať priamo tu.

Pivoting The Data

Druhá časť, ktorú som chcel zdôrazniť pri vytváraní tejto tabuľky dimenzií, je pivotovanie samotných údajov. Pozrime sa bližšie aj na toto.

Začal som pridaním indexu. Aktualizoval som tento index, aby som správne segmentoval bloky údajov. Urobil som to tak, že som vrátil indexové číslo pre každý riadok, kde mal v stĺpci 1 text krajiny, a potom som túto hodnotu vyplnil.

Všetko, čo musíme urobiť, je pivotovať samotné údaje. Na karte transformácie nájdete kontingenčný stĺpec. Keď je vybratý stĺpec 1, kliknite na kontingenčný stĺpec.

Ako nový názov stĺpca sa použijú hodnoty stĺpca 1. Chce to tiež vedieť, kde sú hodnoty pre tieto názvy polí. Tie sú v našom zlúčenom stĺpci.

Ak si Excel dokáže poradiť s textovými hodnotami, Power Query to dokáže aj vďaka nastaveniu rozšírených možností. Všetko, čo musíme urobiť, je vybrať možnosť Neagregovať , aby bolo možné spracovať textové hodnoty.

Keď klikneme na OK, vidíme, že naše údaje boli pivotované.

Záver

Obrázok vyššie je konečným výsledkom dotazu. Dúfam, že sa vám páčilo, ako sme pretvorili chaotické údaje v poskytnutom textovom súbore a zmenili ich na čisté, ktoré je vhodné na analýzu.

Ak sa vám tento blogový príspevok páčil, prihláste sa na odber kanála LuckyTemplates, aby vám neušiel žiadny nový obsah.

Dúfam, že vás všetkých uvidíme v budúcich výzvach Problém týždňa.

Melissa