Power Query som en del af arbejdet med BI – Eller "rent" i Excel
Power Query opfattes ofte som et trin i arbejdet med Microsoft BI værktøjer, men som det fremgår af grafikken, behøver du ikke tænke sådan:
Power Query kan altså anvendes helt uafhængigt af andre BI værktøjer i Excel til simpelthen at tilrette data i lister og derefter vise resultatet i andre lister (Excel tabeller). Det er her, vi traditionelt bruger Excel formler – typisk kolonne formler – og det tager ofte rigtig lang tid at forberede data til analyse med Excel formler og andre traditionelle Excel værktøjer. – Ofte længere tid end den efterfølgende, egentlige dataanalyse, f.eks. med pivot tabeller.
Så selv hvis du glemmer alt om BI, bør du stadigvæk kende til Power Query som et godt alternativ til omfattende brug af Excel formler til at forberede data lister til videre analyse.
Excel formler eller Power Query?
Excel formler
Der er ca. 450 Excel funktioner i de nyere versioner af Excel. Det kræver en del erfaring at bruge disse funktioner i Excel formler, både fordi der er så mange, og fordi der ofte skal bruges en kombination af flere funktioner i den enkelte formel.
Excel giver ikke meget hjælp til formelskrivning, så du skal som udgangspunkt selv udtænke formlerne. – Ikke uden grund kaldes Excel af nogle for verdens mest udbredte programmeringssprog – hvert fald til funktionsorienteret programmering. – Men hvis du ser på Excel med friske øjne, ville det måske have været rart at slippe for at kode så meget selv.
Power Query
Power-forespørgsler bygger på et funktionsorienteret programmeringssprog til beregninger kaldet M (M står for "Mashup"). M kan udføre mange af de samme transformationer og manipulationer som Excel funktioner – og langt flere!
M adskiller sig voldsomt fra Excel formler, så du vil næppe være i stand til at skrive M kode ud fra din viden om Excel formler. Men heldigvis laves det meste arbejde med opbygning af Power-forespørgsler i det grafiske interface Power Query-editor, der bl.a. dukker op i Excel, når du henter data via fanen Data, Hent og transformer data.
Så du behøver altså ikke at kode, når du bruger Power Query.
Og så er det en kæmpe fordel, at resultatet af en Power-forespørgsel lander i en separat Excel tabel, så du ændrer altså aldrig noget i dine oprindelige data.
Eksempler på brug af Power Query i Excel
Her prøver jeg at vise to eksempler på ganske almindelige kolonne transformationer, du kan have brug for i Excel, men hvor Power Query umiddelbart virker som et meget nemmere værktøj sammenlignet med brug af traditionelle Excel formler.
Eksempel 1 – Opsplitning af navne kolonne i Fornavne og efternavne
Du har en navneliste med personnavne i én kolonne, her en Excel tabel med en enkelt kolonne:
Kolonnen Navn skal splittes op i to kolonner med fornavne og efternavn efter det sidste mellemrum. – Det er standard reglen for placering af fornavne og efternavne i et officielt dokument, f.eks. et pas.
Excel løsning
Den traditionelle løsning i Excel er at oprette beregnede kolonner i tabellen:
Det kan lade sig gøre, men det kræver godt kendskab til Excels funktionsbibliotek.
Formlen i kolonnen Fornavne bliver f.eks.:
=HVIS.FEJL(VENSTRE([@Navn];SØG("⛇";UDSKIFT([@Navn];" ";"⛇";LÆNGDE([@Navn])-LÆNGDE(UDSKIFT([@Navn];" ";""))))-1);[@Navn]&"")
Formlen i kolonnen Efternavn bliver f.eks.:
=HVIS.FEJL(MIDT([@Navn];SØG("⛇";UDSKIFT([@Navn];" ";"⛇";LÆNGDE([@Navn])-LÆNGDE(UDSKIFT([@Navn];" ";""))))+1;255);"")
Idéen i disse formler er kort fortalt, at det sidste mellemrum i teksten udskiftes med et specialtegn, der ellers aldrig bruges i tekster (her tegnet "Black Snowman" ⛇), hvorefter teksten splittes efter positionen af dette tegn. – Måske nok smart, men lidt vel udfordrende for de fleste Excel brugere.
Og så kan kolonnen Navn naturligvis ikke fjernes, selvom den måske er overflødig efter oprettelse af de beregnede kolonner.
Power Query løsning
Der oprettes en Power Query-forespørgsel, og resultatet placeres i en separat Excel tabel:
I det grafiske værktøj er det noget med at vælge kommandoen Opdel kolonne og derefter indstillingerne Efter afgrænser og Afgrænser længst til højre. – Meget, meget nemmere end Excel formler i dette tilfælde;)
Eksempel 2 – Kombinere data fra relaterede tabeller
Du har en liste over salgsposteringer, her en Excel tabel med Dato og ProduktID. Men du har brug for også at kende prisen på det enkelt produkt, der ligger i separat prisliste, her en Excel tabel med ProduktID og Pris:
Excel løsning
Den traditionelle løsning i Excel er at oprette en beregnet kolonne i den første tabel:
Formlen i kolonnen Pris bruger f.eks. opslagsfunktionen LOPSLAG:
=LOPSLAG([@ProduktID];Prisliste;2;0)
Opslagsfunktioner giver anledning til en del udfordringer. – F.eks. vises her #I/T og 0 i to celler i kolonnen Pris, hvor det måske ville give mere mening med blanke celler, fordi der ikke er nogen data at hente i prislisten.
Power Query løsning
Der oprettes en Power Query-forespørgsel for hver tabel. I den første forespørgsel flettes priser fra den anden forespørgsel ind, og resultatet placeres i en separat Excel tabel:
I det grafiske værktøj er det noget med at vælge kommandoen Flet forespørgsler og derefter kolonnen ProduktID som matchende kolonne fra de to tabeller. – Igen nemt;)
Og nu vises der ingen data i de to celler i kolonnen Pris, hvor der ikke er nogen pris at hente i prislisten;)
Relevante kurser
4D A/S udbyder en række kurser, hvor vi kommer ind på Power Query, bl.a. nedenstående.
Kurser for dig, der arbejder "rent" i Excel
Excel avanceret
Kurset fokuserer på avancerede værktøjer og funktioner i Excel. Du får bl.a. en grundig introduktion til at transformeres og manipulere data i Excel med Power Query.
Herefter vil det være naturligt at gå i dybden med Power Query med dette kursus:
Kurset for dig, der vil fokusere på at indlæse og klargøre data med Power Query fra en lang række forskellige typer af datakilder med henblik på videre dataanalyse i Excel.
Kurser for dig, der vil arbejde i Power BI
Analyser og visualiser data med Power BI
Kurset giver en god introduktion til Power BI, men du får også en grundig introduktion til at transformeres og manipulere data med Power Query, inden der indlæses i datamodellen i Power BI Desktop.
Herefter vil det være naturligt at gå i dybden med Power Query med dette kursus:
Kurset for dig, der vil fokusere på at indlæse og klargøre data med Power Query fra en lang række forskellige typer af datakilder med henblik på videre dataanalyse i Power BI.
+45 33 14 71 44
