Gegevens importeren uit een map met meerdere bestanden (Power Query)

Gebruik Power Query om meerdere bestanden te combineren met hetzelfde schema dat is opgeslagen in één map in één tabel. U wilt bijvoorbeeld elke maand budgetwerkmappen van meerdere afdelingen combineren, waarbij de kolommen hetzelfde zijn, maar het aantal rijen en waarden in elke werkmap verschilt. Zodra u deze hebt ingesteld, kunt u aanvullende transformaties toepassen zoals u zou doen met elke geïmporteerde gegevensbron en vervolgens de gegevens vernieuwen om de resultaten voor elke maand weer te geven.

Een conceptueel overzicht van het combineren van mapbestanden

Opmerking In dit onderwerp wordt beschreven hoe u bestanden uit een map kunt combineren. U kunt ook bestanden combineren die zijn opgeslagen in SharePoint, Azure Blob Storage en Azure Data Lake Storage. Het proces is vergelijkbaar.

Nieuwere versies Office 2013

Houd het eenvoudig:

Zorg ervoor dat alle bestanden die u wilt combineren zich bevinden in een toegewezen map zonder overbodige bestanden. Anders worden alle bestanden in de map en eventuele submappen die u selecteert, opgenomen in de gegevens die moeten worden gecombineerd.
Elk bestand moet hetzelfde schema hebben met consistente kolomkoppen, gegevenstypen en het aantal kolommen. De kolommen hoeven niet in dezelfde volgorde te staan als de overeenkomende kolomnamen.
Vermijd indien mogelijk niet-gerelateerde gegevensobjecten voor gegevensbronnen die meer dan één gegevensobject kunnen hebben, zoals een JSON-bestand, een Excel-werkmap of Een Access-database.

Elk van deze bestanden volgt een eenvoudig patroon, slechts één tabel met gegevens in elk bestand.

Selecteer Gegevens > Gegevens ophalen > uit bestand > uit map. Het dialoogvenster Bladeren wordt weergegeven.
Zoek de map met de bestanden die u wilt combineren.
Er wordt een lijst met bestanden in de map weergegeven in het dialoogvenster <Mappad>. Controleer of alle gewenste bestanden worden vermeld.
Selecteer een van de opdrachten onderaan het dialoogvenster, bijvoorbeeld Combineren > Combineren & Laden. Er worden aanvullende opdrachten besproken in de sectie Over al deze opdrachten.
Als u een opdracht Combineren selecteert, wordt het dialoogvenster Bestanden combineren weergegeven. Als u bestandsinstellingen wilt wijzigen, selecteert u elk bestand in het vak Voorbeeldbestand en stelt u de bestandsoorsprong, het scheidingsteken en de detectie van gegevenstypen naar wens in. U kunt ook het selectievakje Bestanden met fouten overslaan onder aan het dialoogvenster in- of uitschakelen.
Selecteer OK.

Resultaat

Power Query maakt automatisch query's om de gegevens van elk bestand te consolideren in een werkblad. De querystappen en kolommen die worden gemaakt, zijn afhankelijk van de opdracht die u kiest. Zie de sectie Over al deze query's voor meer informatie.

Selecteer Gegevens > Gegevens ophalen > uit bestand > uit map. Het dialoogvenster Bladeren wordt weergegeven.
Zoek de map met de bestanden die u wilt combineren.
Er wordt een lijst met bestanden in de map weergegeven in het dialoogvenster <Mappad>. Controleer of alle gewenste bestanden worden vermeld.
Selecteer een van de opdrachten onderaan het dialoogvenster, bijvoorbeeld Combineren > Combineren & Transformeren. Er worden aanvullende opdrachten besproken in de sectie Over al deze opdrachten.

De Power Query-editor wordt weergegeven.
De kolom Waarde is een gestructureerde lijstkolom . Selecteer het pictogram uitvouwen en selecteer vervolgens Uitvouwen naar nieuwe rijen.
De kolom Waarde is nu een gestructureerde recordkolom . Selecteer het pictogram uitvouwen . Er wordt een vervolgkeuzedialoogvenster weergegeven.
Houd alle kolommen geselecteerd. U kunt het selectievakje Oorspronkelijke kolomnaam als voorvoegsel gebruiken uitschakelen. Selecteer OK.
Selecteer alle kolommen die gegevenswaarden bevatten. Selecteer Start, de pijl naast Kolommen verwijderen en selecteer vervolgens Andere kolommen verwijderen.
Selecteer Start > & Laden sluiten.

Resultaat

Elk van deze gegevensbronnen kan meer dan één object hebben om te importeren. Een Excel-werkmap kan meerdere werkbladen, Excel-tabellen of benoemde bereiken bevatten. Een Access-database kan meerdere tabellen en query's bevatten.

Selecteer Gegevens > Gegevens ophalen > uit bestand > uit map. Het dialoogvenster Bladeren wordt weergegeven.
Zoek de map met de bestanden die u wilt combineren.
Er wordt een lijst met bestanden in de map weergegeven in het dialoogvenster <Mappad>. Controleer of alle gewenste bestanden worden vermeld.
Selecteer een van de opdrachten onderaan het dialoogvenster, bijvoorbeeld Combineren > Combineren & Laden. Er worden aanvullende opdrachten besproken in de sectie Over al deze opdrachten.
In het dialoogvenster Bestanden combineren :
- Selecteer in het vak Voorbeeldbestand een bestand dat u wilt gebruiken als voorbeeldgegevens die worden gebruikt om de query's te maken. U kunt geen object selecteren of slechts één object selecteren. U kunt er echter niet meer dan één selecteren.
- Als u veel objecten hebt, gebruikt u het zoekvak om een object te zoeken of de weergaveopties samen met de knop Vernieuwen om de lijst te filteren.
- Schakel het selectievakje Bestanden met fouten overslaan onder aan het dialoogvenster in of uit.
Selecteer OK.

Resultaat

Power Query maakt automatisch een query om de gegevens van elk bestand te consolideren in een werkblad. De querystappen en kolommen die worden gemaakt, zijn afhankelijk van de opdracht die u kiest. Zie de sectie Over al deze query's voor meer informatie.

Voor meer flexibiliteit kunt u bestanden in de Power Query-editor expliciet combineren met behulp van de opdracht Bestanden combineren. Stel dat de bronmap een combinatie van bestandstypen en submappen bevat en dat u specifieke bestanden met hetzelfde bestandstype en schema wilt targeten, maar niet met andere. Dit kan de prestaties verbeteren en uw transformaties vereenvoudigen.

Selecteer Gegevens > Gegevens ophalen > uit bestand > uit map. Het dialoogvenster Bladeren wordt weergegeven.
Zoek de map met de bestanden die u wilt combineren en selecteer openen.
Een lijst met alle bestanden in de map en submappen wordt weergegeven in het <Mappad> dialoogvenster. Controleer of alle gewenste bestanden worden vermeld.
Selecteer Gegevens transformeren onderaan. De Power Query-editor wordt geopend en alle bestanden in de map en eventuele submappen worden weergegeven.
Als u de gewenste bestanden wilt selecteren, filtert u kolommen, zoals Extensie of Mappad.
Als u de bestanden in één tabel wilt combineren, selecteert u de kolom Inhoud met elk binair bestand (meestal de eerste kolom) en selecteert u vervolgens Start > Bestanden combineren. Het dialoogvenster Bestanden combineren wordt weergegeven.
Power Query analyseert een voorbeeldbestand, standaard het eerste bestand in de lijst, om de juiste connector te gebruiken en overeenkomende kolommen te identificeren.

Als u een ander bestand wilt gebruiken voor het voorbeeldbestand, selecteert u het in de vervolgkeuzelijst Voorbeeldbestand .
Selecteer eventueel onderaan bestanden overslaan met fouts om deze bestanden uit te sluiten van het resultaat.
Selecteer OK.

Resultaat

Er zijn verschillende opdrachten die u kunt selecteren en elke opdracht heeft een ander doel.

Gegevens combineren en transformeren Als u alle bestanden wilt combineren met een query en vervolgens de Power Query-editor wilt starten, selecteert u Combineren > Gegevens combineren en transformeren.
Combineren en laden Als u het dialoogvenster Voorbeeldbestand wilt weergeven, een query wilt maken en vervolgens in het werkblad wilt laden, selecteert u Combineren > Combineren en laden.
Combineren en laden naar Als u het dialoogvenster Voorbeeldbestand wilt weergeven, maakt u een query en vervolgens het dialoogvenster Importeren , selecteert u Combineren > Combineren en Laden naar.
Laden Als u met één stap een query wilt maken en vervolgens naar een werkblad wilt laden, selecteert u Laden > Laden.
Laden naar Als u met één stap een query wilt maken en vervolgens het dialoogvenster Importeren wilt weergeven, selecteert u Laden > Laden naar.
Gegevens transformerenAls u in één stap een query wilt maken en vervolgens de Power Query-editor wilt starten, selecteert u Gegevens transformeren.

Hoe u bestanden ook combineert, er worden verschillende ondersteunende query's gemaakt in het deelvenster Query's onder de groep Helperquery's.

Een lijst met de query's die zijn gemaakt in het deelvenster Query's

Power Query maakt een 'Voorbeeldbestand'-query op basis van de voorbeeldquery.
Een functiequery 'Bestand transformeren' gebruikt de query Parameter1 om elk bestand (of binair) op te geven als invoer voor de query 'Voorbeeldbestand'. Met deze query wordt ook de kolom Inhoud gemaakt die de bestandsinhoud bevat en wordt de gestructureerde kolom Record automatisch uitgebreid om de kolomgegevens toe te voegen aan de resultaten. De query's Bestand transformeren en Voorbeeldbestand zijn gekoppeld, zodat wijzigingen in de query 'Voorbeeldbestand' worden weergegeven in de query Bestand transformeren.
De query met de uiteindelijke resultaten bevindt zich in de groep Overige query's. Deze is standaard vernoemd naar de map waaruit u de bestanden hebt geïmporteerd.

Klik voor verder onderzoek met de rechtermuisknop op elke query en selecteer Bewerken om elke querystap te bekijken en om te zien hoe de query's samenwerken.

U start het combineren van meerdere bestanden door er eerst voor te zorgen dat alle bestanden die u wilt combineren, zich in één map bevinden.

Opmerking: Excel-, tekst-, CSV-, JSON-, XML- en Access-bestanden worden ondersteund.

Klik op Power Query tabblad en klik vervolgens op Van bestand > Uit map.
Klik op de knop Bladeren om de map te zoeken.

Er wordt een lijst met de bestanden in de map weergegeven. Selecteer de optie Bewerken om de queryeditor te openen. In dit geval hebben we vier Excel-werkmappen geladen.
Nadat u hebt gecontroleerd of alle gewenste bestanden worden weergegeven, klikt u op de knop Binaire gegevens combineren vanuit de kolom Inhoud.
Elk bestand wordt geanalyseerd en de juiste bestandsindeling wordt bepaald, zoals tekst, Excel of JSON. In dit geval wordt een lijst met werkbladen weergegeven in de eerste Excel-werkmap. Selecteer het werkblad dat u wilt gebruiken, en klik op OK.
Het transformatieproces voor het combineren van binaire gegevens maakt vervolgens automatisch een query om de gegevens van elk bestand samen te voegen en een voorbeeld weer te geven. Als u tevreden bent met het resultaat, klikt u op Sluiten en laden.

Het proces voor het combineren van binaire gegevens voegt vervolgens de weergegeven werkbladen samen tot één werkblad.
Als de oorspronkelijke gegevensbestanden worden gewijzigd, kunt u altijd de geïmporteerde gegevens vernieuwen. Klik ergens in het gegevensbereik en ga naar Hulpmiddelen voor query's > Vernieuwen. Bovendien kunt u eenvoudig extra transformatie- of extractiestappen toepassen door de automatisch gemaakte exemplar-query te wijzigen zonder dat u zich zorgen hoeft te maken over het wijzigen of maken van extra functiequerystappen; wijzigingen in de exemplar-query worden automatisch gegenereerd in de gekoppelde functiequery.

Bekende problemen

Bij het laden van de procedure voor het combineren van binaire gegevens naar Excel wordt mogelijk het volgende foutbericht weergegeven:

Foutbericht Binaire bestanden combineren. Dit is een bekende fout die wordt opgelost.

Als u de fout(en) negeert door op OK te klikken, wordt het proces voor het combineren van binaire gegevens voltooid en worden de gegevens ondanks de fout correct geladen. Wij werken aan een oplossing voor de fout.