Power Query kan være en stor hjælp, når du vil kombinere flere projektmapper til en enkelt projektmappe.
Antag f.eks., At du har salgsdata for forskellige regioner (øst, vest, nord og syd). Du kan kombinere disse data fra forskellige projektmapper til et enkelt regneark ved hjælp af Power Query.
Hvis du har disse projektmapper forskellige steder/mapper, er det en god idé at flytte alle disse til en enkelt mappe (eller oprette en kopi og lægge den projektmappe -kopi i den samme mappe).
Så til at begynde med har jeg fire projektmapper i en mappe (som vist herunder).
Nu i denne vejledning dækker jeg tre scenarier, hvor du kan kombinere data fra forskellige projektmapper ved hjælp af Power Query:
- Hver projektmappe har dataene i en Excel -tabel, og alle tabelnavnene er ens.
- Hver projektmappe har dataene med det samme regnearksnavn. Dette kan være tilfældet, når der er et ark med navnet 'resumé' eller 'data' i alle projektmapper, og du vil kombinere alle disse.
- Hver projektmappe har mange ark og tabeller, og du vil kombinere specifikke tabeller/ark. Denne metode kan også være nyttig, når du vil kombinere borde/ark, der ikke har et ensartet navn.
Lad os se, hvordan vi kombinerer data fra disse projektmapper i hvert enkelt tilfælde.
Hver projektmappe har dataene i en Excel -tabel med samme struktur
Nedenstående teknik ville fungere, når dine Excel -tabeller er blevet struktureret på samme måde (samme kolonnenavne).
Antallet af rækker i hver tabel kan variere.
Bare rolig, hvis nogle af Excel -tabellerne har yderligere kolonner. Du kan vælge en af tabellerne som skabelonen (eller som 'nøglen' som Power Query kalder det), og Power Query ville bruge den til at kombinere alle de andre Excel -tabeller med den.
Hvis der er yderligere kolonner i andre tabeller, ignoreres disse, og kun dem, der er angivet i skabelonen/nøglen, kombineres. Hvis den skabelon/nøgletabel, du vælger, f.eks. Har 5 kolonner, og en af tabellerne i en anden projektmappe har 2 ekstra kolonner, ignoreres disse yderligere kolonner.
Nu har jeg fire projektmapper i en mappe, som jeg vil kombinere.
Nedenfor er et øjebliksbillede af den tabel, jeg har i en af projektmapperne.
Her er trinene til at kombinere dataene fra disse projektmapper til en enkelt projektmappe (som en enkelt tabel).
- Gå til fanen Data.
- Klik på rullemenuen Ny forespørgsel i gruppen Hent og transformer.
- Hold markøren på 'Fra fil', og klik på 'Fra mappe'.
- I dialogboksen Mappe skal du indtaste filstien til den mappe, der har filerne, eller klik på Gennemse og find mappen.
- Klik på OK.
- I dialogboksen, der åbnes, skal du klikke på knappen kombiner.
- Klik på 'Kombiner og indlæs'.
- I dialogboksen 'Kombiner filer', der åbnes, skal du vælge tabellen i venstre rude. Bemærk, at Power Query viser dig tabellen fra den første fil. Denne fil vil fungere som skabelonen (eller nøglen) til at kombinere andre filer. Power Query ville nu lede efter 'Tabel 1' i andre projektmapper og kombinere det med denne.
- Klik på OK.
Dette indlæser det endelige resultat (kombinerede data) i dit aktive regneark.
Bemærk, at sammen med dataene tilføjer Power Query automatisk projektmappens navn som den første kolonne i de kombinerede data. Dette hjælper med at holde styr på, hvilke data der kom fra hvilken projektmappe.
Hvis du først vil redigere dataene, før du indlæser dem i Excel, skal du i trin 6 vælge 'Kombiner og rediger'. Dette åbner det endelige resultat i Power Query -editoren, hvor du kan redigere dataene.
Et par ting at vide:
- Hvis du vælger en Excel -tabel som skabelon (i trin 7), bruger Power Query kolonnens navne i denne tabel til at kombinere data fra andre tabeller. Hvis andre tabeller har yderligere kolonner, ignoreres disse. Hvis de andre tabeller ikke har en kolonne, der er der i din skabelontabel, ville Power Query bare sætte 'null' for den.
- Kolonnerne behøver ikke at være i samme rækkefølge, som Power Query bruger kolonneoverskrifter til at kortlægge kolonner.
- Da du har valgt tabel1 som nøgle, leder Power Query efter tabel1 i alle projektmapper og kombinerer alle disse. Hvis den ikke finder en Excel -tabel med samme navn (tabel1 i dette eksempel), giver Power Query dig en fejl.
Tilføjelse af nye filer til mappen
Lad os nu tage et minut og forstå, hvad vi gjorde med ovenstående trin (hvilket kun tog os et par sekunder).
Vi kombinerede dataene fra fire forskellige projektmapper i en enkelt tabel på få sekunder uden selv at åbne nogen af projektmapperne.
Men det er ikke alt.
Den virkelige POWER af Power Query er, at når du nu tilføjer flere filer til mappen, behøver du ikke at gentage nogen af disse trin.
Alt du skal gøre for at flytte den nye projektmappe i mappen, opdatere forespørgslen, og den kombinerer automatisk dataene fra alle projektmapper i den pågældende mappe.
For eksempel i ovenstående eksempel, hvis jeg tilføjer en ny projektmappe - 'Midt-vest.xlsx' til mappen og opdatere forespørgslen, vil det øjeblikkeligt give mig det nye kombinerede datasæt.
Sådan opdaterer du en forespørgsel:
- Højreklik på Excel-tabellen, som du har indlæst i regnearket, og klik på Opdater.
- Højreklik på forespørgslen i ruden 'Workbook Query', og klik på Opdater
- Gå til fanen Data, og klik på Opdater.
Hver projektmappe har dataene med det samme regnearksnavn
Hvis du ikke har dataene i en Excel -tabel, men alle arknavnene (hvorfra du vil kombinere dataene) er de samme, kan du bruge metoden vist i dette afsnit.
Der er et par ting, du skal være forsigtig med, når det bare er tabeldata og ikke en Excel -tabel.
- Regnearkets navne skal være de samme. Dette hjælper Power Query med at gennemgå dine projektmapper og kombinere dataene fra regnearkene, der har samme navn i hver projektmappe.
- Power Query er store og små bogstaver. Dette betyder, at et regneark med navnet 'data' og 'data' betragtes som forskellige. På samme måde betragtes en kolonne med overskriften 'Butik' og en med 'butik' som forskellige.
- Selvom det er vigtigt at have de samme kolonneoverskrifter, er det ikke vigtigt at have den samme rækkefølge. Hvis kolonne 2 i 'East.xlsx' er kolonne 4 i 'West.xlsx', matcher Power Query den korrekt ved at kortlægge overskrifterne.
Lad os nu se, hvordan vi hurtigt kombinerer data fra forskellige projektmapper, hvor regnearksnavnet er det samme.
I dette eksempel har jeg en mappe med fire filer.
I hver projektmappe har jeg et regneark med navnet 'Data', der indeholder dataene i følgende format (bemærk at dette ikke er en Excel -tabel).
Her er trinene til at kombinere data fra flere projektmapper til et enkelt regneark:
- Gå til fanen Data.
- Klik på rullemenuen Ny forespørgsel i gruppen Hent og transformer.
- Hold markøren på 'Fra fil', og klik på 'Fra mappe'.
- I dialogboksen Mappe skal du indtaste filstien til den mappe, der har filerne, eller klik på Gennemse og find mappen.
- Klik på OK.
- I dialogboksen, der åbnes, skal du klikke på knappen kombiner.
- Klik på 'Kombiner og indlæs'.
- I dialogboksen 'Kombiner filer', der åbnes, skal du vælge 'Data' i venstre rude. Bemærk, at Power Query viser dig regnearksnavnet fra den første fil. Denne fil vil fungere som nøglen/skabelonen til at kombinere andre filer. Power Query gennemgår hver projektmappe, finder arket med navnet 'Data' og kombinerer alle disse.
- Klik på OK. Nu går Power Query igennem hver projektmappe, leder efter regnearket med navnet 'Data' i den og kombinerer derefter alle disse datasæt.
Dette indlæser det endelige resultat (kombinerede data) i dit aktive regneark.
Hvis du først vil redigere dataene, før du indlæser dem i Excel, skal du i trin 6 vælge 'Kombiner og rediger'. Dette åbner det endelige resultat i Power Query -editoren, hvor du kan redigere dataene.
Hver projektmappe har dataene med forskellige tabelnavne eller arknavne
Nogle gange får du muligvis ikke strukturerede og konsistente data (f.eks. Tabeller med samme navn eller regneark med samme navn).
Antag f.eks., At du får dataene fra en person, der har oprettet disse datasæt, men navngivet regnearkene som East Data, West Data, North Data og South Data.
Eller personen kan have oprettet Excel -tabeller, men med forskellige navne.
I sådanne tilfælde kan du stadig bruge Power Query, men du skal gøre det med et par ekstra trin.
- Gå til fanen Data.
- Klik på rullemenuen Ny forespørgsel i gruppen Hent og transformer.
- Hold markøren på 'Fra fil', og klik på 'Fra mappe'.
- I dialogboksen Mappe skal du indtaste filstien til den mappe, der har filerne, eller klik på Gennemse og find mappen.
- Klik på OK.
- Klik på knappen Rediger i dialogboksen, der åbnes. Dette åbner Power Query -editoren, hvor du vil se detaljerne for alle filerne i mappen.
- Hold Ctrl-tasten nede, vælg kolonnerne 'Indhold' og 'Navn', højreklik og vælg 'Fjern andre kolonner'. Dette fjerner alle de andre kolonner undtagen de valgte kolonner.
- Klik på "Tilføj kolonne" i båndet i forespørgselseditor, og klik derefter på "Brugerdefineret kolonne".
- I dialogboksen Tilføj tilpasset kolonne skal du navngive den nye kolonne som 'Dataimport' og bruge følgende formel = Excel.Workbook ([INDHOLD]). Bemærk, at denne formel er store og små bogstaver, og du skal indtaste den nøjagtigt som jeg har vist her.
- Nu vil du se en ny kolonne, der har tabel skrevet i den. Lad mig nu forklare, hvad der skete her. Du har givet Power Query navnene på projektmapperne, og Power Query har hentet objekterne, f.eks. Regneark, tabeller og navngivne områder fra hver projektmappe (som findes i tabelcellen fra nu af). Du kan klikke på det hvide mellemrum ved siden af tekstbordet, og du vil se oplysningerne nederst. I dette tilfælde, da vi kun har en tabel og et regneark i hver projektmappe, kan du kun se to rækker.
- Klik på ikonet med dobbelt pil øverst i kolonnen 'Dataimport'.
- Fjern markeringen i afsnittet "Brug den originale kolonne som præfiks" i den kolonnen datafelt, der åbnes, og klik derefter på OK.
- Nu vil du se en udvidet tabel, hvor du ser en række for hvert objekt i tabellen. I dette tilfælde er arkobjektet og tabelobjektet opført separat for hver projektmappe.
- Filtrer listen i kolonnen Kind for kun at vise tabellen.
- Hold kontroltasten nede, og vælg kolonnen Navn og data. Højreklik nu og fjern alle de andre kolonner.
- I kolonnen Data skal du klikke på ikonet med dobbelt pil øverst til højre i dataoverskriften.
- Klik på OK i den kolonnen datafelt, der åbnes. Dette kombinerer dataene i alle tabellerne og vises i Power Query.
- Nu kan du foretage enhver transformation, du har brug for, og derefter gå til fanen Hjem og klikke på Luk og indlæs.
Lad mig nu prøve og hurtigt forklare, hvad vi gjorde her. Da der ikke var nogen konsistens i bladnavne eller tabelnavne, brugte vi = Excel.Workbook -formlen til at hente alle objekterne i projektmapperne i Power Query. Disse objekter kan omfatte ark, tabeller og navngivne områder. Når vi havde alle objekterne fra alle filerne, filtrerede vi disse for kun at overveje Excel -tabeller. Derefter udvidede vi dataene i tabellerne og kombinerede alle disse.
I dette eksempel filtrerede vi dataene til kun at bruge Excel -tabeller (i trin 13). Hvis du vil kombinere ark og ikke tabeller, kan du filtrere ark.
Bemærk - denne teknik giver dig de kombinerede data, selvom der er et misforhold i kolonnenavne. For eksempel, hvis du i East.xlsx har en kolonne, der er stavet forkert, ender du med 5 kolonner. Power Query udfylder data i kolonner, hvis den finder dem, og hvis den ikke kan finde en kolonne, rapporterer den værdien som 'null'.
På samme måde, hvis du har nogle ekstra kolonner i et af tabellernes regneark, vil disse blive inkluderet i det endelige resultat.
Hvis du nu får flere projektmapper, som du skal kombinere data fra, skal du blot kopiere og indsætte dem i mappen og opdatere Power Query
