Combineer gegevens uit meerdere werkmappen in Excel (met Power Query)

Power Query kan een grote hulp zijn wanneer u meerdere werkmappen in één werkmap wilt combineren.

Stel dat u de verkoopgegevens voor verschillende regio's hebt (Oost, West, Noord en Zuid). U kunt deze gegevens uit verschillende werkmappen combineren in één werkblad met Power Query.

Als u deze werkmappen op verschillende locaties/mappen hebt, is het een goed idee om ze allemaal naar een enkele map te verplaatsen (of een kopie te maken en die werkmapkopie in dezelfde map te plaatsen).

Dus om te beginnen heb ik vier werkmappen in een map (zoals hieronder weergegeven).

In deze zelfstudie behandel ik drie scenario's waarin u de gegevens uit verschillende werkmappen kunt combineren met Power Query:

• Elke werkmap heeft de gegevens in een Excel-tabel en alle tabelnamen zijn hetzelfde.
• Elke werkmap heeft de gegevens met dezelfde werkbladnaam. Dit kan het geval zijn wanneer er in alle werkmappen een blad met de naam 'samenvatting' of 'gegevens' staat en u deze allemaal wilt combineren.
• Elke werkmap heeft veel werkbladen en tabellen en u wilt specifieke tabellen/bladen combineren. Deze methode kan ook handig zijn als u tabellen/bladen wilt combineren die geen vaste naam hebben.

Laten we eens kijken hoe we in elk geval gegevens uit deze werkmappen kunnen combineren.

Elke werkmap heeft de gegevens in een Excel-tabel met dezelfde structuur

De onderstaande techniek zou werken als uw Excel-tabellen op dezelfde manier zijn gestructureerd (dezelfde kolomnamen).

Het aantal rijen in elke tabel kan variëren.

Maakt u zich geen zorgen als sommige Excel-tabellen extra kolommen hebben. U kunt een van de tabellen kiezen als sjabloon (of als de 'sleutel' zoals Power Query het noemt), en Power Query zou het gebruiken om alle andere Excel-tabellen ermee te combineren.

Als er extra kolommen in andere tabellen zijn, worden deze genegeerd en worden alleen de kolommen die in de sjabloon/sleutel zijn gespecificeerd, gecombineerd. Als de sjabloon/sleuteltabel die u selecteert bijvoorbeeld 5 kolommen heeft en een van de tabellen in een andere werkmap heeft 2 extra kolommen, dan worden die extra kolommen genegeerd.

Nu heb ik vier werkmappen in een map die ik wil combineren.

Hieronder ziet u een momentopname van de tabel die ik in een van de werkmappen heb.

Hier zijn de stappen om de gegevens uit deze werkmappen te combineren in een enkele werkmap (als een enkele tabel).

1. Ga naar het tabblad Gegevens.
2. Klik in de groep Ophalen en transformeren op de vervolgkeuzelijst Nieuwe query.
3. Beweeg uw cursor over 'Van bestand' en klik op 'Van map'.
4. Voer in het dialoogvenster Map het bestandspad in van de map die de bestanden bevat, of klik op Bladeren en zoek de map.
5. Klik OK.
6. Klik in het dialoogvenster dat wordt geopend op de knop Combineren.
7. Klik op ‘Combineren & Laden’.
8. Selecteer in het dialoogvenster 'Bestanden combineren' dat wordt geopend de tabel in het linkerdeelvenster. Houd er rekening mee dat Power Query u de tabel uit het eerste bestand laat zien. Dit bestand zou fungeren als de sjabloon (of de sleutel) om andere bestanden te combineren. Power Query zou nu in andere werkmappen zoeken naar 'Tabel 1' en deze combineren met deze.
9. Klik OK.

Hiermee wordt het eindresultaat (gecombineerde gegevens) in uw actieve werkblad geladen.

Houd er rekening mee dat Power Query samen met de gegevens automatisch de naam van de werkmap toevoegt als de eerste kolom van de gecombineerde gegevens. Dit helpt bij het bijhouden van welke gegevens uit welke werkmap komen.

Als u de gegevens eerst wilt bewerken voordat u deze in Excel laadt, selecteert u in stap 6 'Combineren en bewerken'. Dit opent het eindresultaat in de Power Query-editor waar u de gegevens kunt bewerken.

Een paar dingen om te weten:

• Als u een Excel-tabel als sjabloon selecteert (in stap 7), gebruikt Power Query de kolomnamen in deze tabel om de gegevens uit andere tabellen te combineren. Als andere tabellen extra kolommen hebben, worden deze genegeerd. Als die andere tabellen geen kolom hebben, die in uw sjabloontabel staat, zou Power Query er gewoon 'null' voor plaatsen.
• De kolommen hoeven niet in dezelfde volgorde te staan, aangezien Power Query kolomkoppen gebruikt om kolommen toe te wijzen.
• Aangezien u Tabel1 als sleutel hebt geselecteerd, zoekt Power Query naar Tabel1 in alle werkmappen en combineert deze allemaal. Als er geen Excel-tabel met dezelfde naam wordt gevonden (Tabel1 in dit voorbeeld), geeft Power Query een foutmelding.

Nieuwe bestanden aan de map toevoegen

Laten we nu even de tijd nemen om te begrijpen wat we met de bovenstaande stappen hebben gedaan (wat ons maar een paar seconden kostte).

We hebben de gegevens van vier verschillende werkmappen in een paar seconden in één enkele tabel gecombineerd zonder zelfs maar een van de werkmappen te openen.

Maar dat is niet alles.

De echte KRACHT van Power Query is dat wanneer u nu meer bestanden aan de map toevoegt, u geen van deze stappen hoeft te herhalen.

U hoeft alleen maar de nieuwe werkmap naar de map te verplaatsen, de query te vernieuwen en de gegevens van alle werkmappen in die map worden automatisch gecombineerd.

Als ik in het bovenstaande voorbeeld bijvoorbeeld een nieuwe werkmap toevoeg - ‘Mid-West.xlsx’ naar de map en vernieuw de query, dan krijg ik meteen de nieuwe gecombineerde dataset.

Zo vernieuw je een query:

• Klik met de rechtermuisknop op de Excel-tabel die u in het werkblad hebt geladen en klik op Vernieuwen.
• Klik met de rechtermuisknop op de query in het deelvenster 'Werkmapquery' en klik op Vernieuwen
• Ga naar het tabblad Gegevens en klik op Vernieuwen.

Elke werkmap heeft de gegevens met dezelfde werkbladnaam

Als u de gegevens niet in een Excel-tabel hebt, maar alle bladnamen (waarvan u de gegevens wilt combineren) hetzelfde zijn, kunt u de methode gebruiken die in deze sectie wordt getoond.

Er zijn een paar dingen waar u voorzichtig mee moet zijn als het alleen gegevens in tabelvorm zijn en geen Excel-tabel.

• De werkbladnamen moeten hetzelfde zijn. Dit helpt Power Query om uw werkmappen te doorlopen en de gegevens te combineren van de werkbladen met dezelfde naam in elke werkmap.
• Power Query is hoofdlettergevoelig. Dit betekent dat een werkblad met de naam 'data' en 'Data' als verschillend wordt beschouwd. Evenzo worden een kolom met de kop 'Store' en een kolom met 'store' als verschillend beschouwd.
• Hoewel het belangrijk is om dezelfde kolomkoppen te hebben, is het niet belangrijk om dezelfde volgorde te hebben. Als kolom 2 in de 'East.xlsx' kolom 4 is in 'West.xlsx', zal Power Query deze correct matchen door de koppen toe te wijzen.

Laten we nu eens kijken hoe u snel gegevens uit verschillende werkmappen kunt combineren waarvan de werkbladnaam hetzelfde is.

In dit voorbeeld heb ik een map met vier bestanden.

In elke werkmap heb ik een werkblad met de naam 'Data' dat de gegevens in het volgende formaat bevat (merk op dat dit geen Excel-tabel is).

Dit zijn de stappen om gegevens uit meerdere werkmappen te combineren in één enkel werkblad:

1. Ga naar het tabblad Gegevens.
2. Klik in de groep Ophalen en transformeren op de vervolgkeuzelijst Nieuwe query.
3. Beweeg uw cursor over 'Van bestand' en klik op 'Van map'.
4. Voer in het dialoogvenster Map het bestandspad in van de map met de bestanden, of klik op Bladeren en zoek de map.
5. Klik OK.
6. Klik in het dialoogvenster dat wordt geopend op de knop Combineren.
7. Klik op ‘Combineren & Laden’.
8. In het dialoogvenster 'Bestanden combineren' dat wordt geopend, selecteert u 'Gegevens' in het linkerdeelvenster. Houd er rekening mee dat Power Query u de werkbladnaam uit het eerste bestand laat zien. Dit bestand zou fungeren als de sleutel/sjabloon om andere bestanden te combineren. Power Query doorloopt elke werkmap, vindt het blad met de naam 'Gegevens' en combineert deze allemaal.
9. Klik OK. Nu doorloopt Power Query elke werkmap, zoekt daarin naar het werkblad met de naam 'Gegevens' en combineert vervolgens al deze gegevenssets.

Hiermee wordt het eindresultaat (gecombineerde gegevens) in uw actieve werkblad geladen.

Als u de gegevens eerst wilt bewerken voordat u deze in Excel laadt, selecteert u in stap 6 'Combineren en bewerken'. Dit opent het eindresultaat in de Power Query-editor waar u de gegevens kunt bewerken.

Elke werkmap heeft de gegevens met verschillende tabelnamen of bladnamen

Soms krijgt u geen gestructureerde en consistente gegevens (zoals tabellen met dezelfde naam of werkblad met dezelfde naam).

Stel dat u de gegevens krijgt van iemand die deze gegevenssets heeft gemaakt, maar de werkbladen de naam Oost-gegevens, West-gegevens, Noord-gegevens en Zuid-gegevens heeft gegeven.

Of de persoon heeft mogelijk Excel-tabellen gemaakt, maar met verschillende namen.

In dergelijke gevallen kunt u nog steeds Power Query gebruiken, maar u moet dit met een paar extra stappen doen.

1. Ga naar het tabblad Gegevens.
2. Klik in de groep Ophalen en transformeren op de vervolgkeuzelijst Nieuwe query.
3. Beweeg uw cursor over 'Van bestand' en klik op 'Van map'.
4. Voer in het dialoogvenster Map het bestandspad in van de map die de bestanden bevat, of klik op Bladeren en zoek de map.
5. Klik OK.
6. Klik in het dialoogvenster dat wordt geopend op de knop Bewerken. Hiermee wordt de Power Query-editor geopend, waar u de details van alle bestanden in de map ziet.
7. Houd de Control-toets ingedrukt en selecteer de kolommen 'Inhoud' en 'Naam', klik met de rechtermuisknop en selecteer 'Andere kolommen verwijderen'. Hiermee worden alle andere kolommen verwijderd, behalve de geselecteerde kolommen.
8. Klik in het lint van de Query-editor op 'Kolom toevoegen' en vervolgens op 'Aangepaste kolom'.
9. Geef in het dialoogvenster Aangepaste kolom toevoegen de nieuwe kolom de naam 'Gegevens importeren' en gebruik de volgende formule: =Excel.Werkmap([INHOUD]). Merk op dat deze formule hoofdlettergevoelig is en dat u deze precies moet invoeren zoals ik hier heb laten zien.
10. Nu ziet u een nieuwe kolom waarin Tabel is geschreven. Laat me nu uitleggen wat hier is gebeurd. U hebt Power Query de namen van de werkmappen gegeven en Power Query heeft de objecten zoals werkbladen, tabellen en benoemde bereiken uit elke werkmap opgehaald (die zich vanaf nu in de tabelcel bevindt). U kunt op de witte ruimte naast de tekst Tabel klikken en u ziet de informatie onderaan. In dit geval, aangezien we slechts één tabel en één werkblad in elke werkmap hebben, ziet u slechts twee rijen.
11. Klik op het pictogram met de dubbele pijl bovenaan de kolom 'Gegevens importeren'.
12. Schakel in het kolomgegevensvak dat wordt geopend het selectievakje 'Oorspronkelijke kolom als voorvoegsel gebruiken' uit en klik vervolgens op OK.
13. Nu ziet u een uitgevouwen tabel waarin u één rij ziet voor elk object in de tabel. In dit geval worden voor elke werkmap het werkbladobject en het tabelobject afzonderlijk vermeld.
14. Filter in de kolom Soort de lijst om alleen de tabel weer te geven.
15. Houd de Control-toets ingedrukt en selecteer de kolom Naam en Gegevens. Klik nu met de rechtermuisknop en verwijder alle andere kolommen.
16. Klik in de kolom Gegevens op het pictogram met de dubbele pijl in de rechterbovenhoek van de gegevenskop.
17. Klik in het kolomgegevensvak dat wordt geopend op OK. Hiermee worden de gegevens in alle tabellen gecombineerd en weergegeven in Power Query.
18. Nu kunt u elke gewenste transformatie maken en vervolgens naar het tabblad Start gaan en op Sluiten en laden klikken.

Laat me nu proberen snel uit te leggen wat we hier hebben gedaan. Omdat er geen consistentie was in de bladnamen of tabelnamen, hebben we de formule =Excel.Workbook gebruikt om alle objecten van de werkmappen in de Power Query op te halen. Deze objecten kunnen werkbladen, tabellen en benoemde bereiken bevatten. Nadat we alle objecten uit alle bestanden hadden, hebben we deze gefilterd om alleen Excel-tabellen te beschouwen. Vervolgens hebben we de gegevens in de tabellen uitgebreid en gecombineerd.

In dit voorbeeld hebben we de gegevens gefilterd om alleen Excel-tabellen te gebruiken (in stap 13). Als u bladen wilt combineren en geen tabellen, kunt u bladen filteren.

Opmerking: deze techniek geeft u de gecombineerde gegevens, zelfs als de kolomnamen niet overeenkomen. Als u bijvoorbeeld in East.xlsx een kolom hebt die verkeerd is gespeld, krijgt u 5 kolommen. Power Query vult gegevens in kolommen als het deze vindt, en als het geen kolom kan vinden, rapporteert het de waarde als 'null'.

Evenzo, als u enkele extra kolommen in een van de werkbladen van de tabellen heeft, worden deze opgenomen in het eindresultaat.

Als u nu meer werkmappen krijgt waaruit u gegevens moet combineren, kopieert u deze eenvoudig naar de map en vernieuwt u de Power Query