Microsoft Power Query oktatóanyag a vegyes, rögzített oszlopszélességgel kapcsolatos problémák megoldásáról

Mudassir: Ma egy nagyon érdekes problémával kell dolgoznunk. Ezzel a fájllal az a probléma, hogy oszlopokkal elválasztva van rögzítve, és nem tudom, hogyan lehet ezt megoldani a Microsoft Power Query használatával. Az oktatóanyag teljes videóját a blog alján tekintheti meg.

Először is, nem volt könnyű dinamikusan törölni az oszlopokat. Másodszor, ebben a jelentésben van egy táblázatunk eltérő oszlopszélességgel, majd egy másik tábla eltérő oszlopszélességgel.

Tehát ha dinamikusan alkalmazok egy határolót felül, nem tudnám szépen lekérni az adatokat. Mindezeket az adatokat a második táblázatból, a termékszámaimat pedig az első táblázatból szeretném lekérni. Azt is szeretném, hogy a munkaszám minden tábla sorában szerepeljen.

Megpróbáltam egyedül megoldani, de mivel ennek valami köze van a power query-hez, segítségre volt szükségem Melissától. Azt hittem, hogy legalább két napba telik, de azonnal sikerült megoldást találnia.

Melissa megmutatja nekünk, hogyan oldotta meg ezt a bonyolult problémát. Azt hiszem, a legtöbb ember foglalkozni fog az ilyen jellegű problémákkal, és keresi a megoldási módokat.

Melissa: Az első tipp az, hogy ha rögzített hosszúságú fájlt néz, lépjen a Nézet fülre , és kapcsolja be a Monospaced beállítást.

Láthatjuk, hogy ez egy fix hosszúságú betűtípus. Láthatjuk a fejléceket, a kezdőtáblákat és az altáblázatokat is. Ezek azok a részek, amelyek érdekelnek minket, és ezekből szeretnénk kivonni.

Ezenkívül győződjön meg arról, hogy a képletsáv engedélyezve van. Mindig jó dolog, ha látható a képernyőn, mivel gyakran fogjuk használni, hogy kisebb módosításokat hajtsunk végre a bemeneten.

Létrehoztam egy paramétert a fájl helyéhez, ahol a CSV-fájlt tároltam. Ezt állomásozófájlként hoztam be, és a fájl helyparaméteremben aláírtam. Aztán létrehoztam egy referenciát, és ebből a hivatkozásból fogok dolgozni. Tehát ezt vizsgáljuk most a Microsoft power queryben.

Tartalomjegyzék

• Index oszlop hozzáadása
• Pufferlista létrehozása a Microsoft Power Queryben
• Fejlécek szétválasztása a sorokból
• A nullák és üresek eltávolítása a Microsoft Power Query programból
• A szakaszok felosztása
• A szöveges karakterláncok kivágása a Microsoft Power Queryben
• Következtetés

Index oszlop hozzáadása

Általában, amikor elkezdek dolgozni egy ilyen fájlon, ismernem kell az ügyfél követelményeit. Megkérdezem, mire van szüksége az ügyfélnek, és mire kell figyelnie.

Ebben az esetben a cikkszámot és a munkaszámot szeretnénk a fejlécekből, majd az összes részletet, amely az adott fejléchez tartozik.

Szükségünk lesz egy kulcsra, hogy összehozzuk ezeket a dolgokat. De ha nincs kulcs, akkor adok hozzá egy . Rákattintok a mini táblázat ikonjára, kiválasztom az Index oszlop hozzáadása , majd hozzáadom a 0-tól lehetőséget .

Később egy logikát fogok használni a listáknál, amelyek 0-alapú indexet tartalmaznak. Ha az index nulláról indul, valójában azt jelenti, hogy ugyanarra a sorra hivatkozhat. Ellenkező esetben le kell vonnia az 1-et, hogy elérje a 0 alapú pozíciót.

Ezután meg kell találnunk azt a pozíciót, ahol a fejléceink vannak, amit nagyon könnyen megtehetünk, mivel ezek a fejlécek folyamatosan ismétlődnek az egész fájlban.

Kezdésként másoljuk át ezt az értéket:

Adjon hozzá egy új üres lekérdezést, illessze be, és hívja ezt a fejlécazonosítót .

Ugyanezt fogom végrehajtani az altáblázatoknál is. Kimásolom ezt a szöveges karakterláncot, létrehozok egy másik üres lekérdezést, és beillesztem az értéket. Ezt a karakterláncot fogjuk használni, amikor részletes sorokat keresünk.

Ha ez a folyamat valamilyen módon megváltoztatja a táblák fejlécét, akkor csak az egyik szöveges karakterláncot kell megváltoztatnom, és a fájl újra működni fog.

Nem igazán kell belemerülnöm az M kódba, hogy megkeressem azt a karakterláncot, amelyet keresünk. Ezt csak paraméterként használhatjuk.

Engedélyezzük ennek a két lekérdezésnek a betöltését.

Pufferlista létrehozása a Microsoft Power Queryben

Az első dolgom az, hogy az 1. oszlopot listává alakítom úgy, hogy egyszer hivatkozom és betöltöm a memóriába. Így nem kell ismételten felhívnom a fájlt.

Megnyitom a speciális szerkesztőt, és egészen a tetejére teszem. Amikor a felhasználói felületet használja a kód felépítéséhez, az az előző lépésre fog hivatkozni.

Ha pufferlépést helyez el bárhol máshol a kódban, és később módosítani szeretne, az segít a manuálisan létrehozandó lépés módosításában.

Meghívom ezt a pufferlistát , és hivatkozom az 1. oszlopra. A memóriába való betöltéshez hozzáadok egy List.Buffer lépést.

Itt van az én változóm a tetején. Újra és újra hivatkozhatok rá.

Az első dolog, amit meg akarok határozni, hogy hol kezdődnek a fejléceim, mert szükségem van egy kulcsra, hogy megtartsam ezeket a fejlécrészeket, és egyetlen értéket kapjak az összes sorhoz. Ehhez hozzáadok egy egyéni oszlopot, és fejlécnek hívom .

Leírom, hogy ha az 1. oszlop megegyezik a fejlécazonosítónkkal, akkor azt szeretném, hogy az indexszámom null legyen.

Ennek eredményeként megtalálta a szöveget, és 5-öt és 23-at adott vissza.

Szükségem van erre az értékre az összes sorban, ezért ki kell töltenem. A kitöltéshez kattintson a jobb gombbal, de használhat nagyon egyszerű szintaxist is, és hozzáadhatja a képletsorhoz.

Ebben az esetben hozzáadtam a Table.FillDown-t és a szöveges sztringben jeleztem, hogy melyik oszlopot szeretnénk kitölteni (Header).

Most az összes sornál kitöltöttük. Az összes fejlécszakaszhoz és minden sorszakaszhoz van kulcsunk, mert mindegyik osztja ezt az értéket.

Fejlécek szétválasztása a sorokból

A következő lépés a fejlécek szétválasztása a sorokból. Hozzáadom még egy egyéni oszlopot, és Temp . Ezúttal valami kidolgozottabbat fogunk csinálni, és kihasználjuk a korábban létrehozott pufferlistát .

Néhány lista funkciót fogunk használni, hogy megnézzük az egyes pozíciókat, és megtudjuk, van-e egyezés az indexnek.

Egy if utasítással kezdem , és a List.Contains használatával keresek egy adott pozíciót a BufferListben , és hivatkozom a HeaderID lekérdezésre .

Meg akarjuk találni a fájl teljes hosszában, majd vissza szeretnénk adni az elem pozícióját a listán belül. Ha egyezik az indexszel, akkor az adott sorhoz van egyezésünk.

Ezután egy értéket szeretnék visszaadni a fejléc azonosításához. Ebben az esetben csak egy H-t adok vissza. Másolom a szintaxist, hogy ne kelljen újra írnom.

A sorszakaszt is azonosítanunk kell. Ha a lista nem a HeaderID-t , hanem a DetailID-t tartalmazza , akkor egy sorszakaszban vagyunk.

Ha az 1. oszlop egy üres karakterlánc, akkor azt szeretném, hogy üresen maradjon . Ha nem ez a helyzet, akkor azt szeretném, hogy nulla legyen .

Ez az oppozíció megkapta a fejlécet, és H-t adott vissza, majd talált egy részletes sort, és egy R-t adott vissza. Ezután 0-t adott vissza az adott sorszakaszon belül megosztott összes elemre.

Ezek az üres vagy nullák fontosak, mert lehetővé teszik a kitöltést. A kitöltés nem fog áthaladni az üres cellák között, így később törölhetjük őket.

Ezt a képletsorban tesszük meg, és ismét használjuk a Table.FillDown parancsot . Egy listát akar az oszlopnévvel, ami a Temp oszlopunk.

Most az egész oszlopban megismételjük a H és R értékeket, ami azt jelenti, hogy ténylegesen szétválaszthatjuk a fejléceket a részletes szakaszokból.

A felhasználói felületről is kitöltheti, ha nem akarja megírni a kódot. Egyszerűen kattintson a jobb gombbal, és válassza a Kitöltés , majd a Le lehetőséget .

A nullák és üresek eltávolítása a Microsoft Power Query programból

Most, hogy megvan ez a jogunk, megszüntethetjük azokat a dolgokat, amelyekre nincs szükségünk. Minden, ami nulla vagy üres, azok a sorok, amelyekre nincs szükségünk, és el kell távolítani. Ezeket szűréssel kiküszöbölhetjük.

A szakaszok felosztása

Miután eltávolítottuk ezeket az üres helyeket és nullákat, minden marad, amire szükségünk van. Ezen a ponton már csak feloszthatjuk a részeket. Fókuszálhatunk a fejlécsorokra, és kijelölhetjük őket, mert külön térközzel rendelkeznek az összes részletsortól (amelynek külön térköze is van).

A képletsorhoz hozzáadok egy új lépést, amely lehetővé teszi egy másik szűrő létrehozását ugyanabban az oszlopban. Ebben az esetben csak az összes fejlécet megtartom.

Most itt van az összes fejlécesor.

Kiválaszthatom az 1. oszlopot , léphetek a képletsorba, kiválaszthatom az Oszlop felosztása lehetőséget , majd pozíciók szerint felosztom.

Ezután a power query magával találja ki ezt. Néhány pozíciót javasol. Kattintson az OK gombra a pozíciók elfogadásához.

A fejlécekből csak a cikk és a munkaszám érdekel minket .

Az itteni képletsávon belül átnevezhetem azokat az Item és Job # karakterekkel . Ez megkímél egy másik oszlop átnevezési lépéstől.

E lépés után nincs más dolgom, mint kiválasztani az Elemet , kiválasztani a Munkaszámot , és természetesen a fejléc kulcsunkat . Ezután eltávolítom az összes többi oszlopot, mert már nincs szükségem rájuk.

Ez lesz az eredmény. Még mindig meg kell tisztítanunk az értékeket, és el kell távolítanunk a szöveges elemet és a kötőjeleket. Csak a kettő közötti értékekre vágyunk.

Tehát megnyitjuk ezt, és töröljük a kötőjelek és elemek kijelölését.

Most az összes fejléc kész.

Ugyanezt a folyamatot kell végrehajtanunk a DetailID esetében is. Át kell neveznem ezeket a lépéseket, hogy később könnyebb legyen visszatérnem hozzá.

Visszatérünk az elkezdett lekérdezéshez. Az Alkalmazott lépések panelen a Szűrt sorokkal kezdtük.

Ezt kimásolom és hozzáadom a szűrőmhöz. Ezúttal nem a H-t, hanem az R-t választom.

Ezután kiválasztom az 1. oszlopot, megyek a Felosztás oszlopra, pozíciók szerint felosztom, majd a power query kitalálja.

Ezt sugallja a power query. Tegyünk egy próbát.

Ez valójában nagyon jól néz ki. Még az összes sor is tökéletesen felosztott. Természetesen sok a hely, mert megvolt az a behúzás.

A szöveges karakterláncok kivágása a Microsoft Power Queryben

Kijelölöm az első oszlopot, majd lenyomom a Le + Shift billentyűket a kiválasztáshoz az 1.10 oszlopig. Lépjen az Átalakítás elemre , válassza a Formátum , majd a Vágás lehetőséget . A vágás csak a sor elől vagy végén lévő felesleges szóközöket távolítja el, a közöttük lévő helyet nem.

Ezután egyszerűen előléptethetjük a fejléceket, így nem kell az összes fejlécet vagy címet begépelnem ezekhez az oszlopokhoz. A felosztási lépésben két oszlopot átneveztem. Most persze 10 oszlopnál ez egy kicsit zavar.

Ezektől a felesleges értékektől is meg kell szabadulnunk. Mivel vannak összegeink, az utolsó három oszlop egyikét kell használnom, mert ezek az egyetlen sorok, amelyekben valahol a kettő között vannak a további értékek. Ezután megszüntetjük az üres helyek, kötőjelek és szövegek kijelölését.

Ezután eltávolítom a felesleges oszlopokat, így csak egy táblázat marad, amely csak a fejléceket és csak a részleteket tartalmazza. Szükségünk van egy kulcsra, hogy újra összehozzuk ezeket a részeket.

Ehhez használhatunk egy ön-egyesítést, hogy a táblát összevonhassuk önmagával, hogy visszaállítsuk az információkat. A Kezdőlap lapon válassza az Egyesítés lehetőséget , majd válassza az 5. oszlopot és ugyanazt a lekérdezést.

Az AllDetails helyett az AllHeaders-t szeretném kiinduló táblázatként, amellyel egyesíteni szeretnék.

Ez visszahozta az összes információt a fejléctáblázatból, egyetlen sorral minden egyes tételhez és minden feladatszámhoz.

Kulcs segítségével egyesítettük a részletsorokat. Ha itt lenyomom az oldalt a fehér mezőben, látni fogjuk az 5. fejléchez tartozó összes sor előnézetét.

Itt eltávolítjuk az utolsó oszlopot, majd végezzük a vegyes rögzített oszlopszélesség javítását a Microsoft Power Queryben.

Következtetés

Ebben az oktatóanyagban egy módot találtunk a vegyes, rögzített oszlopszélességgel kapcsolatos problémák megoldására a Microsoft Power Query használatával. Ha tetszett az oktatóanyagban szereplő tartalom, kérjük, ne felejtsen el feliratkozni a LuckyTemplates TV-csatornára.

Hatalmas mennyiségű tartalom jön ki folyamatosan tőlem és egy sor tartalomkészítőtől, akik mindannyian elkötelezettek a LuckyTemplates és a Power Platform használatának javítása iránt.

Méhfű