De termen "Power Query", "Power Pivot", "Power BI" en andere "powers" duiken steeds vaker op in artikelen en materialen over Microsoft Excel. In mijn ervaring begrijpt niet iedereen duidelijk wat er achter deze concepten zit, hoe ze met elkaar verbonden zijn en hoe ze een eenvoudige Excel-gebruiker kunnen helpen.
Laten we de situatie verduidelijken.
Power Query
In 2013 bracht een speciaal opgerichte groep ontwikkelaars binnen Microsoft een gratis invoegtoepassing voor Excel uit. Power Query (andere namen zijn Data Explorer, Get & Transform), die veel dingen kunnen doen die nuttig zijn voor het dagelijkse werk:
- Uploaden gegevens in Excel uit bijna 40 verschillende bronnen, waaronder databases (SQL, Oracle, Access, Teradata...), bedrijfs-ERP-systemen (SAP, Microsoft Dynamics, 1C...), internetdiensten (Facebook, Google Analytics, bijna alle websites).
- Gegevens verzamelen van Bestanden alle belangrijke gegevenstypen (XLSX, TXT, CSV, JSON, HTML, XML ...), zowel afzonderlijk als in bulk - van alle bestanden in de opgegeven map. Vanuit Excel-werkmappen kunt u automatisch gegevens van alle bladen tegelijk downloaden.
- Opruimen ontvangen gegevens van “garbage”: extra kolommen of rijen, herhalingen, service-informatie in de “header”, extra spaties of niet-afdrukbare tekens, etc.
- Breng gegevens in bestellen: correct hoofdlettergebruik, getallen-als-tekst, lacunes opvullen, de juiste "cap" van de tabel toevoegen, "sticky" tekst in kolommen ontleden en terug plakken, de datum in componenten verdelen, enz.
- op alle mogelijke manieren transformeren tabellen, ze in de gewenste vorm brengen (filteren, sorteren, de volgorde van kolommen wijzigen, transponeren, totalen toevoegen, kruistabellen uitvouwen tot plat en terugvouwen).
- Vervang gegevens van de ene tabel naar de andere door een of meer parameters te matchen, dwz een mooie vervangingsfunctie VPR (VERT.ZOEKEN) en zijn analogen.
Power Query is te vinden in twee versies: als aparte invoegtoepassing voor Excel 2010-2013, te downloaden van de officiële Microsoft-website, en als onderdeel van Excel 2016. In het eerste geval verschijnt er na installatie een apart tabblad in Excel:
In Excel 2016 is alle functionaliteit van Power Query standaard al ingebouwd en staat op het tabblad Data (Datum) als een groep Ophalen en converteren (Krijgen en transformeren):
De mogelijkheden van deze opties zijn volledig identiek.
Het fundamentele kenmerk van Power Query is dat alle acties voor het importeren en transformeren van gegevens worden opgeslagen in de vorm van een query - een reeks stappen in de interne Power Query-programmeertaal, die kortweg "M" wordt genoemd. Stappen kunnen altijd een willekeurig aantal keren worden bewerkt en opnieuw worden afgespeeld (refresh query).
Het hoofdvenster van Power Query ziet er meestal ongeveer zo uit:
Naar mijn mening is dit de nuttigste add-on die in dit artikel wordt vermeld voor een breed scala aan gebruikers. Veel taken waarvoor je ofwel vreselijk moest vervormen met formules of macro's moest schrijven, worden nu eenvoudig en mooi gedaan in Power Query. Ja, en met daaropvolgende automatische actualisering van de resultaten. En aangezien het gratis is, in termen van prijs-kwaliteitverhouding, is Power Query gewoonweg niet meer concurrerend en tegenwoordig een absolute must-have voor elke gemiddelde gevorderde Excel-gebruiker.
powerpivot
Power Pivot is ook een invoegtoepassing voor Microsoft Excel, maar ontworpen voor iets andere taken. Als Power Query gericht is op importeren en verwerken, dan is Power Pivot vooral nodig voor complexe analyse van grote hoeveelheden gegevens. Als eerste benadering kun je Power Pivot zien als een mooie draaitabel.
De algemene principes van werken in Power Pivot zijn als volgt:
- Wij zijn de eersten data laden in Power Pivot – 15 verschillende bronnen worden ondersteund: gemeenschappelijke databases (SQL, Oracle, Access …), Excel-bestanden, tekstbestanden, datafeeds. Daarnaast kun je Power Query gebruiken als databron, wat de analyse bijna alleseter maakt.
- Dan tussen geladen tafels verbindingen zijn geconfigureerd of, zoals ze zeggen, is gemaakt Gegevensmodel. Dit zal het in de toekomst mogelijk maken om rapporten op te bouwen over alle velden van bestaande tabellen alsof het één tabel is. En weer geen VPR.
- Indien nodig worden aanvullende berekeningen aan het datamodel toegevoegd met behulp van berekende kolommen (vergelijkbaar met een kolom met formules in een “slimme tabel”) en maatregelen (een analoog van het berekende veld in de samenvatting). Dit alles is geschreven in een speciale interne Power Pivot-taal genaamd DAX (Data Analysis eXpressions).
- Op het Excel-blad worden, volgens het gegevensmodel, de rapporten die voor ons van belang zijn gebouwd in de vorm draaitabellen en diagrammen.
Het hoofdvenster van Power Pivot ziet er ongeveer zo uit:
En zo ziet het gegevensmodel eruit, dwz alle geladen tabellen met gemaakte relaties:
Power Pivot heeft een aantal functies die het voor sommige taken een uniek hulpmiddel maken:
- Bij PowerPivot geen lijnlimiet (zoals in Excel). U kunt tabellen van elk formaat laden en er gemakkelijk mee werken.
- Power Pivot is erg goed in gegevens comprimeren wanneer u ze in het model laadt. Een origineel tekstbestand van 50 MB kan na het downloaden gemakkelijk 3-5 MB worden.
- Omdat Power Pivot 'onder de motorkap' in feite een volwaardige database-engine heeft, kan het grote hoeveelheden informatie verwerken erg snel. Moet u 10-15 miljoen records analyseren en een samenvatting maken? En dat allemaal op een oude laptop? Geen probleem!
Helaas is Power Pivot nog niet in alle versies van Excel opgenomen. Als u Excel 2010 heeft, kunt u deze gratis downloaden van de Microsoft-website. Maar als je Excel 2013-2016 hebt, dan hangt het allemaal af van je licentie, want. in sommige versies is het inbegrepen (Office Pro Plus bijvoorbeeld), en in sommige niet (Office 365 Home, Office 365 Personal, etc.) Hier lees je meer over.
Krachtkaarten
Deze add-on verscheen voor het eerst in 2013 en heette oorspronkelijk GeoFlow. Het is bedoeld voor visualisatie van geodata, dwz numerieke informatie op geografische kaarten. De initiële gegevens voor weergave zijn afkomstig van hetzelfde Power Pivot-gegevensmodel (zie de vorige paragraaf).
De demoversie van Power Map (bijna niet anders dan de volledige overigens) is weer helemaal gratis te downloaden van de Microsoft website. De volledige versie is opgenomen in sommige Microsoft Office 2013-2016-pakketten samen met Power Pivot - in de vorm van een knop 3D kaart tab Invoegen (Invoegen — 3D-kaart):
Belangrijkste kenmerken van Power Map:
- Maps kan zowel vlak als volumineus (globe) zijn.
- U kunt verschillende visualisatie typen (histogrammen, bellendiagrammen, hittekaarten, vlakvullingen).
- U kunt toevoegen tijdmeting, dwz animeer het proces en kijk hoe het zich ontwikkelt.
- Kaarten worden geladen vanuit de service Bing Maps, dat wil zeggen U heeft een zeer snelle internetverbinding nodig om te bekijken. Soms zijn er problemen met de juiste herkenning van adressen, omdat. de namen in de gegevens komen niet altijd overeen met die van Bing Maps.
- In de volledige (niet-demo) versie van Power Map kun je je eigen versie gebruiken downloadbare kaartenbijvoorbeeld om bezoekers van een winkelcentrum of prijzen voor appartementen in een woongebouw direct op de bouwtekening te visualiseren.
- Op basis van de gemaakte geovisualisaties kunt u video's rechtstreeks in Power Map maken (voorbeeld) om ze later te delen met degenen die de invoegtoepassing niet hebben geïnstalleerd of om ze op te nemen in een Power Point-presentatie.
machtsweergave
Deze invoegtoepassing werd voor het eerst geïntroduceerd in Excel 2013 en is ontworpen om uw gegevens tot leven te brengen met interactieve grafieken, grafieken, kaarten en tabellen. Soms worden hiervoor de termen gebruikt. dashboards (dashboard) or dashboards (score kaart). Het komt erop neer dat u een speciaal blad zonder cellen in uw Excel-bestand kunt invoegen - een Power View-dia, waar u tekst, afbeeldingen en veel verschillende soorten visualisaties kunt toevoegen op basis van uw gegevens uit het Power Pivot-gegevensmodel.
Het ziet er ongeveer zo uit:
De nuances hier zijn:
- De initiële gegevens zijn afkomstig van dezelfde plaats - van het Power Pivot-gegevensmodel.
- Om met Power View te kunnen werken, moet u Silverlight op uw computer installeren – Microsofts analoog van Flash (gratis).
Op de Microsoft-website staat trouwens een zeer degelijke training over Power View in .
Power BI
In tegenstelling tot de vorige is Power BI geen invoegtoepassing voor Excel, maar een apart product, dat een hele reeks tools is voor bedrijfsanalyse en -visualisatie. Het bestaat uit drie belangrijke elementen:
1. Power BI Desktop – een programma voor het analyseren en visualiseren van data, dat onder andere alle functionaliteit van de Power Query en Power Pivot add-ons + verbeterde visualisatiemechanismen uit Power View en Power Map bevat. U kunt het gratis downloaden en installeren vanaf de Microsoft-website.
In Power BI Desktop kunt u:
- Laad gegevens van over 70 verschillende bronnen (zoals in Power Query + extra connectoren).
- binden tabellen om te modelleren (zoals in Power Pivot)
- Voeg extra berekeningen toe aan gegevens met maatregelen и berekende kolommen op DAX (zoals in Power Pivot)
- Maak prachtige data gebaseerd interactieve rapporten met verschillende soorten visualisaties (zeer vergelijkbaar met Power View, maar nog beter en krachtiger).
- Publiceer rapporten gemaakt op de Power BI Service-site (zie volgend punt) en deze delen met collega's. Bovendien is het mogelijk om verschillende personen verschillende rechten (lezen, bewerken) te geven.
2. Power BI online-service – om het simpel te zeggen, dit is een site waar u en elke gebruiker in uw bedrijf hun eigen "sandbox" (werkruimte) hebben waar u rapporten kunt uploaden die zijn gemaakt in Power BI Desktop. U kunt ze niet alleen bekijken, maar zelfs bewerken, waardoor bijna alle functionaliteit van Power BI Desktop online wordt gereproduceerd. U kunt hier ook individuele visualisaties uit de rapporten van anderen lenen en uw eigen auteursdashboards van hen verzamelen.
Het ziet er ongeveer zo uit:
3. Power BI mobiel is een applicatie voor iOS / Android / Windows om verbinding te maken met de Power BI-service en de gemaakte rapporten en dashboards gemakkelijk op het scherm van uw telefoon of tablet te bekijken (niet te bewerken). Je kunt het hier (volledig gratis) downloaden.
Op een iPhone ziet het hierboven gegenereerde rapport er bijvoorbeeld als volgt uit:
En dit alles met behoud van interactiviteit en animatie + opgesloten voor aanraking en tekenen op het scherm met een pen. Zeer comfortabel. Zo komt business intelligence op elk moment en op elke plaats beschikbaar voor alle sleutelpersonen van het bedrijf - alleen internettoegang is nodig.
Power BI-prijsplannen. Power BI Desktop en Mobile zijn standaard gratis, en de meeste Power BI Service-functies zijn ook gratis. Dus voor persoonlijk gebruik of gebruik binnen een klein bedrijf, hoef je geen cent te betalen voor al het bovenstaande en kun je veilig op het plan blijven Gratis. Als u rapporten met collega's wilt delen en hun toegangsrechten wilt beheren, gaat u naar: BESS ($ 10 per maand per gebruiker). Is er nog wat? Premium – voor grote bedrijven (> 500 gebruikers) die aparte opslag- en servercapaciteiten voor data nodig hebben.
- Project Gantt-diagram in Excel met Power Query
- Een database maken in Excel met Power Pivot
- Visualisatie van beweging langs de route op de kaart in Power Map