Převést sloupce 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. Po otočení sloupců obsahuje pravá tabulka diagramu prázdný sloupec a prázdné řádky. Sloupec Atributy obsahuje také devět řádků se třemi opakováními A1, A2 a A3. Nakonec sloupec Hodnoty obsahuje hodnoty 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, Kanada na řádku 2 a Panama 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, čtvrtém a sedmém řádku. Datum 1. července 2023 se zobrazí ve druhém, pátém a osmém řádku. Datum 1. srpna 2023 se nachází 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 rozložené.
• Hodnota: Hodnoty, které byly pod každým z neotočených záhlaví sloupců.

V uživatelském rozhraní je několik míst, kde můžete najít Převést sloupce na řádky. Můžete kliknout pravým tlačítkem myši na sloupce, které chcete odpivotovat, nebo můžete vybrat příkaz na kartě Transform v pásu karet.

Tři způsoby, jak můžete převést sloupce z tabulky:

• Převést sloupce na řádky
• Odebrat otočení u dalších sloupců
• Otočit pouze vybrané sloupce

Převést sloupce na řádky

V případě dříve popsaného scénáře musíte nejprve vybrat sloupce, které chcete rozpivotovat. 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 Zrušit pivot sloupců.

Snímek obrazovky tabulky, na kterém byly vybrány sloupce 1. června 2023, 1. července 2023 a 1. srpna 2023, a příkaz Unpivot sloupců vybrán v kontextovém menu.

Výsledkem této operace je výsledek zobrazený na následujícím obrázku.

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, čtvrtém a sedmém řádku. Datum 1. července 2023 je v druhém, pátém a osmém řádku. Datum 1. srpna 2023 se zobrazí ve třetím, šestém a devátém řádku. Položka Převést sloupce na řádky je navíc zvýrazněná v podokně Nastavení dotazu a kód jazyka M se zobrazí na řádku vzorců.

Zvláštní aspekty

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 s daty z 1. června 2023, 1. července 2023 a srpna 2023, s přidáním sloupce data z 1. září 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ě). Toto chování znamená, že jakýkoli nový sloupec, který jste přidali do zdrojové tabulky, se také 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.

Převést jiné sloupce do formátu řádků

Můžete také vybrat sloupce, které nechcete zrušit sloupce, a zrušit sloupce zbývajících sloupců v tabulce. Tato operace je místo, kde přichází na řadu Unpivot další sloupce.

Výsledkem této operace je přesně tentýž výsledek jako ten, který jste získali z Unpivot columns.

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, čtvrtém a sedmém řádku. Datum 1. července 2023 je v druhém, pátém a osmém řádku. Datum 1. srpna 2023 se zobrazí 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 rozpoloží všechny sloupce v tabulce s výjimkou těch, které jste vybrali. Tento typ transformace je ideálním řešením v případě, že v aktualizaci vašeho scénáře zdroj dat získá nové sloupce s daty, protože tyto nové sloupce jsou automaticky zahrnuty a odpivotovány.

Zvláštní aspekty

Podobně jako při operaci Převést sloupce na řádky, pokud se dotaz aktualizuje a jsou načtena další data ze zdroje, všechny sloupce se převedou na řádky, kromě 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 Země a pak vybrat Rozbalit 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.

Převést jenom vybrané sloupce na řádky

Úč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. Umožňuje rozvést pouze vybrané sloupce na řádky.

Chcete-li provést tuto operaci, vyberte sloupce, které chcete unpivotovat; v tomto příkladu se jedná o všechny sloupce kromě sloupce Země. Potom klikněte pravým tlačítkem myši na libovolný z vybraných sloupců a poté vyberte Odepivotovat pouze vybrané sloupce.

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, čtvrtém a sedmém řádku. Datum 1. července 2023 je v druhém, pátém a osmém řádku. Datum 1. srpna 2023 se zobrazí ve třetím, šestém a devátém řádku.

Zvláštní aspekty

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 unpivot operace byla použita pouze na 6/1/2020, 7/1/2020 a 8/1/2020, takže sloupec se záhlavím 9/1/2020 zůstane beze změny.