Tweet

Sloučit Merge možnosti - v Power Query a Power BI

Sloučit Merge

V tomto článku se podíváme na možnost jak sloučit tabulky v Power Query Editoru, ať už v Excel, nebo i v Power BI. Pro přehlednost je článek rozdělen na kapitoly.

• Sloučit (Merge) o co jde, SVYHLEDAT?
• Data, které budeme slučovat
• Jak sloučit dvě tabulky
• Možnosti sloučení - jedinečné hodnoty
  • Levé vnější - Left Outer (všechny z prvního, odpovídající z druhého)
  • Pravé vnější - Right Outer (všechny z druhého, odpovídající z prvního)
  • Úplné vnější - Full Outer (všechny řádky z obou)
  • Vnitřní - Inner (pouze odpovídající řádky)
  • Levé anti - Left Anti (řádky jenom v prvním)
  • Pravá anti - Right Anti (řádky jenom v druhém)
• Možnosti sloučení - duplicitní hodnoty v tabulce
  • Levé vnější - Left Outer (všechny z prvního, odpovídající z druhého)
  • Pravé vnější - Right Outer pro duplicity (všechny z druhého, odpovídající z prvního)
  • Úplné vnější - Full Outer pro duplicity (všechny řádky z obou)
  • Vnitřní - Inner pro duplicity (pouze odpovídající řádky)
  • Levé anti - Left Anti pro duplicity (řádky jenom v prvním)
  • Pravá anti - Right Anti pro duplicity (řádky jenom v druhém)
• Jiné možnosti sloučení

Sloučit v angličtině Merge

Potřebujete-li propojit dvě tabuky v Excel (nebo Power BI), můžete využít nástroj Power Query Editor. Kdy dojde ke sloučení tabulek. Na první pohled může sloučení připomínat funkci SVYHLEDAT, kdy k jedné tabulce přiřadíte data z druhé tabulky.

Proč potřebujete slučovat

Představte si že máte seznam zaměstnanců, kdy každý má své unikátní osobní číslo a druhou tabulku seznam odměn, kde je informace o odměně a v jednom sloupci se nachází osobní číslo zaměstnance, který danou odměnu nebo ocenění obdržel. budete-li mít tabulky v Excel, tak někoho napadne použít funkci SVYHLEDAT a k odměnám vložit třeba jméno a příjmení oceněného.

V tomto článku si ukážeme, jak na to využitím možnosti Sloučit - Merge v nástroji Power Query Editor (je součásti Excel i Power BI).

Možnosti slučování

Často se u slučování ukazuje pouze první možnost spojení, aneb levé vnější (a angličtině Left Outer). V totmo článku si představíme všechny možnosti.

Zůstaneme-li u oněch zaměstanců a odměn, možná někdo má těch odměn za rok více, někdo žádnou, co když dostane odměnu člověk, co již u vás nepracuje (ale než úřední šiml rozhodl ... tak chvílí trvalo).

V článku si ukážeme všechny možností, které Power Query Editor nabízí a to:

• Levé vnější - Left Outer (všechny z prvního, odpovídající z druhého)
• Pravé vnější - Right Outer (všechny z druhého, odpovídající z prvního)
• Úplné vnější - Full Outer (všechny řádky z obou)
• Vnitřní - Inner (pouze odpovídající řádky)
• Levé anti - Left Anti (řádky jenom v prvním)
• Pravá anti - Right Anti (řádky jenom v druhém)

Jak jednotlivá spojení fungují jsem viděl ukazovat i na venových diagramech:

Což pro někoho může být problém na pochopení. Proto představím na praktických příkladech, budeme si spojovat dvě malé tabulky.

Když už článek tvořím, tak zmíním i jak bude funguje, pokud se budou v tabulkách opakovat hodnoty, podle kterých se budou tabulky slučovat (podrobněji viz dále).

Data, které budeme slučovat

Rychlou teorií mate za sebou, teď se podíváme jaké tabulky budete potřebovat sloučit. Pro jednoduchost a přehled budeme mít dvě tabulky první označíme TabulkaA a druhou TabulkaB, obě budou mít tři sloupečky:

• ID - unikátní číslo, které se neopakuje
• Jméno nebo Příjmení - V tabulce A bude jméno a v tabulce B bude příjmení
• Poznámka - pro lepší pochopení propojení

Některá ID v první tabulce nemají protějšek ve druhé, některé mají a stejně tak některé ID v druhé tabulce B, nemají protějšek v první tabulce.

Samozdřejmě že v obou tabulkách jsou sloupce ID unikátní (tj. hodnoty v tomto sloupci se neopakují).

Tabulky s duplicitami

Pro komplexní pohled na Sloučit (Merge) budeme mít ještě dvě tabulky (TabulkaA_duplicita a TabulkaB_duplicita) ve které se hodnoty ve sloucpi ID budou záměrně opakovat. Tabulky budou mít opět tři sloupce:

• ID - neunikátní čísla, některé hodnoty se opakují
• Jméno nebo Příjmení - V tabulce A bude jméno a v tabulce B bude příjmení
• Poznámka - pro lepší pochopení propojení

Jak sloučit dvě tabulky

Předpokládám, že tabulky máte načteny v Power Query Editoru.

V prvním kroku budeme potřebovat načítat TabulkaA a TabulkaB. Pro jednoduchost k tabulce A připojíme tabulku B.

Vyberte TabulkaA v dotazech a na pásu karet Domů (Home) Vyberte Sloučit dotazy (Merge) a Sloučit dotazy jako nový (Merge as new)

Obdržíte dialogové okno, kde již máte předvybranou TabulkaA (1), v "rozbalovátku" (2) vyberete TabulkaB, označíte sloupce ID (3) a v Typu spojení ponecháte Levé vnější (4). Samozdřejmě pro další testy si můžete vybrat jiný typ spojení.

Klik na OK a máte hotovo.

Přidá se nový dotaz Sloučit (můžete si když tak přejmenovat). Pro dokončení ke potřeba tabulku rozbalit klikem na dvojšipku u názvu sloupce TabulkaB

.

Tip: Nemusíte používat původní název sloupce jako předpona.

Výsledkem je spojená tabulka. V dalším si ukážeme rozdíly, jak by vypadalo pokud pokud vyberete jinou volbu v typu spojení.

Další co můžete s tabulkou provést je, že data z důvodu přehlednosti seřadite ;)

Možnosti sloučení

Tabulky umíte sloučit, jak již víte k dispozici máte tyto možností:

• Levé vnější - Left Outer
• Pravé vnější - Right Outer
• Úplné vnější - Full Outer
• Vnitřní - Inner
• Levé anti - Left Anti
• Pravá anti - Right Anti

V dalších podkapitolách se podíváte jak budou vypadat jiná sloučení.

Levé vnější - Left Outer

Všechny záznamy z první tabulky a odpovídající záznamy z druhé tabulky.

Toto již víte jak bude vypadat.

Pravé vnější - Right Outer

Všechny záznamy z druhé tabulky a odpovídající z první tabulky. Vlastně opak předchozího

Úplné vnější - Full Outer

Všechny řádky z obou tabulek.

Vnitřní - Inner

pouze odpovídající řádky z první a druhé tabulky, na základě ID

Levé anti - Left Anti

Řádky které jsou jen v první tabulce a nemají záznam ve druhé tabulce.

Pravá anti - Right Anti

Řádky které jsou jen ve druhé tabulce a nemají záznam v první tabulce.

Možnosti sloučení pro duplicitní hodnoty v tabulce

V podobném duchu se podíváme co st stane pokud v tabulce se bude 2x (nebo i vícekrát) objevovat stejné ID. A to jak v první tabulce, tak ve druhé, případně v obou zároveň.

V ukázkových tabulkách jde o ID číslo 4, které je dva krát v první tabulce, ID 6, které je dva krát ve druhé tabulce a číslo 5 které je dva krát v první i ve druhé tabulce.

Opět budeme spojovat:

• Levé vnější - Left Outer
• Pravé vnější - Right Outer
• Úplné vnější - Full Outer
• Vnitřní - Inner
• Levé anti - Left Anti
• Pravá anti - Right Anti

Levé vnější - Left Outer (duplicitní záznamy)

Všechny záznamy z první tabulky a odpovídající záznamy z druhé tabulky. Tj Id 4 se objevuje 2x, stejně jako ID 6, kdežto ID 5 se objevuje 4x protože 2 x 2 jsou 4.

Jak bude sloučení vypadat...

Pravé vnější - Right Outer (duplicitní záznamy)

Všechny záznamy z druhé tabulky a odpovídající z první tabulky. Vlastně opak předchozího

Úplné vnější - Full Outer (duplicitní záznamy)

Všechny řádky z obou tabulek.

Vnitřní - Inner (duplicitní záznamy)

pouze odpovídající řádky z první a druhé tabulky, na základě ID

Levé anti - Left Anti (duplicitní záznamy)

Řádky které jsou jen v první tabulce a nemají záznam ve druhé tabulce.

Pravá anti - Right Anti (duplicitní záznamy)

Řádky které jsou jen ve druhé tabulce a nemají záznam v první tabulce.

Jiné možnosti sloučení

Další možností jak sloučit tabulky, nebo data v tabulkách:

Excel

• SVYHLEDAT (VLOOKUP) funkce
• Datový model
• Power Pivot
• ...

Power BI

• Model
• RELADET a RELADETTABLE
• ...

Což přesahuje prostor, který jsme pro slučování vyčlenil. Samozdřejmě zmíním v samostatných článcích.

Závěrem

Narazili jste na nějaký problém/ nejasnost? Máte tip na doplnění, můžete to zmínit v komentářích.

Článek byl aktualizován: 10.01.2022 15:30

Pavel Lasák - autor webu

Microsoft Office (Word, Excel, Google tabulky, PowerPoint) se věnuji od roku 2000 (od 2004 na této doméně) - V roce 2017 jsem od Microsoft získal prestižní ocenění MVP (zatím jsem jediný z ČR v kategorií Excel). Své vědomosti a zkušenosti dávám k dispozici i on-line ve videích pro SEDUO. Ve firmách školím a konzultuji, učím na MUNI. Tento web již tvořím přes 15 let. Zdarma je zde přes 1.000 návodu, tipů a triků, včetně přes 250 různých šablon, sešitů.

           

Komentáře zatím nejsou

Můžete být prvními co zanechají smysluplný komentář.