Mérettáblázat szöveges fájlban: Power Query megoldás

Ebben a blogbejegyzésben áttérek a 2. problémára a Heti Probléma című folyóiratban, ahol megvitattuk, hogyan lehet rendezni a rendetlen adatokat egy tiszta dimenziótáblázatba. Ez a második iterációja ennek az új kezdeményezésnek, amelyet a LuckyTemplates-on adunk otthont. Kifejezetten rajongok ezért a sorozatért, mert mindenkinek lehetősége nyílik arra, hogy rendszeresen többet gyakoroljon. Az oktatóanyag teljes videóját a blog alján tekintheti meg.

Minden hónap első szerdáján van egy DAX kihívás , a harmadik szerdán pedig egy power query kihívás .

Ez egy nagyszerű lehetőség arra, hogy felfedezze, felfedezze és új dolgokat tanuljon meg ezekről a nyelvekről, amelyeket a LuckyTemplatesben kell hasznosítania.

A LuckyTemplates fórumon találsz egy kategóriát a hét problémájáról.

Az elsődleges hangsúly a folyamaton van, és nem annyira az eredményen.

Ha sikerül kisebb darabokra bontania egy problémát, akkor minden felmerülő problémát meg tud majd oldani.

Melissa de Korte

Brian korábban a gumikacsa hibakeresésnek nevezett technikáról beszélt . Ha lemaradtál róla, mindenképp nézd meg a videóját. Segíthet, ha elakad ezeken a kihívásokon.

Először is vizsgáljuk meg az adott feladatot. Van egy rendetlen szövegfájlunk, amelyet megfelelő mérettáblázattá kell átalakítanunk.

Most, ha az ilyen adatokat nézem, a legtöbb esetben a szövegtisztításról, a nem kívánt karakterek eltávolításáról, a vágásokról, a szavak nagybetűs írásáról és így továbbról lesz szó.

De ebből a veremadatokból is le kell kérnünk az összes sort , és minden ország számára egyetlen sorba kell alakítanunk. Azért nevezem veremadatoknak, mert az összes mezőnév megismétlődik egyetlen oszlopban minden országban.

Van némi tapasztalatom az Excelben, és véleményem szerint az egyik leghatékonyabb funkciója az.

Tartalomjegyzék

• Pivot táblák Excelben
• Power Query használata dimenziótábla létrehozásához
• Egyedi szövegtisztító funkció
• Az adatok elforgatása
• Következtetés

Pivot táblák Excelben

A kimutatások segítségével szegmensenként tekinthetem meg az adatokat. Attól függően, hogy mit helyez el a sorszakaszban, a kimutatástáblázat a szegmens minden előfordulását egyetlen sorba tömöríti.

Ezután még tovább oszthatja a mezőket az oszlopszakaszba húzva.

Létrehoztam egy példát arra, hogy ez hogyan néz ki. Itt nagyjából ugyanazok az adatok vannak, mint a szövegfájlban.

A következő oldalon pedig létrehoztam egy üres pivot táblát.

Amit most nem mutattam meg, az az, hogy három oszlop van itt a kettő helyett, amit már megmutattam.

A szegmensemet áthúzom a sorok közé.

Mivel az 1. oszlopban voltak a mezőnevek, áthúzom az oszlopszakaszba.

A 2. oszlopban az összes mezőérték szerepelt, ezért áthúzom az érték szakaszba.

Itt láthatjuk, hogy a pivot táblák nem tudják kezelni a szöveges karakterláncokat. Számolja őket, de azt mutatja, hogy minden mezőhöz egyetlen értékünk van.

Most pedig vessünk egy pillantást az általam létrehozott szegmensre. Tehát visszatérek az Adatokhoz, és felfedem az oszlopomat. Láthatja, hogy ez csak egy indexszám, amely azonosítja az egyes adatblokkokat, amelyek még mindig egymásra vannak rakva.

Tehát számomra a kulcstranszformáció az adatok forgópontja lesz, hogy visszakerüljenek a táblázatos formátumba.

A fórumon más módszereket is láttam ennek kezelésére. Vannak más módszerek is a táblázatos formátum elérésére a pivot műveleten kívül, és ezek ugyanolyan jól működnek. Ha érdekel, látogass el a fórumra, és kezdd el a felfedezést.

Power Query használata dimenziótábla létrehozásához

Nézzük át a power query-t, és tekintsük át a megoldásomat.

Személy szerint úgy gondolom, hogy a felhasználói felület nagyszerű munkát végez az M-kód zömének megírásában. Így a lekérdezéseimet a felhasználói felület segítségével szoktam megtervezni, amennyire csak tudom.

Miután a lekérdezés megtette, amire szükségem volt, belépek a speciális szerkesztőbe, és megvizsgálom az M-kódot, hátha módosítom. Lássuk, hogy néz ki.

Ez az én alapcsoportom, amely a felhasználói felület segítségével épül fel.

Láthatja, hogy a jobb oldalon lévő alkalmazott lépésekben sok lépés található.

Ez önmagában nem probléma, de ha ezeket a lépéseket nézzük, láthatjuk, hogy nagyon sok átalakítást lehet csoportosítani.

Nyissuk meg a speciális szerkesztőt.

Látjuk, hogy ez a lekérdezés 31 lépésből áll.

Hozzáfűztem néhány megjegyzést is ehhez a 31 lépést tartalmazó lekérdezéshez, de szakaszokra bontottam.

Az első dolgom az volt, hogy eltávolítottam a változástípus lépést. Azt javaslom, hogy hozzon létre egy egyéni függvényt az összes szövegátalakítási lépés végrehajtásához.

Nagyon sok hozzászólás van itt, de ebben a blogbejegyzésben csak két dolgot szeretnék kiemelni. Az első a szövegtisztítási célú egyéni funkció .

A második a pivot lépés az ilyen típusú adatok megfelelő táblázatos formátummá alakításához .

Egyedi szövegtisztító funkció

Térjünk vissza a lekérdezés felépítésének korai szakaszához, ahol megvolt az összes csoportosított lépés a szövegek megtisztításához: az 1. oszlop és az egyesített oszlop.

Hozzáadtam egy további egyéni oszlopot is. Ennek egyetlen célja az egyéni szövegtisztító funkcióm kiépítése . Ezt hívtam meg az egyesített oszlopban.

Így nem kell egy lépésben megírnom a függvényt, hanem fokozatosan, lépésenként felépíteni, az előző lépés eredményeinek áttekintése után hozzáadva egy új transzformációt.

Nézzük meg a szövegtisztító funkció M kódját.

Amint látja, több lépésem van. Amikor ezt a szöveges függvényt építettem, oda-vissza jártam a lekérdezések között, hogy megnézzem az eredményeket, hogy lássam, mit kell építeni és mit kell javítani.

Ezzel az eredménnyel elvégeztem az összes szükséges átalakítást. Az itt használt M függvények egy részét a felhasználói felület biztosította a kezdeti lekérdezés létrehozásakor, például a Szöveg. Trim funkció. A többi használt funkció azonban nem.

Ha nem ismeri őket, megkeresheti az összesetaz M formula útmutatóban online. Ez az a link , ahová menned kell.

Talál egy szakaszt a teljesítménylekérdezésnek és a funkcióknak.

Ha lefelé görget, megtalálja a szövegfunkciókra vonatkozó részt, és minden szakasz áttekintéssel kezdődik. Van egy lista az összes szöveges függvényről a power query és a formula M nyelven belül.

Ha konkrét átalakítást keres, itt megtekintheti.

Az adatok elforgatása

A második rész, amelyet ennek a dimenziós táblázatnak a létrehozása során kiemelten szerettem volna kiemelni, magának az adatoknak a forgatása. Nézzük meg ezt is közelebbről.

Egy index hozzáadásával kezdtem. Frissítettem az indexet, hogy megfelelően szegmentáljam az adatblokkokat. Ezt úgy tettem meg, hogy minden olyan sorhoz visszaadtam az indexszámot, ahol az 1. oszlopban szerepelt az ország szövege, majd kitöltöttem ezt az értéket.

Mindössze magát az adatokat kell elforgatnunk. Az átalakítás lapon találja a Pivot oszlopot. Az 1. oszlop kiválasztása után kattintson a Pivot Column elemre.

Új oszlopnévként az 1. oszlop értékeit fogja használni. Azt is tudni szeretné, hogy hol vannak a mezőnevek értékei. Ezek az egyesített rovatunkban találhatók.

Ha az Excel képes kezelni a szöveges értékeket, a Power Query is képes a speciális beállítások miatt. Csak annyit kell tennünk, hogy a Don't Aggregate opciót választjuk , hogy képes legyen kezelni a szöveges értékeket.

Ha rákattintunk az OK gombra, láthatjuk, hogy adataink el lettek forgatva.

Következtetés

A fenti kép a végeredmény lekérdezése. Remélem, élvezte, ahogy a mellékelt szöveges fájlban lévő rendetlen adatokat átalakítottuk, és elemzésre alkalmas tisztaságúvá alakítottuk.

Ha tetszett ez a blogbejegyzés, iratkozz fel a LuckyTemplates csatornára, hogy ne maradj le egyetlen új tartalomról sem.

Remélem, találkozunk a jövőbeli A hét problémája kihívásaiban.

Méhfű