Hur använder jag Power Query i Excel?
Excel Power Query används för att söka datakällor, skapa anslutningar med datakällor och sedan forma data enligt vårt analyskrav. När vi är klara med att forma data enligt våra behov kan vi också dela våra resultat och skapa olika rapporter med fler frågor.
Steg
Grundläggande finns det fyra steg, och ordningen på dessa fyra steg i Power Query är som följer:
- Anslut: Vi ansluter först till data, som kan finnas någonstans, i molnet, i tjänst eller lokalt.
- Transform: Det andra steget skulle vara att ändra formen på data enligt användarkrav.
- Kombinera: I det här steget utför vi några omvandlings- och aggregeringssteg och kombinerar data från båda källorna för att producera en kombinerad rapport.
- Hantera: Detta slår samman och lägger till kolumner i en fråga med matchande kolumner i andra frågor i arbetsboken.
Det finns många superkraftiga funktioner i Excel Power Query.
Anta att vi har inköpsdata för de senaste 15 åren i 180 filer. Nu skulle ledningen av en organisation kräva att siffrorna konsolideras innan de analyseras. Ledningen kan ta någon av följande metoder:
- De skulle öppna alla filer och kopiera och klistra in dem på en fil.
- Å andra sidan kan de använda en klok lösning, som är att tillämpa formler, eftersom den är benägen för fel.
Oavsett vilken metod de väljer, innehåller den mycket manuellt arbete, och efter några månader skulle det finnas nya försäljningsdata för den extra varaktigheten. De måste göra samma övning igen.
Power Query kan dock hjälpa dem att inte göra detta tråkiga och repetitiva arbete. Låt oss förstå denna excel power-fråga med ett exempel.
Exempel
Antag att vi har textfiler i en mapp med försäljningsdata, och vi vill få den informationen i vår excel-fil.
Som vi kan se i bilden nedan att vi har två typer av filer i mappen, men vi vill hämta data för endast textfiler i Excel-filen.
För att göra detsamma skulle stegen vara:
Steg 1: Först måste vi hämta data i Power Query så att vi kan göra de ändringar som krävs för att importera det till en excel-fil.
För att göra detsamma väljer vi alternativet "Från mapp" från "Från fil" -menyn efter att ha klickat på kommandot "Hämta data" från gruppen "Hämta och omvandla" på fliken "Data" .
Steg 2: Välj mappens plats genom att bläddra.
Klicka på 'OK'
Steg 3: En dialogruta öppnas som innehåller listan för alla filer i den valda mappen med kolumnrubrikerna som 'Innehåll', 'Namn', 'Tillägg', 'Datum öppnat', 'Datum ändrat', 'Datum skapat' 'Attribut' och 'Mappsökväg.'
Det finns tre alternativ, dvs. kombinera , ladda och transformera data .
- Kombinera : Detta alternativ används för att gå till en skärm där vi kan välja vilka data som ska kombineras. Redigeringssteg hoppas över för det här alternativet och ger oss ingen kontroll över vilka filer som ska kombineras. Kombinera funktion tar varje fil i mappen att konsolidera, vilket kan leda till fel.
- Ladda: Detta alternativ laddar bara tabellen som visas ovan på bilden i Excel-kalkylbladet istället för de faktiska uppgifterna i filerna.
- Transformera data: Till skillnad från kommandot "Kombinera" , om vi använder det här kommandot, kan vi välja vilka filer som ska kombineras, dvs. vi kan bara kombinera en typ av fil (samma tillägg).
Som i vårt fall vill vi bara kombinera textfiler (.txt); vi väljer kommandot "Transform Data" .
Vi kan se "Tillämpade steg" till höger om fönstret. För tillfället är det bara ett enda steg som är att ta filinformation från mappen.
Steg 4: Det finns en kolumn med namnet "Extension" där vi kan se att värdena i kolumnen skrivs i båda fallen, dvs. versaler och gemener.
Vi måste emellertid konvertera alla värden till små bokstäver eftersom filter skiljer mellan båda. För att göra detsamma måste vi välja kolumnen och sedan välja "Små bokstäver" från kommandomenyn "Format" .
Steg 5: Vi filtrerar data med hjälp av kolumnen "Tillägg" för textfiler.
Steg 6: Vi måste kombinera data för båda textfilerna nu med den första kolumnen "Innehåll". Vi klickar på ikonen placerad till höger om kolumnnamnet.
Steg 7: En dialogruta med texten 'Kombinera filer' öppnas där vi måste välja avgränsare som 'Flik' för textfiler (filer med '.txt' förlängning ') och kan välja bas för datatypsidentifiering. Och klicka på 'OK'.
Efter att ha klickat på 'OK' får vi de kombinerade uppgifterna för textfiler i fönstret 'Power Query' .
Vi kan ändra datatypen för kolumnerna efter behov. För kolumnen "Intäkter" ändrar vi datatypen till "Valuta".
Vi kan se stegen som tillämpas på data med hjälp av en energifråga till höger om fönstret.
Efter att ha gjort alla nödvändiga ändringar i data kan vi ladda in data i ett Excel-kalkylblad med kommandot 'Stäng och ladda till' under gruppen 'Stäng' på fliken 'Hem' .
Vi måste välja om vi vill ladda data som en tabell eller anslutning. Klicka sedan på 'OK'.
Nu kan vi se data som en tabell i kalkylbladet.
Och rutan 'Arbetsbokfrågor' på höger sida, som vi kan använda för att redigera, duplicera, slå samman, lägga till frågorna och för många andra ändamål.
Excel Power Query är mycket användbart eftersom vi kan se att 601.612 rader har laddats inom några minuter.
Saker att komma ihåg
- Power Query ändrar inte den ursprungliga källdata. Istället för att ändra originalkälldata registrerar det varje steg som tas av användaren när data ansluts eller omvandlas, och när användaren är klar med att forma data tar den den förfinade datamängden och tar den med i arbetsboken.
- Power Query är skiftlägeskänslig.
- När vi konsoliderar filerna i den angivna mappen måste vi se till att använda kolumnen "Extension" och vi måste utesluta tillfälliga filer (med tillägget ".tmp" och namnet på dessa filer börjar med "~" -tecknet) Power Query kan också importera dessa filer.
