Připojení několika listů v Excelu k LuckyTemplates

Tento tutoriál pojednává o tom, jak importovat a otevřít soubor Excel s více listy do jedné tabulky LuckyTemplates.

Tento datový soubor Excel bude použit pro tento tutoriál.

Soubor obsahuje 60 listů dat se stejným rozložením.

Cílem tohoto tutoriálu je načíst všechny listy Excelu do LuckyTemplates a získat informace o dluhopisech jako jednu tabulku. Musíte získat ticker Zabezpečení a přidat jej do samostatného sloupce. Poté je všechny připojte do jedné tabulky, která využívá uživatelské rozhraní ke generování kódu, který potřebujete.

Obsah

• Import listů Excelu do LuckyTemplates
• Přináší bezpečnostní ticker z Excelu do LuckyTemplates
• Odstranění prázdných řádků
• Duplikování dotazu
• Přiřazení datových typů ke sloupcům
• Přidání sloupce Security Ticker z Excelu do LuckyTemplates
• Vytvoření vlastního sloupce pro dotaz na dluhopisy
• Závěr

Import listů Excelu do LuckyTemplates

První věc, kterou musíte udělat, je otevřít a vytvořit parametr pro uložení umístění souboru pomocí možnosti Spravovat parametry .

V poli Spravovat parametry klikněte na Nový a jako název parametru zadejte FileLocation . Dále nastavte Typ dat na Text a Navrhované hodnoty na Libovolnou hodnotu . Zkopírujte cestu k souboru Excel a vložte ji do polí Aktuální hodnota.

Po stisknutí OK klikněte na Nový zdroj a vyberte Sešit Excel .

Dále vyberte a otevřete soubor Excel. Po dokončení se otevře obrazovka Navigátor a zobrazí se seznam všech listů v souboru Excel.

Vyberte první list a stiskněte OK. Poté se tabulka zobrazí v Editoru Power Query.

Další věcí, kterou musíte udělat, je změnit pevně zakódovanou cestu k souboru na parametr souboru. Otevřete okno Advanced Editor a změňte cestu k souboru ve zdrojovém kódu na FileLocation .

Nejlepší je mít data v tabulkách aplikace Excel, protože hranice nebo rozsah dat jsou definovány předtím, než je do nich přenesete. Protože soubor obsahuje listy a žádné tabulky, riskuje, že přinese prázdné sloupce a prázdné řádky, takže to musíte vyřešit.

Přináší bezpečnostní ticker z Excelu do LuckyTemplates

Vědět, že rozvržení listů je pevné, pomáhá při vytváření řešení, zejména pokud chcete maximalizovat a používat uživatelské rozhraní ke generování kódu. Jedním požadavkem je například přidat sloupec, který obsahuje ticker zabezpečení. Pokud se podíváte na data, můžete vidět ticker.

Uživatelské rozhraní vám může pomoci extrahovat hodnotu tickeru. Klikněte pravým tlačítkem na buňku a vyberte Přidat jako nový dotaz .

V řádku vzorců uvidíte, že za názvem tabulky následuje číslo řádku založené na nule mezi složenými závorkami. Složené závorky se nazývají operátory pozičního indexu . Uvidíte také název pole uzavřený v hranatých závorkách, které se nazývají operátory přístupu k polím .

Pomocí těchto kódů nyní můžete extrahovat hodnotu. Vraťte se do tabulky dluhopisů a získejte data dluhopisů. Nejprve odstraňte horních 8 osm řádků. Klikněte na Odebrat řádky a vyberte Odebrat horní řádky .

Dále zadejte 8 do pole Počet řádků a stiskněte OK.

Poté klepněte na Použít první řádek jako záhlaví a nastavte záhlaví.

Po dokončení bude tabulka Bond nyní vypadat takto.

Přímo pod záhlavím uvidíte tenkou čáru, která představuje kvalitu sloupce. Odtud můžete vidět, že ve sloupcích je poměrně hodně prázdných míst. To znamená, že soubor přinesl mnoho prázdných řádků.

Odstranění prázdných řádků

Chcete-li odstranit prázdné řádky, klikněte na Odebrat řádky a vyberte Odebrat prázdné řádky .

Tato transformace generuje tuto syntaxi:

Record.FieldValues ​​získává všechny hodnoty z aktuálního řádku v tabulce jako seznam. List.RemoveMatchingItems odstraní všechny hodnoty v prvním seznamu, který má shodu v druhém seznamu. Druhý seznam obsahuje pouze prázdný textový řetězec nebo null. Toto jsou hodnoty, které budou vyloučeny z prvního seznamu.

Pokud byly ze seznamu s hodnotami pole záznamu odstraněny všechny prázdné textové řetězce a null, měl by být seznam prázdný a List.IsEmpty se vyhodnotí jako True. Potom Table.SelectRows zachová Trues.

Neměli byste skončit s tabulkou pouze s prázdnými řádky. Proto je klíčové slovo not přidáno před List.IsEmpty . To pak vrátí tabulku obsahující neprázdné řádky.

Kromě prázdných řádků musíte také odstranit prázdné sloupce. Ještě předtím se ale podívejte, co Power Query generuje, když sloupec odeberete. Vyberte čtvrtý sloupec a klikněte na Odebrat sloupce .

Po provedení transformace uvidíte tuto syntaxi v řádku vzorců.

Zavolá funkci Table.RemoveColumns , poté odkazuje a předá předchozí krok v podokně Aplikované kroky jako první argument. Transformace dále předá seznam obsahující názvy sloupců, které chcete odstranit.

Duplikování dotazu

Nyní duplikujte dotaz a v podokně Použité kroky vyberte krok Povýšit záhlaví. Dále klikněte na tento krok pravým tlačítkem a vyberte Smazat do konce .

Nezapomeňte, že můžete použít operátor pozičního indexu k předání čísla řádku založeného na nule v sadě složených závorek. Zachovejte tedy pouze první řádek zadáním 0 do dvou složených závorek na řádku vzorců.

Po dokončení bude tabulka Bond vypadat takto.

Odtud znovu použijte část logiky, která byla vytvořena krokem Odstraněné prázdné řádky, k přeměně záznamu na seznam a k odstranění hodnoty null. Vraťte se k dotazu na vazbu a vyberte krok Odebrat prázdné řádky. Dále zkopírujte tento M kód.

Vraťte se k duplicitnímu dotazu a vložte kód do řádku vzorců. Poté uspořádejte několik řetězců, aby vzorec vypadal takto.

Nyní jste vytvořili seznam s názvy sloupců, které chcete zachovat. Přejmenujte dotaz Názvy sloupců .

Poté se vraťte k dotazu Bond. Protože jste vytvořili dotaz, který obsahuje všechny sloupce, které chcete zachovat, musíte ve funkci Table.RemoveColumns uvést všechny sloupce, které chcete vyloučit .

Změňte {Sloupec4} se stejnou syntaxí, která byla zkopírována z kroků Odebrané prázdné řádky. Změňte také {“”, null} na ColumnNames .

Poté musíte předat seznam se skutečnými názvy sloupců z tabulky Bond. Změňte Record.FieldValues(_) na Table.ColumnNames() . Chcete-li předat argument odkazu na tabulku , zadejte do závorek #“Odstraněné prázdné řádky“ .

Přiřazení datových typů ke sloupcům

Další věcí, kterou musíte udělat, je přiřadit příslušné datové typy sloupcům. Ve sloupci Datum klikněte na ikonu vedle záhlaví a vyberte Datum.

Pro sloupce PX_LAST a YLD_YTM_MID vyberte datový typ Desetinné číslo.

Pomocí těchto tří dotazů jste vytvořili všechny stavební bloky, které můžete použít k návrhu řešení, které řeší všechny listy v souboru. K tomu je třeba duplikovat dotaz Bond a odstranit všechny kroky kromě kroku Zdroj v podokně Aplikované kroky.

Přidání sloupce Security Ticker z Excelu do LuckyTemplates

V kroku Zdroj můžete vidět všechna data v souboru Excel. Namísto vytváření samostatného dotazu pro každý list můžete použít Bond dotaz a transformovat vnořené tabulky ve sloupci Data.

Nejprve přidejte štítek Zabezpečení. Pokud kliknete na prázdné místo uvnitř buňky libovolné tabulky, zobrazí se náhled obsahu této tabulky.

Chcete-li získat ticker zabezpečení, musíte vytvořit logiku pomocí příkazu. Přejděte na dotaz Column2 a zkopírujte adresu tickeru zabezpečení z řádku vzorců.

Dále se vraťte k dotazu Bond a přidejte vlastní sloupec.

Pojmenujte sloupec Security Ticker a napište následující M kód.

Vzorec obsahuje příkaz IF , který říká, že pokud je slovo Zabezpečení nalezeno ve Sloupec1, poskytne hodnotu buňky ze Sloupec2, která s ním sousedí. V opačném případě bude poskytnuta hodnota null.

Po stisknutí OK se do tabulky přidá nový sloupec s tickery Zabezpečení.

Klikněte na rozbalovací tlačítko sloupce Security Ticker a zrušte výběr hodnoty null, abyste odstranili všechny hodnoty null ve sloupci.

Poté vám zůstanou všechny informace o dluhopisech ze všech listů. Vše, co musíte udělat, je zopakovat provedené transformace provedené v dotazu Bond(2) a aplikovat je na vnořené tabulky ve sloupci Data.

Vytvoření vlastního sloupce pro dotaz na dluhopisy

Přejděte na dotaz Bond(2), otevřete okno Advanced Editor a zkopírujte následující kód:

Dále se vraťte k dotazu Bond a vytvořte další vlastní sloupec. Protože potřebujete použít více transformací ve více krocích, musíte použít příkaz let . Zadejte tedy let a vložte kód z pokročilého editoru.

Potom změňte Bond_Sheet na Data , abyste transformovali tabulku ve sloupci Data.

Po stisknutí OK se do tabulky přidá nový sloupec.

Pokud kliknete na prázdné místo uvnitř kterékoli buňky tohoto sloupce, zobrazí se data z dotazu Bond(2).

Vše, co potřebujete, je v posledních dvou sloupcích dotazu Bond. Vyberte tedy sloupce Security Ticker a Custom a na pásu karet Odebrat sloupce klikněte na Odebrat další sloupce .

Rozbalte sloupec Vlastní a zrušte zaškrtnutí políčka Použít původní název jako předponu.

Po dokončení potvrďte tabulku. Pod tabulkou vyberte možnost Profilování sloupců na základě celé sady dat .

Po této transformaci dojde ve sloupci Data k chybě.

Chybu tedy prozkoumejte kliknutím na sloupec Data a výběrem možnosti Keep Errors na pásu karet Keep Rows.

Pokud kliknete na hodnotu Error ve sloupci Data, můžete si přečíst následující chybovou zprávu:

Chcete-li problémy vyřešit, nejprve odstraňte krok Udržované chyby. Klikněte na sloupec Data a na pásu karet Odebrat řádky vyberte možnost Odebrat chyby .

Poté nastavte možnost profilování sloupců zpět na prvních 1000 řádků. A to je vše!

Závěr

Toto řešení založené na uživatelském rozhraní vám může pomoci připojit více listů ze souboru Excel do LuckyTemplates. Namísto vytváření 60 samostatných dotazů a opakovaného provádění všech transformací může toto řešení vytvořit jediný dotaz, který provede všechny transformace. Využijte a maximalizujte toto řešení na .

Melissa