Tabellen samenvoegen in Excel met Power Query (eenvoudige stapsgewijze handleiding)

Met Power Query is het eenvoudiger geworden om te werken met gegevens verspreid over werkbladen of zelfs werkmappen.

Een van de dingen waar Power Query u veel tijd kan besparen, is wanneer u tabellen met verschillende formaten en kolommen moet samenvoegen op basis van een overeenkomende kolom.

Hieronder ziet u een video waarin ik precies laat zien hoe u tabellen in Excel samenvoegt met Power Query.

Als u liever de tekst leest dan een video bekijkt, vindt u hieronder de schriftelijke instructies.

Stel dat u een tabel heeft zoals hieronder weergegeven:

Deze tabel bevat de gegevens die ik wil gebruiken, maar er ontbreken nog steeds twee belangrijke kolommen: de 'Product-ID' en de 'Regio' waar de verkoper actief is.

Deze informatie wordt geleverd als afzonderlijke tabellen, zoals hieronder weergegeven:

Om al deze informatie in één tabel te krijgen, moet u deze drie tabellen samenvoegen, zodat u vervolgens een draaitabel kunt maken en deze kunt analyseren, of deze kunt gebruiken voor andere rapportage-/dashboarddoeleinden.

En met samenvoegen bedoel ik niet een simpele copy-paste.

U moet de relevante records uit Tabel 1 in kaart brengen met gegevens uit Tabel 2 en 3.

Nu kunt u vertrouwen op VERT.ZOEKEN of INDEX/MATCH om dit te doen.

Of als je een VBA-fan bent, kun je een code schrijven om dit te doen.

Maar deze opties zijn tijdrovend en ingewikkeld in vergelijking met Power Query.

In deze zelfstudie laat ik u zien hoe u deze drie Excel-tabellen kunt samenvoegen tot één.

Om deze techniek te laten werken, moet u verbindingskolommen hebben. In Tabel 1 en Tabel 2 is de gemeenschappelijke kolom bijvoorbeeld 'Artikel' en in Tabel 1 en Tabel 3 is de gemeenschappelijke kolom 'Verkoopvertegenwoordiger'. Merk ook op dat er geen herhaling mag zijn in deze verbindingskolommen.

Opmerking: Power Query kan worden gebruikt als invoegtoepassing in Excel 2010 en 2013 en is een ingebouwde functie vanaf Excel 2016. Op basis van uw versie kunnen sommige afbeeldingen er anders uitzien (afbeeldingen die in deze zelfstudie worden gebruikt, zijn afkomstig uit Excel 2016).

Tabellen samenvoegen met Power Query

Ik heb deze tabellen benoemd zoals hieronder weergegeven:

1. Tabel 1 - Verkoopdata
2. Tafel 2 - Pdt_Id
3. Tafel 3 - Regio

Het is niet verplicht om deze tabellen te hernoemen, maar het is beter om namen te geven die beschrijven waar de tabel over gaat.

U kunt in Power Query slechts twee tabellen in één keer samenvoegen.

We zullen dus eerst Tabel 1 en Tabel 2 moeten samenvoegen en vervolgens Tabel 3 erin samenvoegen in de volgende stap.

Tabel 1 en Tabel 2 samenvoegen

Als u tabellen wilt samenvoegen, moet u deze tabellen eerst converteren naar verbindingen in Power Query. Als je eenmaal de verbindingen hebt, kun je deze eenvoudig samenvoegen.

Dit zijn de stappen om een ​​Excel-tabel op te slaan als verbinding in Power Query:

1. Selecteer een cel in de tabel Sales_Data.
2. Klik op het tabblad Gegevens.
3. Klik in de groep Ophalen en transformeren op 'Van tabel/bereik'. Hiermee wordt de Query-editor geopend.
4. Klik in de Query-editor op het tabblad 'Bestand'.
5. Klik op de optie 'Sluiten en laden naar'.
6. Selecteer in het dialoogvenster 'Gegevens importeren' de optie 'Alleen verbinding maken'.
7. Klik OK.

De bovenstaande stappen zouden een verbinding tot stand brengen met de naam Sales_Data (of een andere naam die u aan de Excel-tabel hebt gegeven).

Herhaal de bovenstaande stappen voor Tabel 2 en Tabel 3.

Dus als je klaar bent, heb je drie connecties (met de naam Sales_Data, Pdt_Id en Region).

Laten we nu eens kijken hoe we de tabel Sales_Data en Pdt_Id kunnen samenvoegen.

1. Klik op het tabblad Gegevens.
2. Klik in de groep Gegevens ophalen en transformeren op Gegevens ophalen.
3. Klik in de vervolgkeuzelijst op Query's combineren.
4. Klik op Samenvoegen. Dit opent het dialoogvenster Samenvoegen.
5. Selecteer in het dialoogvenster Samenvoegen 'Sales_Data' in de eerste vervolgkeuzelijst.
6. Selecteer 'Pdt_Id' in de tweede vervolgkeuzelijst.
7. Klik in het voorbeeld 'Sales_Data' op de kolom 'Artikel'. Als u dit doet, wordt de hele kolom geselecteerd.
8. Klik in het voorbeeld van 'Pdt_Id' op de kolom 'Item'. Als u dit doet, wordt de hele kolom geselecteerd.
9. Selecteer in de vervolgkeuzelijst 'Join Kind' de optie 'Left Outer (all from first, matching from second)'.
10. Klik OK.

De bovenstaande stappen zouden de Query-editor openen en u de gegevens van de Sales_Data tonen met één extra kolom (van Pdt_Id).

De Excel-tabellen samenvoegen (Tabel 1 & 2)

Nu zal het proces van het samenvoegen van de tabellen plaatsvinden in de Query-editor met de volgende stappen:

1. Klik in de extra kolom (Pdt_Id) op de dubbele pijl in de kop.
2. Schakel in het optievak dat wordt geopend alle kolomnamen uit en selecteer alleen Item. Dit komt omdat we de productnaamkolom al in de bestaande tabel hebben en we alleen de product-ID voor elk product willen.
3. Vink de optie ‘Oorspronkelijke kolomnaam gebruiken als voorvoegsel’ uit.
4. Klik OK.

Dit zou u de resulterende tabel geven met elk record uit de Sales_Data-tabel en een extra kolom die ook product-ID's heeft (uit de Pdt_Id-tabel).

Als u nu slechts twee tabellen wilt combineren, kunt u deze Excel laden en bent u klaar.

Maar we hebben drie tabellen om samen te voegen, dus er is meer werk aan de winkel.

U moet deze resulterende tabel opslaan als een verbinding (zodat we deze kunnen gebruiken om deze samen te voegen met Tabel 3).

Hier zijn de stappen om deze samengevoegde tabel (met gegevens van Sales_Data en Pdt_Id tabel) als een verbinding op te slaan:

1. Klik op het tabblad Bestand
2. Klik op de optie 'Sluiten en laden naar'.
3. Selecteer in het dialoogvenster 'Gegevens importeren' de optie 'Alleen verbinding maken'.
4. Klik OK.

Hiermee worden de nieuw samengevoegde gegevens als een verbinding opgeslagen. U kunt deze verbinding desgewenst een andere naam geven.

Tabel 3 samenvoegen met de resulterende tabel

Het proces van het samenvoegen van de derde tabel met de resulterende tabel (die we hebben gekregen door Tabel 1 en Tabel 2) samen te voegen is precies hetzelfde.

Dit zijn de stappen om deze tabellen samen te voegen:

1. Klik op het tabblad Gegevens.
2. Klik in de groep Gegevens ophalen en transformeren op 'Gegevens ophalen'.
3. Klik in de vervolgkeuzelijst op 'Query's combineren'.
4. Klik op 'Samenvoegen'. Dit opent het dialoogvenster Samenvoegen.
5. Selecteer in het dialoogvenster Samenvoegen 'Samenvoegen1' in de eerste vervolgkeuzelijst.
6. Selecteer 'Regio' in de tweede vervolgkeuzelijst.
7. Klik in het voorbeeld van 'Samenvoegen1' op de kolom 'Verkoopvertegenwoordiger'. Als u dit doet, wordt de hele kolom geselecteerd.
8. Klik in Regiovoorbeeld op de kolom 'Verkoopvertegenwoordiger'. Als u dit doet, wordt de hele kolom geselecteerd.
9. Selecteer in de vervolgkeuzelijst 'Join Kind' Left Outer (allemaal vanaf de eerste, overeenkomend met de tweede).
10. Klik OK.

De bovenstaande stappen zouden de Query-editor openen en u de gegevens van Merge1 tonen met één extra kolom (Regio).

Nu zal het proces van het samenvoegen van de tabellen plaatsvinden in de Query-editor met de volgende stappen:

1. Klik in de extra kolom (Regio) op de dubbele pijl in de kop.
2. Schakel in het optievak dat wordt geopend alle kolomnamen uit en selecteer alleen Regio.
3. Vink de optie ‘Oorspronkelijke kolomnaam gebruiken als voorvoegsel’ uit.
4. Klik OK.

De bovenstaande stappen geven u een tabel waarin alle drie de tabellen zijn samengevoegd (Sales_Data-tabel met één kolom voor Pdt_Id en één voor Region).

Dit zijn de stappen om deze tabel in Excel te laden:

1. Klik op het tabblad Bestand.
2. Klik op 'Sluiten en laden naar'.
3. Selecteer in het dialoogvenster 'Gegevens importeren' de opties Tabel en Nieuwe werkbladen.
4. Klik OK.

Dit zou u de resulterende samengevoegde tabel in een nieuw werkblad geven.

Een van de beste dingen van Power Query is dat u eenvoudig wijzigingen in de onderliggende gegevens (tabel 1, 2 en 3) kunt aanpassen door deze eenvoudig te vernieuwen.

Stel dat Laura wordt overgeplaatst naar Azië en u krijgt nieuwe gegevens voor de volgende maand. U hoeft de bovenstaande stappen nu niet opnieuw te herhalen. Het enige dat u hoeft te doen, is de tabel vernieuwen en hij doet alles opnieuw voor u.

Binnen enkele seconden heb je de nieuwe samengevoegde tabel.

Misschien vind je de volgende Power Query-zelfstudies misschien ook leuk:

• Combineer gegevens uit meerdere werkmappen in Excel (met Power Query).
• Combineer gegevens van meerdere werkbladen in een enkel werkblad in Excel.
• Gegevens in Excel ongedaan maken met Power Query (ook bekend als Get & Transform)
• Een lijst met bestandsnamen uit mappen en submappen ophalen (met Power Query)