V jednom z předchozích videích jsme si ukázali, jak pomocí funkce ROZDĚLIT.TEXT neboli funkce TEXTSPLIT oddělit hodnoty podle více oddělovačů. Po tomto videu mi přišlo několik zpráv, jak se podobné rozdělení provede v Power Query. A přesně to si ukážeme v dnešním videu. A použijeme stejný příklad jako v předchozím videu.
Excelový soubor ke stažení:
První, co musíme před nahráním dat do Power Query udělat, je změnit obyčejný rozsah dat na excelovou tabulku. Klikneme do dat a použijeme klávesovou kombinaci CTRL+T, potvrdíme, že tabulka obsahuje záhlaví a tabulku rovnou pojmenujeme na Data, a ještě se rovnou zbavíme tohoto typického proužkování, takže Styl tabulky a žádný styl.
Teď můžeme kliknout do tabulky a na kartě Data vybrat Z tabulky nebo oblasti. Tabulka se po chvilce nahraje do Power Query.
Když v Power Query chceme rozdělit hodnoty podle oddělovače, tak na to máme nástroj na kartě Transformace, kde máme možnost Rozdělit sloupec a zde podle oddělovače. A zde si můžeme buď vybrat jeden z přednastavených oddělovačů nebo si stanovit oddělovač vlastní.
Stejně jako ve funkci ROZDĚLIT.TEXT zde máme ale možnost nastavit pouze jeden oddělovač. Takže pokud bychom neznali trik, který vám za chvíli ukážu, tak byste nejspíše postupovali tak, že byste nejprve sloupec rozdělili podle prvního oddělovače.
A tento proces s rozdělováním byste opakovali tak dlouho, dokud byste nerozdělili všechny hodnoty. Teoreticky na tomto postupu není nic špatného, jediná nevýhoda je ta, že zaprvé musíte proces opakovat a za druhé, že se tím navyšuje počet kroků. Což není u dat, kde máte třeba statisíce nebo miliony řádků žádoucí, jelikož vysoký počet kroků může zpomalovat úpravu dat.
Postupně tedy hodnoty rozdělovat nechceme. Kroky smažeme a začneme znovu.
Úprava bude velmi podobná jako ve funkci ROZDĚLIT.TEXT. Než ale s úpravou začneme, tak se podíváme na to, jakou funkci Power Query vygeneruje, když rozděluje hodnoty v buňce podle oddělovače. Rozdělíme sloupec podle oddělovače ještě jednou. Ve funkci Table.SplitColumn je použitá funkce Splitter.SplitTextByDelimiter, což je funkce, která rozděluje hodnoty v buňce podle jednoho zvoleného oddělovače. Ve funkci dokonce tento oddělovač vidíme.
Abychom mohli rozdělit hodnoty podle více oddělovačů, tak musíme změnit tuto funkci, která rozděluje buňku podle jednoho oddělovače a nahradit ji funkcí, která bude rozdělovat hodnoty podle více oddělovačů. A to je funkce Splitter.SplitTextByAnyDelimiter. A v této funkci můžeme nastavit ve složených závorkách oddělovače.
Když to ale potvrdíme, tak se zobrazí pouze dva sloupce. Kde je zbytek rozdělených hodnot? Pokud nepracujete často v Power Query, tak vám tento problém nemusí být na první pohled zřejmý a rovněž není pro začátečníky jednoduché ho odhalit.
Abychom pochopili, proč se vytvořili pouze dva sloupce u rozdělení, vytvoříme duplikát tabulky, na které si to ukážeme. Znovu rozdělíme sloupec oddělovačem. To je totiž způsob, jakým jsme začali. A teď jsme vygenerovali funkci, kterou jsme následně nahradili funkcí pro oddělení dle více oddělovačů. Nicméně když se k rozdělení vrátíme, tak pochopíme, proč se hodnoty rozdělili do dvou sloupců. Jelikož je to nastavené zde v rozdělení. To ostatně můžeme vidět i v samotné funkci, kde vidíme, že se mají vytvořit dva sloupce. Tím, že jsme funkci pro oddělení jedním oddělovačem nahradili funkcí pro rozdělení více oddělovači jsme, ale nezměnili to, že mají stále vzniknout pouze dva sloupce.
Řešení je jednoduché. První možností je, že nahradíme tyto dva vytvořené sloupce přesným počtem sloupců, které se mají vytvořit. Máme čtyři oddělovače, tudíž vznikne pět sloupců. V tomto případě je výsledkem pět sloupců, takže hodnoty nahradíme pětkou.
Nebo můžeme tento nepovinný parametr zcela smazat a tím pádem se vytvoří rovněž pět sloupců.
Hodnoty máme rozdělené, takže tabulku můžeme poslat zpátky do Excelu. Samozřejmě, když do zdrojové tabulky přidáme nové řádky a obnovíme propojení, tak se nové řádky automaticky rozdělí do stanovených sloupců.
