Kurz Microsoft Power Query o tom, jak opravit smíšené problémy s pevnou šířkou sloupců

Mudassir: Pro dnešek máme velmi zajímavý problém, se kterým můžeme pracovat. Problém s tímto souborem je, že je opravený oddělený sloupci a nevím, jak to vyřešit pomocí Microsoft Power Query. Celé video tohoto tutoriálu si můžete prohlédnout ve spodní části tohoto blogu.

Za prvé, nebylo pro mě snadné dynamicky mazat sloupce. Za druhé, v tomto přehledu máme jednu tabulku s jinou šířkou sloupce a pak další tabulku s jinou šířkou sloupce.

Pokud tedy použiji jeden oddělovač dynamicky nahoře, nebyl bych schopen získat data úhledně. Chci získat všechna tato data z druhé tabulky a moje čísla produktů z první tabulky. Také chci číslo zakázky v každém řádku všech tabulek.

Snažil jsem se to vyřešit sám, ale protože to má něco společného s dotazem na napájení, potřeboval jsem pomoc od Melissy. Myslel jsem, že jí to zabere aspoň dva dny, ale hned se jí podařilo přijít na řešení.

Melissa nám ukáže, jak tento komplikovaný problém vyřešila. Myslím, že většina lidí se bude potýkat s těmito druhy problémů a hledá způsoby, jak je vyřešit.

Melissa: První tip je, že pokud se díváte na soubor s pevnou délkou, můžete přejít na kartu Zobrazit a zapnout možnost Monospaced .

Vidíme, že jde o písmo s pevnou délkou. Můžeme také vidět záhlaví, počáteční tabulky a podtabulky. Toto jsou části, které nás zajímají a z nichž chceme extrahovat.

Také se prosím ujistěte, že máte povolenou lištu vzorců. Vždy je dobré mít to viditelné na obrazovce, protože je budeme často používat k drobným úpravám vstupu.

Vytvořil jsem parametr pro umístění souboru, kam jsem uložil soubor CSV. Přinesl jsem to jako pracovní soubor a vložil jsem ho do parametru umístění souboru. Poté jsem vytvořil referenci a z této reference budu vycházet. To je to, na co se právě teď díváme v Microsoft power query.

Obsah

• Přidání sloupce indexu
• Vytvoření seznamu vyrovnávací paměti v Microsoft Power Query
• Rozdělení záhlaví z řádků
• Odstranění nul a prázdných míst v Microsoft Power Query
• Rozdělení sekcí
• Oříznutí textových řetězců v Microsoft Power Query
• Závěr

Přidání sloupce indexu

Obvykle, když začnu pracovat na takovém souboru, budu potřebovat znát požadavky klienta. Ptám se, co klient potřebuje a co hledat.

V tomto případě chceme číslo položky a číslo zakázky z hlaviček a pak chceme všechny podrobnosti, které patří do této konkrétní hlavičky.

Budeme potřebovat klíč, abychom ty věci dali zase dohromady. Ale pokud není přítomen žádný klíč, pak chci přidat . Kliknu na ikonu mini tabulky, vyberu Přidat sloupec indexu a poté přidám Od 0 .

Později použiji logiku se seznamy, které mají index založený na nule. To, že váš index začíná od nuly, ve skutečnosti znamená, že můžete odkazovat na stejný řádek. V opačném případě budete muset odečíst 1, abyste se dostali na pozici založenou na 0.

Potom musíme najít pozici, kde jsou naše záhlaví, což můžeme udělat docela snadno, protože tyto záhlaví se neustále opakují v celém souboru.

Pro začátek zkopírujeme tuto hodnotu:

Přidejte nový prázdný dotaz, vložte jej a nazvěte jej HeaderID .

Udělám stejný postup pro podtabulky. Zkopíruji tento textový řetězec, vytvořím další prázdný dotaz a vložím tuto hodnotu. Toto bude řetězec, který použijeme při hledání podrobných řádků.

Pokud tento proces nějak změní záhlaví některé z těchto tabulek, stačí změnit jeden z textových řetězců a soubor bude znovu fungovat.

Opravdu se nemusím ponořit do kódu M, abych hledal řetězec, který hledáme. Můžeme to použít jen jako parametr.

Povolme zatížení pro tyto dva dotazy.

Vytvoření seznamu vyrovnávací paměti v Microsoft Power Query

První věc, kterou udělám, je přeměnit Column1 na seznam tím, že na něj jednou odkazuji a načtu ho do paměti. Tímto způsobem nemusím provádět opakované volání do souboru.

Otevřu pokročilý editor a umístím ho úplně nahoru. Když k vytvoření kódu použijete uživatelské rozhraní, bude odkazovat na předchozí krok.

Když umístíte krok vyrovnávací paměti kamkoli jinam do kódu a budete chtít provést úpravu, později vám to pomůže provést změny v kroku, který ručně vytváříte.

Budu to nazývat BufferList a odkazovat na Column1. Pro načtení do paměti přidám krok List.Buffer .

Tady je moje proměnná úplně nahoře. Mohu na to odkazovat znovu a znovu.

První věc, kterou chci zjistit, je, kde moje záhlaví začínají, protože potřebuji klíč k zachování těchto sekcí záhlaví a získání jediné hodnoty pro všechny tyto řádky. K tomu přidám vlastní sloupec a nazvu ho Záhlaví .

Napíšu, že pokud se Sloupec1 rovná našemu ID záhlaví, pak chci, aby moje indexové číslo bylo null.

Výsledkem bylo, že našel text a vrátil 5 a 23.

Potřebuji tuto hodnotu ve všech řádcích, takže ji musím vyplnit. Stačí kliknout pravým tlačítkem a vyplnit, ale můžete také použít velmi jednoduchou syntaxi a přidat ji do řádku vzorců.

V tomto případě jsem přidal Table.FillDown a v textovém řetězci jsem uvedl, který sloupec chceme vyplnit (Header).

Nyní jsme to vyplnili pro všechny řádky. Máme klíč pro všechny sekce záhlaví a všechny sekce řádků, protože všechny sdílejí tuto hodnotu.

Rozdělení záhlaví z řádků

Dalším krokem je rozdělení záhlaví z řádků. Přidám další vlastní sloupec a nazvu ho Temp . Tentokrát uděláme něco propracovanějšího a využijeme BufferList , který jsem vytvořil dříve.

Použijeme několik funkcí seznamů, abychom se podívali na každou z pozic a zjistili, zda existuje shoda s indexem.

Začnu příkazem if a použiji List.Contains k vyhledání konkrétní pozice v BufferList a odkazu na dotaz HeaderID .

Chceme ji najít v celé délce souboru a poté vrátit pozici položky v seznamu. Pokud se shoduje s indexem, máme shodu pro tento konkrétní řádek.

Poté chci vrátit hodnotu pro identifikaci záhlaví. V tomto případě právě vracím H. Zkopíruji syntaxi, abych to nemusel psát znovu.

Musíme také identifikovat sekci řádku. Pokud seznam neobsahuje HeaderID , ale DetailID , pak jsme v sekci řádků.

Pokud je Column1 prázdný textový řetězec, pak chci, aby zůstal prázdný . Pokud tomu tak není, pak chci, aby to bylo null .

Tato opozice získala řádek záhlaví a vrátila H a poté našla podrobný řádek a vrátila R. Poté vrátila 0 pro všechny položky, které jsou sdíleny v této sekci řádku.

Tyto mezery nebo nuly jsou důležité, protože umožňují vyplnit. Fill down se přes tyto prázdné buňky nebude pohybovat, takže je můžeme později odstranit.

To provedeme v řádku vzorců a znovu použijeme Table.FillDown . Chce to seznam s názvem sloupce, což je náš sloupec Temp .

Nyní máme hodnoty H a R opakovány v celém tomto sloupci, což znamená, že můžeme skutečně rozdělit záhlaví z podrobných sekcí.

Můžete jej také vyplnit z uživatelského rozhraní, pokud nechcete psát kód. Stačí kliknout pravým tlačítkem a vybrat Vyplnit a poté Dolů .

Odstranění nul a prázdných míst v Microsoft Power Query

Nyní, když máme toto právo, můžeme odstranit věci, které nepotřebujeme. Vše, co je null nebo obsahuje prázdné místo, jsou řádky, které nepotřebujeme a musíme je odstranit. Můžeme je odstranit filtrováním.

Rozdělení sekcí

Jakmile odstraníme tyto mezery a nuly, zůstane nám vše, co potřebujeme. V tomto okamžiku můžeme pouze rozdělit části. Můžeme se zaměřit na řádky záhlaví a vybrat je, protože mají oddělené mezery od všech řádků podrobností (které mají také oddělené mezery).

Do řádku vzorců přidám nový krok, který mi umožní vytvořit další filtr ve stejném sloupci. V tomto případě ponechám pouze všechny sekce záhlaví.

Teď tu mám všechny ty řádky záhlaví.

Mohu vybrat Sloupec1 , přejít na řádek vzorců, vybrat Rozdělit sloupec a poté rozdělit podle pozic.

Pak si to nechte zjistit sám power query. Navrhne několik pozic. Klepnutím na tlačítko OK tyto pozice přijmete.

Jediné, co nás z hlaviček zajímá, je položka a číslo zakázky .

Uvnitř řádku vzorců zde mohu přejmenovat ty s Item a Job # . To mě ušetří od dalšího kroku přejmenování sloupce.

Po tomto kroku vše, co musím udělat, je vybrat Item , vybrat Job # a samozřejmě vybrat náš klíč záhlaví . Poté odstraním všechny ostatní sloupce, protože je již nepotřebuji.

Toto bude výsledek. Musíme ještě vyčistit hodnoty a odstranit textovou položku a pomlčky. Vše, co chceme, jsou ty hodnoty mezi tím.

Takže to otevřeme a zrušíme výběr pomlček a položek.

Nyní jsou všechny hlavičky hotové.

Stejný proces musíme udělat i pro DetailID . Budu muset tyto kroky přejmenovat, aby pro mě bylo snazší se k nim vrátit o něco později.

Vrátíme se zpět k původnímu dotazu, který jsme začali. Začali jsme s Filtrovanými řádky v podokně Aplikované kroky.

Zkopíruji to a přidám to do svého filtru. Tentokrát nevybírám H, ale volím R.

Pak vyberu Sloupec1, půjdu do sloupce Rozdělit, rozdělen podle pozic, pak si to nechám zjistit pomocí dotazu Power.

To naznačuje mocenský dotaz. Pojďme to zkusit.

Tohle vlastně vypadá docela dobře. Dokonce i celkové řady se dokonale rozdělily. Samozřejmě je tam spousta mezer, protože jsme měli takové odsazení.

Oříznutí textových řetězců v Microsoft Power Query

Vyberu tento první sloupec a poté stisknutím klávesy Down + Shift vyberte sloupec 1.10. Přejděte na Transformace , vyberte Formát a poté Oříznout . Zastřižením se odstraní pouze přebytečné prostory před nebo na konci provázku, nikoli mezi nimi.

Dále můžeme propagovat pouze záhlaví, takže nemusím zadávat všechna záhlaví nebo názvy těchto sloupců. V kroku rozdělení jsem přejmenoval dva sloupce. Teď samozřejmě s 10 sloupci je to trochu otrava.

Musíme se také zbavit těch přebytečných hodnot. Protože máme součty, musím použít jeden z těchto posledních tří sloupců, protože jsou to jediné řádky, které mají další hodnoty někde mezi nimi. Poté zrušíme výběr těchto mezer, pomlček a textů.

Poté odstraním nepotřebné sloupce, takže zbude pouze tabulka s pouze záhlavími a pouze detaily. Potřebujeme klíč, abychom ty sekce dali zase dohromady.

K tomu můžeme použít samosloučení, abychom mohli sloučit tabulku se sebou samým a dát tyto informace zpět dohromady. Na kartě Domů vyberte Sloučit a poté vyberte Sloupec 5 a stejný dotaz.

Místo AllDetails chci AllHeaders jako svou počáteční tabulku, se kterou chci sloučit.

Tím se vrátily všechny informace z tabulky záhlaví s jedním řádkem pro každou položku a každé číslo zakázky.

Použili jsme klíč ke sloučení s řádky podrobností. Pokud v tomto prázdném prostoru stisknu na stranu, uvidíme náhled všech řádků, které patří do záhlaví 5.

Zde odebereme poslední sloupec a poté dokončíme opravu smíšené pevné šířky sloupce v dotazu Microsoft Power query.

Závěr

V tomto kurzu jsme přišli na způsob, jak vyřešit smíšené problémy s pevnou šířkou sloupců pomocí Microsoft Power Query. Pokud se vám obsah obsažený v tomto konkrétním tutoriálu líbil, nezapomeňte se přihlásit k odběru televizního kanálu LuckyTemplates.

Neustále nám vychází obrovské množství obsahu od mě a od řady tvůrců obsahu, z nichž všichni se věnují zlepšování způsobu, jakým používáte LuckyTemplates a Power Platform.

Melissa