Hvordan bruke Power Query i Excel?
Excel Power Query brukes til å søke i datakilder, lage forbindelser med datakilder, og deretter forme dataene i henhold til vårt analysebehov. Når vi er ferdige med å forme dataene etter våre behov, kan vi også dele funnene våre og lage forskjellige rapporter ved hjelp av flere spørsmål.
Fremgangsmåte
I utgangspunktet er det fire trinn, og rekkefølgen på disse fire trinnene i Power Query er som følger:
- Koble: Vi kobler først til dataene, som kan være et sted, i skyen, i tjeneste eller lokalt.
- Transform: Det andre trinnet vil være å endre formen på dataene i henhold til brukerens krav.
- Kombiner: I dette trinnet utfører vi noen transformasjons- og aggregeringstrinn og kombinerer data fra begge kilder for å produsere en kombinert rapport.
- Administrer: Dette slår sammen og legger til kolonner i et spørsmål med samsvarende kolonner i andre spørsmål i arbeidsboken.
Det er mange superkraftige funksjoner i Excel Power Query.
Anta at vi har kjøpsdata for de siste 15 årene i 180 filer. Nå vil ledelsen i en organisasjon kreve at tallene konsolideres før de analyseres. Ledelsen kan ta en av følgende metoder:
- De ville åpne alle filene og kopiere og lime dem inn i en fil.
- På den annen side kan de bruke en klok løsning, som er å bruke formler, da den er utsatt for feil.
Uansett hvilken metode de velger, inneholder den mye manuelt arbeid, og etter noen måneder vil det være nye salgsdata for den ekstra varigheten. De må gjøre den samme øvelsen igjen.
Power Query kan imidlertid hjelpe dem til ikke å gjøre dette kjedelige og repeterende arbeidet. La oss forstå dette excel power spørringen med et eksempel.
Eksempel
Anta at vi har tekstfiler i en mappe med salgsdata, og vi ønsker å få disse dataene i excel-filen vår.
Som vi kan se i bildet nedenfor at vi har to typer filer i mappen, men vi ønsker å få dataene til bare tekstfiler i Excel-filen.
For å gjøre det samme vil trinn være:
Trinn 1: Først må vi få dataene i Power Query slik at vi kan gjøre de nødvendige endringene i dataene for å importere det til en Excel-fil.
For å gjøre det samme, velger vi alternativet "Fra mappe" fra "Fra fil" -menyen etter å ha klikket på kommandoen "Get Data" fra "Get & Transform" -gruppen i "Data" -fanen.
Trinn 2: Velg plasseringen til mappen ved å bla gjennom.
Klikk på 'OK'
Trinn 3: En dialogboks åpnes som inneholder listen for alle filene i den valgte mappen med kolonneoverskriftene som 'Innhold', 'Navn', 'Utvidelse', 'Tilgangsdato, ' Dato for endring, 'Dato opprettet,' 'Attributter' og 'Mappesti.'
Det er tre alternativer, dvs. kombinere , laste og transformere data .
- Kombiner : Dette alternativet brukes til å gå til et skjermbilde der vi kan velge hvilke data som skal kombineres. Redigeringstrinn hoppes over for dette alternativet og gir oss ingen kontroll over hvilke filer vi skal kombinere. Kombinere-funksjonen tar hver fil i mappen for å konsolidere, noe som kan føre til feil.
- Last: Dette alternativet vil bare laste inn tabellen som vist ovenfor på bildet i Excel-regnearket i stedet for de faktiske dataene i filene.
- Transform Data: I motsetning til kommandoen 'Kombinere' , hvis vi bruker denne kommandoen, kan vi velge hvilke filer vi vil kombinere, dvs. vi kan bare kombinere en type fil (samme filtype).
Som i vårt tilfelle vil vi bare kombinere tekstfiler (.txt); vi velger kommandoen "Transform Data" .
Vi kan se "Anvendte trinn" på høyre side av vinduet. Foreløpig er det bare et enkelt trinn gjort som er å ta fildetaljer fra mappen.
Trinn 4: Det er en kolonne kalt 'Extension' der vi kan se at verdiene i kolonnen er skrevet i begge tilfeller, dvs. store og små bokstaver.
Vi må imidlertid konvertere alle verdiene til små bokstaver ettersom filter skiller mellom begge. For å gjøre det samme, må vi velge kolonnen og deretter velge "Små bokstaver" fra kommandoen "Format" -menyen.
Trinn 5: Vi filtrerer dataene ved hjelp av kolonnen 'Utvidelse' for tekstfiler.
Trinn 6: Vi må kombinere data for begge tekstfilene nå ved hjelp av den første kolonnen "Innhold." Vi klikker på ikonet plassert til høyre for kolonnenavnet.
Trinn 7: En dialogboks med bildeteksten 'Kombinere filer' åpnes der vi trenger å velge skilletegn som 'Tab' for tekstfiler (filer med '.txt' utvidelse ') og kan velge basen for deteksjon av datatype. Og klikk på 'OK'.
Etter å ha klikket på 'OK' , får vi de kombinerte dataene til tekstfiler i vinduet 'Power Query' .
Vi kan endre datatypen for kolonnene etter behov. For kolonnen 'Inntekter' endrer vi datatypen til 'Valuta'.
Vi kan se trinnene som brukes på dataene ved hjelp av et strømforespørsel på høyre side av vinduet.
Etter å ha gjort alle nødvendige endringer i dataene, kan vi laste dataene inn i et Excel-regneark ved hjelp av kommandoen 'Lukk og last til' under 'Lukk' -gruppen i 'Hjem' -fanen.
Vi må velge om vi vil laste dataene som en tabell eller tilkobling. Klikk deretter på 'OK'.
Nå kan vi se dataene som en tabell i regnearket.
Og 'Workbook Queries' -ruten på høyre side, som vi kan bruke til redigering, duplisering, sammenslåing, vedlegg av spørsmålene og til mange andre formål.
Excel Power Query er veldig nyttig, da vi kan se at 601.612 rader er lastet i løpet av få minutter.
Ting å huske
- Power Query endrer ikke de opprinnelige kildedataene. I stedet for å endre originale kildedata, registrerer det hvert trinn som tas av brukeren mens de kobler til eller transformerer dataene, og når brukeren er ferdig med å forme dataene, tar det det raffinerte datasettet og bringer det inn i arbeidsboken.
- Power Query er store og små bokstaver.
- Mens vi konsoliderer filene i den angitte mappen, må vi sørge for at bruk av kolonnen 'Utvidelse', og vi må ekskludere midlertidige filer (med utvidelsen '.tmp' og navnet på disse filene starter med '~' -tegnet) som Power Query kan også importere disse filene.
