Power Query – lite blandade automatiseringar

I det här tipset ser du hur effektivt och enkelt det är att automatisera med Power Query. Lek med tanken att du har en stor tabell med åtta kolumner och 2000 rader data som den blåa tabellen i bilden nedanför. Tabellen visar data på en detaljerad nivå. Du behöver skapa en mindre tabell som både grupperar, summerar, filtrerar, sorterar och gör beräkningar den blåa tabellen. Resultatet som du vill ha är den orangefärgade tabellen till höger. Dessutom måste din lösning vara flexibel, d v s dynamisk och fungera om den blåa tabellen får nya transaktioner eller ändrar på befintliga data. Hur löser du detta?

Vad är det som ska göras ”rent praktiskt”?

1. Data behöver filtreras. Det finns två varumärken i den blåa tabellen. Du vill endast hämta data för varumärket Postfix i denna rapport. Dessutom vill du endast hämta data från år 2024 och framåt.
2. I den slutliga tabellen vill du ha tre kolumner summerade; Antal, Intäkt och Vinst. I den blåa tabellen finns inte kolumnen Intäkt. Däremot finns kolumnen Pris. Du behöver alltså multiplicera kolumnen Pris med kolumnen Antal för att kunna få Intäkt.
3. Data behöver grupperas på kolumnerna Kundkategori och Kund och samtidigt summeras på den grupperade nivån för tre kolumner; Antal, Intäkt och Vinst.
4. Du vill att den slutliga tabellen ska vara sorterad efter Kundkategori i stigande ordning och därefter sorterad efter kolumnen Intäkt i fallande ordning.
5. Alla kolumner ska vara formaterade med tusentalsavgränsare och noll decimaler.
6. Den slutliga tabellen ska dessutom ha summor längst ner för de tre numeriska kolumnerna.
7. Det ska vara enkelt att uppdatera den slutliga tabellen om vi får ny eller förändrad data i den blåa tabellen.

Steg för steg i Power Query

Hämta data med och öppna Power Query

Vill du ladda ner filen gör du det här.

Stå med cellmarkören i din tabell och gå in under menyfliken Data och klicka på knappen Från tabell/intervall för att Power Query-editorn.

Filtrera med Power Query

Klicka på droplistan i kolumnen Produkt och välj att filtrera på Postfix (alla rader med övriga varumärken filtreras därmed bort från urvalet/hämtningen).

Beräknade kolumner

Därefter ska skapa en beräknad kolumn genom att multiplicera kolumnerna Antal och Pris med varandra.

1️⃣ Markera kolumnerna Antal och Pris

2️⃣ Klicka på menyfliken Lägg till kolumn

3️⃣ Klicka på knappen Standard

4️⃣ Välj Multiplicera

5️⃣ En ny kolumn skapas (Multiplikation). Dubbelklicka på kolumnnamnet och skriv Intäkt.

Gruppera och summera aggregering

Nu är det dags att skapa grupperingen. Klicka på knappen Gruppera efter som finns i både menyfliken Start och Transformera.

Klicka i Avancerat.

Välj Kundkategori i droplistan. Klicka på Lägg till gruppering för att lägga till ytterligare kolumn/nivå att gruppera på och välj Kund i droplistan.

Därefter behöver du klicka på Lägg till sammansättning två gånger för att få totalt tre fält att fylla i. Skriv in önskat fältnamn till vänster, välj Summa i droplistan i mitten och se till att rätt kolumn väljs längst till höger.

Sortera med Power Query

Sista steget i vår databearbetning är att sortera. Markera kolumnen Kundkategori och välj knappen A-Z (stigande sortering) i menyfliken Start. Markera därefter kolumnen Intäkt och klicka på knappen Z-A (fallande). Notera att en 1:a visas på kolumnen Kundkategori och en 2:a visas i kolumnen Intäkt. Detta indikerar på att tabellen först sorteras i stigande ordning på kolumnen Kundkategori och därefter i fallande ordning efter kolumnen Intäk.

Läsa tillbaka tabellen till Excel

Nu har vi gjort alla magiska omvandlingar i Power Query. Notera att varje litet steg vi har gjort syns i panelen längst till höger. I den panelen kan man enkelt klicka sig framåt/bakåt i flödet och se hur dina data ser i varje steg. Det är med andra ord enkelt att följa omvandlingarna rent fysiskt .

Sista steget inne i Power Query-fönstret är att läsa över tabellen till Excel. Under menyfliken Start klickar du på knappen Stäng och läs in och väljer Stäng och läs in till.

I exemplet vill du ha den slutliga tabellen bredvid den blåa tabellen och väljer därför Tabell och På detta kalkylblad och klickar i önskad cell (L2).

Formatering

När du hämtar och omvandlar data kan du göra en massa saker, men formatering sker alltid i Excel-gränssnittet. Så vill du till exempel ha en viss formatering markerar du kolumnerna och väljer önskad formatering på sedvanligt sätt (Ctrl + 1 är kortkommandot för att öppna dialogrutan Formatera celler).

Tabellsummering

När du skickar tillbaka data från Power Query till Excel-gränssnittet skapas en så kallad Exceltabell. När du står i tabellen så blir menyfliken Tabelldesign tillgänglig. Under den menyfliken kan du klicka på knappen Summarad för att skapa en extra rad längst ner i tabellen som summerar dina data. Det går enkelt att klicka i fler celler i summaraden och via droplistan välja beräkningssätt. Snabbt, smidigt och fraktfritt .

Smidig uppdatering vid förändringar

Totalt har vi 16421 i kolumnen antal. Skriv in ”2” i cellen G3 där det nu står ”1”. Högerklicka i den nya tabellen till höger och välj Uppdatera. Din Power Query-fråga körs då och går igenom alla de steg ”du lärt den” och uppdaterar tabellen. Du ser resultatet genom att summan av kolumnen Antal nu blir 16422.

Så varje gång ny data tillkommer eller ändras, räcker det alltså att trycka på Uppdatera. Allt görs automatiskt via vårt frågeflöde som vi har byggt upp. Power Query hämtar data, filtrerar data, beräknar kolumner, grupperar och summerar grupperingarna och sorterar vårt data automatiskt .