I denne artikel ser vi på hvordan vi kan bruge Power Query til at importere filerne i en mappe. Vi giver Power Query en mappesti, klikker på et par knapper, og den vil importere og kombinere alle filerne i en enkelt tabel. Det er imponerende ting, ikke sandt! Hvis vi vil tilføje en anden fil til outputtabellen, behøver vi kun at gemme en kopi af filen i mappen og klikke på Opdater, og den nye fil vil også blive importeret. Dette kan spare dig meget tid i løbet af dagen.
Inden vi ser nærmere denne teknik, er der et punkt, som jeg er nødt til at gøre dig opmærksom på. Alle de filer, der skal importeres, skal have den samme struktur og kolonneopstilling. Power Query er fantastisk, men du skal give den en chance.
Nedenfor kan du downloade en zip-fil med de filer der benyttes i opgaverne.
Opsætning af filerne til eksemplerne
For at arbejde med eksemplerne i denne opgave skal du starte med at flytte januar og februar filerne til en separat mappe. Dette er de filer, du skal importerer.
Tag ikke marts eller april filerne med, vi tilføjer dem senere som en del af eksemplerne.
Importer alle CSV-filer fra en mappe
De nemmeste filer at arbejde med er CSV eller tekst filer, så det er et godt sted at starte. Hvad er det der gør dem nemme at arbejde med?
- De indeholder altid et enkelt regneark.
- Den første række behandles som overskriftsrække.
- Flettede celler er ikke tilladt.
I Excel skal du vælge Data -> Ny forespørgsel -> Fra fil -> Fra mappe.
Dialogboksen Mappe åbnes. Klik på Gennemse, og naviger frem til den mappe, der skal bruges, og klik derefter på OK.
Der vises en forhåndsvisning af mappen og filattributterne. Hvis filerne er helt ens, og der ikke er nogen redigeringer, der kan udføres, kan du bare klikke på Kombiner -> Kombiner Luk & Indlæs. Du vil dog bemærke, at vi har en blanding af .csv og .xlsx filer i mappen; vi kan derfor ikke kombinere disse med det samme. I stedet vil vi klikke på Rediger og se nærmere på hvad der sker.
Forespørgselseditoren åbnes. Det viser ikke dataene fra filen, som de har gjort tidligere, i stedet viser de data om filerne.
Vores første transformation vil være kun at beholde de filer, vi ønsker at kombinere (CSV filer i vores nuværende eksempel). Det er altid god ide at filtrere for kun at inkludere de filer, vi har brug for; du ved aldrig, hvornår en anden bruger måske beslutter sig for at gemme en tilfældig fil i mappen.
Filtrer Extension kolonnen så den kun at omfatte .csv.
Herfra har vi to muligheder:
- Brug en brugerdefineret kolonne
- Kombiner filerne
Hvilken metode du vælger er op til dig, og du kan endda beslutte at bruge forskellige metoder under forskellige omstændigheder.
Metode 1: Brug en brugerdefineret kolonne
Den første metode benytter en brugerdefineret kolonne, der indeholder en Power Query funktion.
Start med at vælge kolonnerne Content og Name, og klik derefter på Hjem -> Fjern kolonner -> Fjern andre kolonner. Dette fjerner filattribut kolonnerne, som vi ikke har brug for. Vi har beholder kolonnen Name for at illustrere, at du ved hjælp af denne metode kan bevare filattributter i det endelige output.
Klik på Tilføj kolonne -> Brugerdefineret kolonne.
Indtast følgende oplysninger i vinduet Brugerdefineret kolonne:
Nyt kolonnenavn: CSV
Tilpasset kolonneformel:
= Csv.Document ([Content])
Klik på OK.
En ny kolonne kaldet CSV føjes til eksempelvinduet.
Du vil se et nyt ikon i CSV kolonneoverskriften. Dette er ikonet Udvid og angiver, at kolonnen indeholder flere detaljer, som kan udvides.
Klik på ikonet Udvid, og klik derefter på OK, da vi vil beholde alle kolonner med data.
Vi har nu udvidet alle data fra begge filer, og januar og februar filerne er kombineret.
Gør følgende for at rydde op i tabellen:
- Fjern kolonnen Content
- Tilføj kolonneoverskrifterne, det skal være Kildefil, Dato, Kunde, Produkt, Solgt af og Værdi.
- I filteret for Værdi kolonnen fjernes teksten “Værdi”
- Angiv datatyperne for hver kolonne
Vores eksempelvindue skal nu se sådan ud:
Det er det hele, klik på Luk & Indlæs for at indlæse tabellen i Excel.
Metode 2: Kombiner filerne
Nu er det tid til metode 2. Denne bruger Power Query’s smarte indbyggede kombineringsproces.
Hvis vi antager, at vi ikke benyttede metode 1, skal forhåndsvisnings vinduet se sådan ud:
Har du bemærket ikonet med de to nedadvente pile øverst i kolonnen Content? Det er ikonet Kombiner filer, det analyser fra filniveau til indholdsniveau og kombinerer hver fil. Fortsæt og klik på Kombiner filer.
Vinduet Kombiner fil åbnes og viser en prøve af dataene ved hjælp af den første fil i forespørgslen som et eksempel. Alt ser godt ud, så klik på OK.
Power Query vil nu udføre sin magi. Når den er færdig, kan vi se filerne er blevet kombineret, det var alt, hvad det krævede, en knap! Som vist på skærmbilledet nedenfor er der foretaget en masse transformationer og trin for at opnå dette.
Du kan se, at dataene for både januar og februar er blevet kombineret i denne tabel.
Klik på Hjem -> Luk & indlæs for at indlæse dataene i Excel.
Tilføjelse af flere filer til mappen
Jeg har oprettet en Pivot-tabel for at vise, at filerne for januar og februar er blevet kombineret.
Kopier filen Marts 2020.csv til mappen.
Og nu til det spændende, klik på Data -> Opdater alle, dataene vil blive opdateret.
“Hvis den er opdateret, hvorfor blev pivottabellen så ikke opdateret?” – Godt spørgsmål. Pivottabellen opdateres, inden dataene blev indlæst fra forespørgslen, så vi er nødt til at klikke på Data -> Opdater alle igen for at opdatere pivottabellen.
Læs eventuelt indlægget om Data opdatering for at deaktivere behovet for dobbelt opdatering.
Filens ensartethed er nøglen til succes
Tilføj nu filen April 2020.csv til mappen. Klik på Data -> Opdater alle.
Metode 1 opdaterer uden problemer. Metode 2 vil dog vise en fejl.
Hvorfor skete dette? Hvad gik galt? Åbn April 2020.csv og kig nærmere på den første række. I denne fil er kolonnen der kaldes Kunder, der findes i de andre filer, ændret til Solgt til.
Som en del af de automatiske trin promoverer Power Query den første række i en CSV fil som kolonneoverskrifter. Da vi oprindeligt kombinerede filerne ved hjælp af metode 2, brugte Februar som eksempelfil, derfor blev kolonneoverskrifterne fra Februar benyttet som kolonneoverskrifter for hele forespørgslen. Power Query kan på ingen måde vide, at April er forskellig fra de andre filer.
Metode 1 bruger ikke kolonneoverskrifterne fra CSV filerne, men bruger de relative kolonnepositioner. Hvilket er fint, medmindre kolonnens rækkefølge ændres i filer, i hvilket tilfælde du kunne kombinere forskellige datatyper uden at vide det.
Dette er grunden til, at filens ensartethed er nøglen til succes med denne teknik.
Importer Excel projektmappe fra en mappe
Metoden til at importere Excel projektmapper er næsten identisk med CSV filer. Men det indebærer forskellige udfordringer. Vi har allerede set i tidligere indlæg, at Power Query godt kan lide at hardkode værdier i forespørgslen. Dette bliver et problem, når alle vores Excel projektmapper har regneark med forskellige navne. For eksempel har alle vores eksempelfiler forskellige regnearksnavne.
Kopier Marts 2020.xlsx og April 2020.xlsx ind i mappen.
Følg disse trin i Excel:
- Vælg Data-> Ny forespørgsel -> Fra fil -> Fra mappe.
- Klik på Gennemse i dialogboksen Mappe. Find din mappe, og klik derefter på OK.
- Når listen over filer vises, skal du klikke på knappen Rediger.
- Forespørgseleditor vinduet åbnes.
- I kolonnen Extension filtreres så den kun omfatte .xlsx filer.
Lad os se på de to metoder som før.
Metode 1 – Brug en brugerdefineret kolonne
Metode 1 benytter en brugerdefineret kolonne formel som før.
Vælg kolonnerne Content og Name, og fjern derefter andre kolonner.
Lad os nu tilføje den nye kolonne, klik på Tilføj kolonne -> Brugerdefineret kolonne.
Indtast følgende oplysninger i dialogboksen Brugerdefineret kolonne.
Nyt kolonnenavn: ExcelFiler
Tilpasset kolonneformel:
=Excel.Workbook([Content])
Klik på OK.
Den nye kolonne ExcelFiler tilføjes. Klik på ikonet Udvid, og klik derefter på OK for at inkludere alle kolonner.
Hvert regneark og tabel fra projektmappen er vist. Filen April indeholder et regneark og en tabel, filtrer kolonnen ExcelFiler.Kind for at fjerne tabellerne og kun beholde regneark (Sheet).
Vælg kolonnen ExcelFiler.Data, og fjern derefter de andre kolonner. Klik på ikonet Udvid i kolonnen ExcelFiler.Data.
Se på forhåndsvisningsvinduet alle regneark er nu kombineret. Vælg Luk & indlæs for at indlæse dataene i Excel.
Metode 2: Kombiner filerne
Nu er det tid til metode 2. Dette bruger Power Query’s indbyggede kombineringsproces igen. Hvis vi antager, at vi ikke benyttede metode 1, skal forhåndsvisningsvinduet se sådan ud:
Klik på kombinationsikonet i kolonnen Content, som vi gjorde, da vi brugte metode 2 til CSV filer.
Da April 2020.xlsx er først på listen over filer, vil Power Query bruge denne, som en prøvefil. I dialogboksen Kombiner filer skal du vælge regnearket April og klikke på OK.
Hvis alle dine regneark har det samme navn, er du heldig. Alle data kombineres da til en enkelt tabel.
Men i vores scenarie, som du måske har regnet med, indlæses kun dataene April i forhåndsvisningsvinduet. Der er kun en projektmappe med et regneark kaldet April, så kun et regneark indlæses. Vi er nødt til at finde placeringen i M koden, hvor regnearks navnet er blevet kodet og justere det.
Åbn forespørgslen Transformer eksempelfil fra Øvelse.
Klik på Navigations trinnet i panelet Anvendte trin.
M koden til dette trin ser sådan ud:
= Kilde{[Item="April",Kind="Sheet"]}[Data]
Slet den fremhævede del. M koden skal nu se sådan ud:
= Source{[Kind="Sheet"]}[Data]
Vend tilbage til hovedforespørgslen og se på forhåndsvisningsvinduet. Alle regneark er nu kombineret.
Afslut forespørgslen
Vi bliver nødt til at foretage nogle ændringer for at rydde op i tingene.
- Fjern de 3 øverste rækker.
- Fremhæv den første række som overskrift ved at klikke på Transform -> Brug første række som overskrifter.
- Vælg den sidste kolonne og filter for at fjerne “null” og “Værdi”.
- Anvend de korrekte datatyper i hver kolonne.
Til sidst skal du lukke og indlæse forespørgslen i Excel.
Bemærkninger og tips
Her er et par bonustips:
- Når du leder efter filer, ser Power Query også i undermapper. Dette er smart, når filer er organiseret i en defineret struktur. Men hvis mapperne er rodede, kan det være nødvendigt med en smule oprydning for at få denne metode til at fungere.
- Planlæg, hvad der kan gå galt, tænk over, hvad der kan ske, hvis en anden skulle gemme en anden filtype i mappen. Når du opretter forespørgslen, skal du medtage ekstra filtertrin for at sikre kun, at de ønskede filer importeres.
- Data, der kommer fra et it-system, fungerer normalt godt sammen med denne teknik. Jeg har opdaget, at al data, der kræver brugerinput, har tendens til at skabe problemer … hvorfor? Fordi folk ændrer ting, tilføjer kolonner, omdøber ark, overskriver statiske værdier. Hvis dine filer kommer fra brugerinput, skal du overveje at bruge kombinerings- og tilføjelsesmetoden.