Power Query: combineer bestanden uit meerdere mappen

Voor dit bericht gaan we werken in Power Query, bestanden combineren uit meerdere mappen in ons netwerk, desktop, OneDrive of SharePoint.

In dit voorbeeld hebben we drie CSV-bestanden in een map en de gegevens zien er als volgt uit:

We gebruiken Power Query in Excel, dat precies hetzelfde werkt als Power Query in LuckyTemplates.

Inhoudsopgave

• Verbinding maken met een map in een netwerk
• De gegevens opschonen
• Verbinding maken met een map in SharePoint of OneDrive
• Conclusie

Verbinding maken met een map in een netwerk

We beginnen met onze tutorial over power query, combineren bestanden uit meerdere mappen met een leeg Excel-bestand en gaan naar het tabblad gegevens, klikken op Gegevens ophalen , Uit bestand en vervolgens Uit map .

Zo maak je verbinding met een map die op je C-schijf of op je netwerk staat. Gebruik deze connector als u verbinding wilt maken met een bestand op SharePoint.

Gebruik het bladervenster om naar uw map te zoeken. Klik vervolgens op de knop Gegevens combineren en transformeren .

Vervolgens wordt ons gevraagd wat voor type CSV-bestand dit is. Klik op OK en als het niet werkt, ga dan terug en wijzig het in het juiste CSV-bestandstype.

We krijgen dit grote stappenblok in Power Query. Dit kan een beetje ontmoedigend en verwarrend zijn. Deze drie dossiers worden op elkaar gestapeld, en dat is niet wat we willen bereiken.

We klikken op het eerste bestand en klikken vervolgens op Voorbeeldbestand transformeren . Welke wijzigingen we ook aanbrengen in dit bestand, het wordt ook toegepast op de andere bestanden.

De gegevens opschonen

Laten we dit eerst opruimen. We willen dat deze datum voor elk record wordt ingevuld. Als Kolom1 het woord datum bevat, willen we de datum op de andere kolommen zetten en die datum invullen.

We voegen een voorwaardelijke kolom toe die zegt dat als Kolom1 gelijk is aan het woord datum, we de waarde uit Kolom2 willen. Laat het anders leeg.

Selecteer de datum- en null-kolom, klik met de rechtermuisknop en vul in.

Het resultaat is een kolom met alle datums.

Om de spaties en de nullen hier te verwijderen, klikt u op Remove Empty .

We willen de eerste rij niet als onze koppen gebruiken, maar we kunnen ook de knop Eerste rij als koppen gebruiken niet gebruiken, omdat deze de datums in de koppenrij duwt.

Als u dit doet, wordt de datum voor elk bestand gewijzigd. De eenvoudigste oplossing is dat we deze kolommen handmatig hernoemen.

Selecteer alle kolommen, klik met de rechtermuisknop en klik vervolgens op andere kolommen verwijderen .

Nadat we de subkoppen hebben bewerkt, zijn we nu zo goed als klaar.

Wanneer we op de DemoFolder klikken , krijgen we een foutmelding. We krijgen dit meestal wanneer we een kleine wijziging aanbrengen in het transformatievoorbeeldbestand.

Meestal wordt de fout veroorzaakt door deze stap Type gewijzigd , omdat deze probeert die oorspronkelijke Kolom1 te wijzigen, die niet meer bestaat.

Door deze stap eenvoudigweg te verwijderen, wordt de fout verholpen.

Alles wat we met dat eerste bestand hebben gedaan, wordt nu op elk bestand toegepast. Het is echt afhankelijk van onze bronbestanden die op dezelfde manier zijn gestructureerd. Het is niet onmogelijk als ze anders zijn, maar het wordt een stuk moeilijker.

Dus laten we terugkijken op deze vragen en uitzoeken wat er aan de hand is. Als we naar de bronstap van het voorbeeldbestand gaan, verwijst dit naar iets genaamd Parameter1 .

En als we naar links kijken, wordt Parameter1 het voorbeeldbestand genoemd.

En als we naar de bron van het voorbeeldbestand gaan, navigeren we naar record 0.

Power query is een op nul gebaseerde taal, wat betekent dat Record1 eigenlijk Item 0 is. Het is gewoon het eerste bestand pakken en dan worden de andere stappen automatisch geconverteerd, wat gewoon geweldig is. Vroeger moest u dit vroeger handmatig doen op het tabblad Bestand transformeren.

Als we dit uitbreiden, krijgt het een bestandsnaam en vervolgens een overzicht van alle stappen.

Als u de geavanceerde editor gaat gebruiken, krijgt u deze waarschuwing. We raden u aan dit niet te doen, omdat u ze daarna niet meer kunt herstellen. Voer in het algemeen gewoon al uw aanpassingen in het voorbeeldbestand uit.

Dus wat er in deze consolidatiestap gebeurde, is dat het naar de map ging en verborgen bestanden of systeembestanden eruit filterde. Daarna voegde het de aangeroepen aangepaste functie toe, hernoemde een paar kolommen en verwijderde vervolgens al het andere.

Het enige dat opvalt, is dat het de kolomtypen verliest. Welke kolomtypen u ook kiest in het transformatievoorbeeldbestand, wordt dus niet overgenomen door het consolidatiebestand.

We klikken dan op Sluiten en laden . Elke keer dat we op vernieuwen klikken, wordt door die map gescand.

Verbinding maken met een map in SharePoint of OneDrive

Als uw bestanden zich in een OneDrive- of een SharePoint-map bevinden, is het proces vrijwel hetzelfde, maar subtiel anders. U hebt het juiste pad nodig voor OneDrive, dat is de URL tot aan uw naam. We klikken op Gegevens ophalen en vervolgens op de SharePoint-map.

Het zal ons om een ​​URL vragen waar we de link kunnen plakken. Vervolgens wordt vrijwel elk bestand weergegeven dat u in OneDrive hebt. Zodra we Transform Data ingaan , moeten we een aantal verschillende stappen uitvoeren. Eerst moeten we de juiste map vinden, namelijk de DemoOneDriveFolder .

We klikken op Tekstfilter , vervolgens op Bevat en typen vervolgens DemoOneDrive in . Als we dit niet doen, kunnen we eeuwenlang scrollen en niet vinden wat we zoeken.

Dit proces filtert het vervolgens naar de juiste map. Zoals je kunt zien, hebben we de drie dossiers. Uitstekend.

We gaan naar de kolom Mappad , klikken met de rechtermuisknop, gaan naar Tekstfilter en kiezen Is gelijk aan .

Op deze manier kunnen we het juiste pad pakken in plaats van het bevat-gedeelte. Die laatste stap kunnen we gewoon weglaten omdat we die niet nodig hebben.

Een ander advies zou zijn om een ​​filter te maken waarbij het tekstfilter gelijk is aan CSV-bestanden - voor het geval er andere bestanden in terechtkomen.

De laatste aanbeveling is om deze query OneDriveFolder aan te roepen en er vervolgens naar te verwijzen. Het is handig wanneer u later probeert te debuggen en terug moet werken, of zelfs wanneer u de map moet wijzigen.

Dit zou complexer zijn dan het eerdere voorbeeld waaraan we hebben gewerkt. Voor deze hebben we alleen de kolommen Inhoud en Naam nodig .

We klikken met de rechtermuisknop om de andere kolommen te verwijderen.

Klik op de kleine dubbele pijl, die de bestanden zal combineren.

Vanaf dit punt is het proces identiek aan het vorige voorbeeld, waar ook de helperquery's werden gemaakt.

Even een korte opmerking: als u Excel-bestanden consolideert, zal een van de tussenliggende schermen die verschijnen, vragen welk blad u wilt consolideren. De bladen moeten echt dezelfde naam hebben, want als u meerdere Excel-bestanden consolideert, kan het rommelig worden.

Hier is ons getransformeerde voorbeeldbestand, allemaal netjes aan elkaar geknoopt, precies zoals we in het vorige voorbeeld deden.

En tot slot, hier is het consolidatiebestand dat naar buiten wordt geduwd en in het gegevensmodel wordt geladen.

En we hebben ook deze ene extra map met de naam OneDriveFolder . Vergeet ten slotte niet uw stappen een andere naam te geven met iets nuttigs en zinvols.

Power Query Best Practices voor uw gegevensmodel
Uw query's organiseren in groepen in LuckyTemplates

Conclusie

Voor deze zelfstudie hebben we gewerkt aan powerquery, bestanden combineren uit meerdere mappen van ons netwerk, desktop, OneDrive of SharePoint.

Als je genoten hebt van de inhoud die in deze specifieke tutorial wordt behandeld, abonneer je dan op het LuckyTemplates TV-kanaal. Er komt voortdurend een enorme hoeveelheid content uit van mijzelf en een reeks contentmakers, allemaal toegewijd aan het verbeteren van de manier waarop u LuckyTemplates en het Power Platform gebruikt.