Sådan bruges Power Query i Excel?
Excel Power Query bruges til at søge datakilder, oprette forbindelser med datakilder og derefter forme dataene i henhold til vores analysebehov. Når vi er færdige med at forme dataene efter vores behov, kan vi også dele vores resultater og oprette forskellige rapporter ved hjælp af flere forespørgsler.
Trin
Grundlæggende er der 4 trin, og rækkefølgen af disse 4 trin i Power Query er som følger:
- Opret forbindelse: Vi opretter først forbindelse til dataene, som kan være et eller andet sted, i skyen, i service eller lokalt.
- Transform: Det andet trin ville være at ændre formen på dataene efter brugerens krav.
- Kombiner: I dette trin udfører vi nogle transformation- og aggregeringstrin og kombinerer data fra begge kilder for at producere en kombineret rapport.
- Administrer: Dette fletter og tilføjer kolonner i en forespørgsel med matchende kolonner i andre forespørgsler i projektmappen.
Der er mange superkraftige funktioner i Excel Power Query.
Antag, at vi har købsdata for de sidste 15 år i 180 filer. Nu vil ledelsen af en organisation kræve, at tallene konsolideres, før de analyseres. Ledelsen kan tage en af følgende metoder:
- De åbnede alle filer og kopierede dem på en fil.
- På den anden side kan de bruge en klog løsning, der er at anvende formler, da den er tilbøjelig til fejl.
Uanset hvilken metode de vælger, indeholder den en masse manuelt arbejde, og efter et par måneder ville der være nye salgsdata i den ekstra varighed. De bliver nødt til at udføre den samme øvelse igen.
Power Query kan dog hjælpe dem med ikke at udføre dette kedelige og gentagne arbejde. Lad os forstå denne excel power-forespørgsel med et eksempel.
Eksempel
Antag, at vi har tekstfiler i en mappe med salgsdata, og vi ønsker at få disse data i vores excel-fil.
Som vi kan se i billedet nedenfor, at vi har to typer filer i mappen, men vi ønsker kun at hente dataene for tekstfiler i excel-filen.
For at gøre det samme vil trin være:
Trin 1: Først skal vi hente dataene i Power Query, så vi kan foretage de nødvendige ændringer i dataene for at importere det til en excel-fil.
For at gøre det samme vælger vi indstillingen "Fra mappe" i menuen "Fra fil" efter at have klikket på kommandoen "Hent data" fra gruppen "Hent og transformer" i fanen "Data" .
Trin 2: Vælg placeringen af mappen ved at gennemse.
Klik på 'OK'
Trin 3: En dialogboks åbnes, der indeholder listen over alle filer i den valgte mappe med kolonneoverskrifterne som 'Indhold', 'Navn', 'Udvidelse', 'Dato,' 'Dato,' 'Dato ,' 'Oprettelsesdato,' ' 'Attributter' og 'Mappesti.'
Der er 3 muligheder, dvs. kombinere , indlæse og transformere data .
- Kombiner : Denne mulighed bruges til at gå til en skærm, hvor vi kan vælge, hvilke data der skal kombineres. Redigeringstrin springes over for denne mulighed og giver os ingen kontrol over, hvilke filer der skal kombineres. Kombineringsfunktionen tager hver fil i mappen til at konsolidere, hvilket kan føre til fejl.
- Indlæs: Denne indstilling indlæser bare tabellen som vist ovenfor på billedet i Excel-regnearket i stedet for de faktiske data i filerne.
- Transform data: I modsætning til kommandoen 'Kombiner' , hvis vi bruger denne kommando, kan vi vælge hvilke filer der skal kombineres, dvs. vi kan kun kombinere en type fil (samme udvidelse).
Som i vores tilfælde ønsker vi kun at kombinere tekstfiler (.txt); vi vælger kommandoen "Transform Data" .
Vi kan se "Anvendte trin" på højre side af vinduet. For nu er der kun et enkelt trin, der er at tage filoplysninger fra mappen.
Trin 4: Der er en kolonne med navnet 'Udvidelse', hvor vi kan se, at værdierne i kolonnen er skrevet i begge tilfælde, dvs. store og små bogstaver.
Vi skal dog konvertere alle værdier til små bogstaver, da filter skelner mellem begge. For at gøre det samme er vi nødt til at vælge kolonnen og derefter vælge "Små bogstaver" fra kommandoen "Format" -menuen.
Trin 5: Vi filtrerer dataene ved hjælp af kolonnen 'Udvidelse' til tekstfiler.
Trin 6: Vi skal kombinere data for begge tekstfiler nu ved hjælp af den første kolonne 'Indhold'. Vi klikker på ikonet placeret i højre side af kolonnenavnet.
Trin 7: En dialogboks med overskriften 'Kombiner filer' åbnes, hvor vi skal vælge afgrænsningen som 'Tab' for tekstfiler (filer med '.txt' udvidelse ') og kan vælge basen til detektion af datatype. Og klik på 'OK'.
Efter at have klikket på 'OK' får vi de kombinerede data fra tekstfiler i vinduet 'Power Query' .
Vi kan ændre datatypen for kolonnerne efter behov. For kolonnen 'Omsætning' ændrer vi datatypen til 'Valuta'.
Vi kan se de trin, der anvendes på dataene ved hjælp af en strømforespørgsel i højre side af vinduet.
Efter at have foretaget alle de nødvendige ændringer i dataene, kan vi indlæse dataene i et excel-regneark ved hjælp af kommandoen 'Luk og indlæs til' under gruppen 'Luk' i fanen 'Hjem' .
Vi skal vælge, om vi vil indlæse dataene som en tabel eller en forbindelse. Klik derefter på 'OK'.
Nu kan vi se dataene som en tabel i regnearket.
Og 'Workbook Queries' -ruden i højre side, som vi kan bruge til redigering, duplikering, fletning, tilføjelse af forespørgsler og til mange andre formål.
Excel Power Query er meget nyttigt, da vi kan se, at 601.612 rækker er blevet indlæst inden for få minutter.
Ting at huske
- Power Query ændrer ikke de originale kildedata. I stedet for at ændre originale kildedata, registrerer det hvert trin, der tages af brugeren under tilslutning eller transformation af dataene, og når brugeren er færdig med at forme dataene, tager det det raffinerede datasæt og bringer det ind i projektmappen.
- Power Query er store og små bogstaver.
- Mens vi konsoliderer filerne i den angivne mappe, skal vi sørge for at bruge kolonnen 'Udvidelse', og vi skal udelukke midlertidige filer (med udvidelsen '.tmp' og navnet på disse filer starter med '~' tegn) som Power Query kan også importere disse filer.
