Převedení sloupců na řádky
V Power Query můžete sloupce transformovat na páry atribut-hodnota, kde se sloupce stanou řádky.
Diagram znázorňující levou tabulku s prázdným sloupcem a řádky a hodnotami Atributy A1, A2 a A3 jako záhlaví sloupců Sloupec A1 v této tabulce obsahuje hodnoty V1, V4 a V7. Sloupec A2 obsahuje hodnoty V2, V5 a V8. Sloupec A3 obsahuje hodnoty V3, V6 a V9. V případě nepřekontovaných sloupců obsahuje pravá tabulka diagramu prázdný sloupec a řádky, sloupec Atributy s devíti řádky a třikrát opakovanými řádky A1, A2 a A3 a sloupec Hodnoty s hodnotami V1 až V9.
Například vzhledem k následující tabulce, kde řádky země a sloupce kalendářních dat vytvářejí matici hodnot, je obtížné analyzovat data škálovatelným způsobem.
Snímek obrazovky tabulky obsahující sloupec Země nastavený v datovém typu Text a tři sloupce s kalendářními daty 1. června 2023, 1. července 2023 a 1. srpna 2023 nastaveným jako datový typ Celé číslo Sloupec Země obsahuje USA v řádku 1, Kanadě na řádku 2 a Panamě na řádku 3.
Místo toho můžete tabulku transformovat na tabulku s nepřekontovanými sloupci, jak je znázorněno na následujícím obrázku. V transformované tabulce je jednodušší použít datum jako atribut k filtrování.
Snímek obrazovky tabulky obsahující sloupec Země nastavený jako datový typ Text, sloupec Atribut nastavený jako datový typ Text a sloupec Hodnota nastavený jako datový typ Celé číslo Sloupec Země obsahuje USA v prvních třech řádcích, Kanadu v dalších třech řádcích a Panamě v posledních třech řádcích. Sloupec Atribut obsahuje datum 1. června 2023 v prvním a sedmém řádku, datum 1. července 2023 ve druhém, pátém a osmém řádku a datum 1. srpna 2023 ve třetím, šestém a devátém řádku.
Klíčem v této transformaci je, že máte v tabulce sadu kalendářních dat, která by měla být všechna součástí jednoho sloupce. Odpovídající hodnota pro každé datum a zemi by měla být v jiném sloupci a efektivně vytvořit dvojici atribut-hodnota.
Power Query vždy vytvoří dvojici atribut-hodnota pomocí dvou sloupců:
- Atribut: Název záhlaví sloupců, které byly převedeny na řádky.
- Hodnota: Hodnoty, které byly pod jednotlivými záhlavími nepředkonaného sloupce.
V uživatelském rozhraní je několik míst, kde můžete najít sloupce Převést na řádky. Můžete kliknout pravým tlačítkem myši na sloupce, které chcete převést na řádky, nebo můžete vybrat příkaz na kartě Transformace na pásu karet.
Existují tři způsoby, jak převést sloupce z tabulky na řádky:
- Převést sloupce na řádky
- Převést další sloupce na řádky
- Převést jenom vybrané sloupce na řádky
V případě dříve popsaného scénáře musíte nejprve vybrat sloupce, které chcete převést na řádky. Stisknutou klávesu Ctrl můžete vybrat tolik sloupců, kolik potřebujete. V tomto scénáři chcete vybrat všechny sloupce s výjimkou sloupce s názvem Země. Po výběru sloupců klikněte pravým tlačítkem na některý z vybraných sloupců a pak vyberte Převést sloupce na řádky.
Snímek obrazovky tabulky s vybranými sloupci z 1. června 2023, 1. července 2023 a 1. srpna 2023 a příkazem Převést sloupce na řádky vybrané v místní nabídce
Výsledkem této operace je výsledek zobrazený na následujícím obrázku.
Po vytvoření dotazu z předchozích kroků si představte, že se vaše počáteční tabulka aktualizuje tak, aby vypadala jako na následujícím snímku obrazovky.
Snímek obrazovky tabulky se stejnými původními sloupci kalendářních dat z 1. června 2023, 1. července 2023 a ze srpna 2023 s přidáním sloupce kalendářního data ze září 1. 2023 Sloupec Country (Země) stále obsahuje hodnoty USA, Kanady a Panamy, ale také spojené království přidáno do čtvrtého řádku a Mexiko přidáno do pátého řádku.
Všimněte si, že přidáváte nový sloupec pro datum 1. září 2023 (1. 9. 2023) a dva nové řádky pro země/oblasti Uk a Mexiko.
Pokud aktualizujete dotaz, všimněte si, že operace se provádí v aktualizovaném sloupci, ale nemá vliv na sloupec, který nebyl původně vybrán (v tomto příkladu země). To znamená, že jakýkoli nový sloupec, který jste přidali do zdrojové tabulky, se nepřekontuje.
Následující obrázek ukazuje, jak váš dotaz vypadá po aktualizaci s novou aktualizovanou zdrojovou tabulkou.
Snímek obrazovky tabulky se sloupci Země, Atribut a Hodnota První čtyři řádky sloupce Země obsahují USA, druhý čtyři řádky obsahují Kanadu, třetí čtyři řádky obsahují Panamu, čtvrtý čtyři řádky obsahuje Spojené království a páté čtyři řádky obsahují Mexiko. Sloupec Atribut obsahuje data z 1. června 2023, 1. července 2023 a srpna 2023 v prvních čtyřech řádcích, která se opakují pro každou zemi.
Můžete také vybrat sloupce, které nechcete převést na řádky a převést na řádky ostatních sloupců v tabulce. Tato operace spočívá v tom, že se přehrávají další sloupce, které přejdou na řádky .
Výsledkem této operace je přesně stejný výsledek jako výsledek, který jste získali ze sloupců Převést na řádky.
Snímek obrazovky tabulky obsahující sloupec Země nastavený jako datový typ Text, sloupec Atribut nastavený jako datový typ Text a sloupec Hodnota nastavený jako datový typ Celé číslo Sloupec Země obsahuje USA v prvních třech řádcích, Kanadu v dalších třech řádcích a Panamě v posledních třech řádcích. Sloupec Atribut obsahuje datum 1. června 2023 v prvním a sedmém řádku, datum 1. července 2023 ve druhém, pátém a osmém řádku a datum 1. srpna 2023 ve třetím, šestém a devátém řádku.
Poznámka
Tato transformace je zásadní pro dotazy, které mají neznámý počet sloupců. Operace zruší převést všechny sloupce z tabulky s výjimkou sloupců, které jste vybrali. Toto je ideální řešení, pokud zdroj dat ve vašem scénáři získal nové sloupce kalendářních dat v aktualizaci, protože ty se vyberou a nepřevedou na řádky.
Podobně jako při operaci Převést sloupce na řádky, pokud se dotaz aktualizuje a ze zdroje dat se vybere další data, všechny sloupce se nepřekontují s výjimkou těch, které byly dříve vybrány.
Chcete-li tento proces ilustrovat, řekněme, že máte novou tabulku, jako je ta na následujícím obrázku.
Snímek obrazovky tabulky se sloupci Země, 1. června 2023, 1. července 2023, 1. srpna 2023 a 1. září 2023 a všemi sloupci nastavenými na datový typ Text Sloupec Země obsahuje odshora dolů, USA, Kanadu, Panamu, Spojené království a Mexiko.
Můžete vybrat sloupec Country (Země) a pak vybrat Unpivot other column (Převést na jiný sloupec), což vede k následujícímu výsledku.
Snímek obrazovky tabulky se sloupci Země, Atribut a Hodnota Sloupce Země a Atribut jsou nastaveny na datový typ Text. Sloupec Hodnota je nastaven na datový typ Celá hodnota. První čtyři řádky sloupce Země obsahují USA, druhý čtyři řádky obsahují Kanadu, třetí čtyři řádky obsahují Panamu, čtvrtý čtyři řádky obsahuje Spojené království a páté čtyři řádky obsahují Mexiko. Sloupec Atribut obsahuje 1. června 2023, 1. července 2023, 1. srpna 2023 a 1. září 2023 v prvních čtyřech řádcích, které se opakují pro každou zemi.
Účelem této poslední možnosti je oddělit jenom určité sloupce z tabulky. Tato možnost je důležitá pro scénáře, ve kterých pracujete s neznámým počtem sloupců ze zdroje dat a chcete vybrané sloupce jenom převést na řádky.
Chcete-li provést tuto operaci, vyberte sloupce, které chcete převést na řádky, které jsou v tomto příkladu všechny sloupce kromě sloupce Země . Potom klikněte pravým tlačítkem myši na libovolný z vybraných sloupců a pak vyberte Převést pouze vybrané sloupce na řádky.
Všimněte si, jak tato operace přináší stejný výstup jako předchozí příklady.
Snímek obrazovky tabulky obsahující sloupec Země nastavený jako datový typ Text, sloupec Atribut nastavený jako datový typ Text a sloupec Hodnota nastavený jako datový typ Celé číslo Sloupec Země obsahuje USA v prvních třech řádcích, Kanadu v dalších třech řádcích a Panamě v posledních třech řádcích. Sloupec Atribut obsahuje datum 1. června 2023 v prvním a sedmém řádku, datum 1. července 2023 ve druhém, pátém a osmém řádku a datum 1. srpna 2023 ve třetím, šestém a devátém řádku.
Pokud se po aktualizaci změní zdrojová tabulka tak, aby měla nový sloupec 1. 9. 2020 a nové řádky pro Spojené království a Mexiko, výstup dotazu se liší od předchozích příkladů. Řekněme, že se naše zdrojová tabulka po aktualizaci změní na tabulku na následujícím obrázku.
Výstup našeho dotazu vypadá jako na následujícím obrázku.
Vypadá to takto, protože operace převést na řádky byla použita pouze na 1. 6. 2020, 1. 7. 2020 a 1. 8. 2020 , takže sloupec se záhlavím 9. 1. 2020 zůstane beze změny.