Power Query kan være til stor hjelp når du vil kombinere flere arbeidsbøker til en enkelt arbeidsbok.
Anta for eksempel at du har salgsdata for forskjellige regioner (øst, vest, nord og sør). Du kan kombinere disse dataene fra forskjellige arbeidsbøker til et enkelt regneark ved hjelp av Power Query.
Hvis du har disse arbeidsbøkene på forskjellige steder/mapper, er det en god idé å flytte alle disse til en enkelt mappe (eller opprette en kopi og legge den arbeidsbokkopien i samme mappe).
Så til å begynne med har jeg fire arbeidsbøker i en mappe (som vist nedenfor).
Nå, i denne opplæringen, dekker jeg tre scenarier der du kan kombinere data fra forskjellige arbeidsbøker ved hjelp av Power Query:
- Hver arbeidsbok har dataene i en Excel -tabell, og alle tabellnavnene er de samme.
- Hver arbeidsbok har dataene med det samme regnearknavnet. Dette kan være tilfelle når det er et ark som heter "sammendrag" eller "data" i alle arbeidsbøkene, og du vil kombinere alle disse.
- Hver arbeidsbok har mange ark og tabeller, og du vil kombinere bestemte tabeller/ark. Denne metoden kan også være nyttig når du vil kombinere bord/ark som ikke har et konsistent navn.
La oss se hvordan vi kombinerer data fra disse arbeidsbøkene i hvert enkelt tilfelle.
Hver arbeidsbok har dataene i en Excel -tabell med samme struktur
Teknikken nedenfor fungerer når Excel -tabellene har blitt strukturert på samme måte (samme kolonnenavn).
Antall rader i hver tabell kan variere.
Ikke bekymre deg hvis noen av Excel -tabellene har flere kolonner. Du kan velge en av tabellene som mal (eller som ‘nøkkelen’ som Power Query kaller det), og Power Query ville bruke den til å kombinere alle de andre Excel -tabellene med den.
Hvis det er flere kolonner i andre tabeller, blir de ignorert, og bare de som er angitt i malen/nøkkelen vil bli kombinert. For eksempel, hvis malen/nøkkeltabellen du velger har fem kolonner, og en av tabellene i en annen arbeidsbok har to ekstra kolonner, blir de ekstra kolonnene ignorert.
Nå har jeg fire arbeidsbøker i en mappe som jeg vil kombinere.
Nedenfor er et øyeblikksbilde av tabellen jeg har i en av arbeidsbøkene.
Her er trinnene for å kombinere dataene fra disse arbeidsbøkene til en enkelt arbeidsbok (som en enkelt tabell).
- Gå til fanen Data.
- Klikk på rullegardinlisten Ny forespørsel i Get & Transform -gruppen.
- Hold markøren på "Fra fil" og klikk på "Fra mappe".
- I mappen dialogboksen, skriv inn filbanen til mappen som har filene, eller klikk på Bla gjennom og finn mappen.
- Klikk OK.
- Klikk på kombinere -knappen i dialogboksen som åpnes.
- Klikk på "Kombiner og last".
- I tabellen "Kombiner filer" som åpnes, velger du tabellen i venstre rute. Vær oppmerksom på at Power Query viser deg tabellen fra den første filen. Denne filen vil fungere som malen (eller nøkkelen) for å kombinere andre filer. Power Query ville nå lete etter ‘Tabell 1’ i andre arbeidsbøker og kombinere den med denne.
- Klikk OK.
Dette vil laste det endelige resultatet (kombinerte data) inn i det aktive regnearket.
Vær oppmerksom på at sammen med dataene legger Power Query automatisk til arbeidsboknavnet som den første kolonnen i de kombinerte dataene. Dette hjelper deg med å holde oversikt over hvilke data som kom fra hvilken arbeidsbok.
Hvis du først vil redigere dataene før du laster dem inn i Excel, velger du "Kombiner og rediger" i trinn 6. Dette åpner det endelige resultatet i Power Query -editoren, hvor du kan redigere dataene.
Noen ting å vite:
- Hvis du velger en Excel -tabell som mal (i trinn 7), bruker Power Query kolonnenavnene i denne tabellen for å kombinere dataene fra andre tabeller. Hvis andre tabeller har flere kolonner, blir disse ignorert. Hvis de andre tabellene ikke har en kolonne, som er der i malbordet, ville Power Query bare sette 'null' for den.
- Kolonnene trenger ikke å være i samme rekkefølge som Power Query bruker kolonneoverskrifter for å kartlegge kolonner.
- Siden du har valgt Tabell1 som nøkkel, vil Power Query se etter Tabell1 i alle arbeidsbøkene og kombinere alle disse. Hvis den ikke finner en Excel -tabell med samme navn (tabell1 i dette eksemplet), gir Power Query deg en feil.
Legge til nye filer i mappen
La oss ta et minutt og forstå hva vi gjorde med trinnene ovenfor (som bare tok oss noen få sekunder).
Vi kombinerte dataene fra fire forskjellige arbeidsbøker i en enkelt tabell på få sekunder uten å åpne noen av arbeidsbøkene.
Men det er ikke alt.
Den virkelige POWER av Power Query er at når du legger til flere filer i mappen, trenger du ikke å gjenta noen av disse trinnene.
Alt du trenger å gjøre for å flytte den nye arbeidsboken i mappen, oppdatere spørringen, og den vil automatisk kombinere dataene fra alle arbeidsbøkene i den mappen.
For eksempel, i eksemplet ovenfor, hvis jeg legger til en ny arbeidsbok - 'Midt-vest.xlsx' til mappen og oppdatere spørringen, vil det umiddelbart gi meg det nye kombinerte datasettet.
Slik oppdaterer du en forespørsel:
- Høyreklikk på Excel-tabellen du lastet inn i regnearket, og klikk på Oppdater.
- Høyreklikk på spørringen i "Workbook Query" -ruten, og klikk på Oppdater
- Gå til kategorien Data og klikk på Refresh.
Hver arbeidsbok har dataene med det samme regnearknavnet
Hvis du ikke har dataene i en Excel -tabell, men alle bladnavnene (som du vil kombinere dataene fra) er de samme, kan du bruke metoden som vises i denne delen.
Det er noen ting du må være forsiktig med når det bare er tabelldata og ikke en Excel -tabell.
- Navnene på regnearket skal være de samme. Dette vil hjelpe Power Query til å gå gjennom arbeidsbøkene dine og kombinere dataene fra regnearkene som har samme navn i hver arbeidsbok.
- Power Query er store og små bokstaver. Dette betyr at et regneark som heter 'data' og 'data' anses som forskjellige. På samme måte anses en kolonne med overskriften "Butikk" og en med "butikk" som forskjellig.
- Selv om det er viktig å ha de samme kolonneoverskriftene, er det ikke viktig å ha samme rekkefølge. Hvis kolonne 2 i ‘East.xlsx’ er kolonne 4 i ‘West.xlsx’, vil Power Query matche den riktig ved å kartlegge overskriftene.
La oss se hvordan vi raskt kan kombinere data fra forskjellige arbeidsbøker der regnearknavnet er det samme.
I dette eksemplet har jeg en mappe med fire filer.
I hver arbeidsbok har jeg et regneark med navnet 'Data' som inneholder dataene i følgende format (merk at dette ikke er en Excel -tabell).
Her er trinnene for å kombinere data fra flere arbeidsbøker til ett enkelt regneark:
- Gå til fanen Data.
- Klikk på rullegardinmenyen Ny forespørsel i gruppen Get & Transform.
- Hold markøren på "Fra fil" og klikk på "Fra mappe".
- I mappen dialogboksen, skriv inn filbanen til mappen som har filene, eller klikk på Bla gjennom og finn mappen.
- Klikk OK.
- Klikk på kombinere -knappen i dialogboksen som åpnes.
- Klikk på "Kombiner og last".
- I dialogboksen "Kombiner filer" som åpnes, velger du "Data" i den venstre ruten. Vær oppmerksom på at Power Query viser deg regnearknavnet fra den første filen. Denne filen vil fungere som nøkkelen/malen for å kombinere andre filer. Power Query går gjennom hver arbeidsbok, finner arket med navnet 'Data' og kombinerer alle disse.
- Klikk OK. Nå vil Power Query gå gjennom hver arbeidsbok, se etter regnearket med navnet 'Data' i den, og deretter kombinere alle disse datasettene.
Dette vil laste det endelige resultatet (kombinerte data) inn i det aktive regnearket.
Hvis du først vil redigere dataene før du laster dem inn i Excel, velger du ‘Kombiner og rediger’ i trinn 6. Dette åpner det endelige resultatet i Power Query -editoren, hvor du kan redigere dataene.
Hver arbeidsbok har dataene med forskjellige tabellnavn eller arknavn
Noen ganger kan det hende at du ikke får strukturerte og konsistente data (for eksempel tabeller med samme navn eller regneark med samme navn).
Anta for eksempel at du får dataene fra noen som opprettet disse datasettene, men kalte regnearkene som East Data, West Data, North Data og South Data.
Eller personen kan ha opprettet Excel -tabeller, men med forskjellige navn.
I slike tilfeller kan du fortsatt bruke Power Query, men du må gjøre det med et par ekstra trinn.
- Gå til fanen Data.
- Klikk på rullegardinmenyen Ny forespørsel i gruppen Get & Transform.
- Hold markøren på "Fra fil" og klikk på "Fra mappe".
- I mappen dialogboksen, skriv inn filbanen til mappen som har filene, eller klikk på Bla gjennom og finn mappen.
- Klikk OK.
- Klikk på Rediger -knappen i dialogboksen som åpnes. Dette åpner Power Query -editoren der du vil se detaljene for alle filene i mappen.
- Hold nede Ctrl-tasten, velg "Innhold" og "Navn" -kolonnene, høyreklikk og velg "Fjern andre kolonner". Dette fjerner alle de andre kolonnene unntatt de valgte kolonnene.
- Klikk på "Legg til kolonne" i Query Editor -båndet, og klikk deretter på "Egendefinert kolonne".
- I dialogboksen Legg til egendefinert kolonne, navngi den nye kolonnen som 'Dataimport' og bruk følgende formel = Excel.Workbook ([CONTENT]). Vær oppmerksom på at denne formelen er store og små bokstaver, og du må angi den nøyaktig slik jeg har vist her.
- Nå vil du se en ny kolonne som har tabellen skrevet i den. La meg nå forklare hva som skjedde her. Du oppgav Power Query navnene på arbeidsbøkene, og Power Query har hentet objektene, for eksempel regneark, tabeller og navngitte områder fra hver arbeidsbok (som ligger i tabellcellen fra nå av). Du kan klikke på det hvite feltet ved siden av tekstbordet, og du vil se informasjonen nederst. I dette tilfellet, siden vi bare har én tabell og ett regneark i hver arbeidsbok, kan du bare se to rader.
- Klikk på dobbeltpilikonet øverst i kolonnen "Dataimport".
- I kolonnen data -boksen som åpnes, fjerner du merket for ‘Bruk original kolonne som prefiks’, og klikker deretter OK.
- Nå vil du se en utvidet tabell der du ser en rad for hvert objekt i tabellen. I dette tilfellet er arkobjektet og tabellobjektet listet separat for hver arbeidsbok.
- I Kind -kolonnen filtrerer du listen for bare å vise tabellen.
- Hold kontrolltasten og velg kolonnen Navn og data. Høyreklikk nå og fjern alle de andre kolonnene.
- I datakolonnen klikker du på dobbeltpilen øverst til høyre i dataoverskriften.
- Klikk OK i kolonnen datafelt som åpnes. Dette vil kombinere dataene i alle tabellene og vises i Power Query.
- Nå kan du gjøre hvilken som helst transformasjon du trenger, og deretter gå til kategorien Hjem og klikke Lukk og last inn.
La meg prøve å raskt forklare hva vi gjorde her. Siden det ikke var noen konsistens i arknavn eller tabellnavn, brukte vi = Excel.Workbook -formelen for å hente alle objektene til arbeidsbøkene i Power Query. Disse objektene kan inneholde ark, tabeller og navngitte områder. Når vi hadde alle objektene fra alle filene, filtrerte vi disse for å bare vurdere Excel -tabeller. Deretter utvidet vi dataene i tabellene og kombinerte alle disse.
I dette eksemplet filtrerte vi dataene for å bare bruke Excel -tabeller (i trinn 13). Hvis du vil kombinere ark og ikke tabeller, kan du filtrere ark.
Merk - denne teknikken gir deg de kombinerte dataene selv om det er feil samsvar i kolonnenavn. For eksempel, hvis du i East.xlsx har en kolonne som er stavet feil, vil du ende opp med 5 kolonner. Power Query fyller data i kolonner hvis den finner dem, og hvis den ikke finner en kolonne, rapporterer den verdien som "null".
På samme måte, hvis du har noen ekstra kolonner i noen av tabellene, vil disse bli inkludert i det endelige resultatet.
Hvis du får flere arbeidsbøker du trenger å kombinere data fra, kan du bare kopiere og lime den inn i mappen og oppdatere Power Query
