inhoudsopgave

Wat is power query?

Power Query is een business intelligence tool die beschikbaar is in Excel waarmee u gegevens uit veel verschillende bronnen kunt importeren en vervolgens uw gegevens kunt opschonen, transformeren en opnieuw vormgeven als dat nodig is.

hiermee kunt u een query één keer instellen en deze vervolgens opnieuw gebruiken met een eenvoudige verversing. Het is ook vrij krachtig. Power Query kan importeren en schoon miljoenen rijen in het gegevensmodel voor analyse na. De gebruikersinterface is intuã tief en goed aangelegd, dus het is heel gemakkelijk op te halen., Het is een ongelooflijk korte leercurve in vergelijking met andere Excel-tools zoals formules of VBA.

het beste eraan is dat je geen code hoeft te leren of te gebruiken om er iets van te doen. De power query-editor registreert al uw transformaties stap voor stap en converteert ze naar de M-code voor u, vergelijkbaar met hoe de Macro-recorder met VBA.

als u uw eigen M-code wilt bewerken of schrijven, kunt u dat zeker, maar hoeft u dat zeker niet te doen.

haal de gegevens die in dit bericht worden gebruikt om mee te volgen.

wat kan Power Query doen?,

stel je voor dat je maandelijks een verkooprapport krijgt in een tekstbestand van je systeem dat er zo uitziet.

elke maand moet u naar de map gaan waar het bestand wordt geüpload en het bestand openen en de inhoud naar Excel kopiëren.

u gebruikt vervolgens de functie tekst naar kolom om de gegevens op te splitsen in nieuwe kolommen.

het systeem voert alleen het ID van de verkoper uit, dus u moet een nieuwe kolom aan de gegevens toevoegen en een VLOOKUP gebruiken om de verkoper te krijgen die aan elke ID is gekoppeld., Dan moet je de verkoop door Verkoper samenvatten en de Commissie berekenen om uit te betalen.

u moet ook de Product-ID koppelen aan de productcategorie, maar alleen de eerste 4 cijfers van de productcode hebben betrekking op de productcategorie. U maakt een andere kolom met de functie links om de eerste 4-cijfers van de productcode te krijgen en gebruikt vervolgens een VLOOKUP om de productcategorie te krijgen. Nu kunt u de gegevens per categorie samenvatten.,

misschien duurt het slechts een uur per maand om te doen, maar het is vrij hersenloos werk dat niet plezierig is en neemt de tijd weg die je daadwerkelijk kunt besteden aan het analyseren van de gegevens en het produceren van betekenisvol inzicht.

Met Power Query kan dit allemaal worden geautomatiseerd tot een klik op de knop Vernieuwen op maandelijkse basis. Het enige wat u hoeft te doen is het bouwen van de query een keer en hergebruik het, het opslaan van een uur werk elke maand!

Waar is Power Query?,

Power Query is beschikbaar als een invoegtoepassing om te downloaden en te installeren voor Excel 2010 en 2013 en zal verschijnen als een nieuw tabblad in het lint met het label Power Query. In 2016 werd het hernoemd naar Get & Transform en verschijnt in het tabblad Data zonder de noodzaak om een invoegtoepassing te installeren.

uw Gegevens importeren met Power Query

uw gegevens importeren met Power Query is eenvoudig. Excel biedt veel voorkomende dataverbindingen die toegankelijk zijn vanaf het tabblad Gegevens en kunnen worden gevonden via de opdracht Gegevens ophalen.,

  • Gegevens ophalen uit een enkel bestand, zoals een Excel-werkmap, tekst-of CSV-bestand, XML-en JSON-bestanden. U kunt ook meerdere bestanden importeren vanuit een bepaalde map.
  • haal gegevens uit verschillende databases zoals SQL Server, Microsoft Access, Analysis Services, SQL Server Analysis Server, Oracle, IBM DB2, MySQL, PostgreSQL, Sybase, Teradata en SAP HANA databases.
  • Gegevens ophalen van Microsoft Azure
  • Gegevens ophalen van online diensten zoals Sharepoint, Microsoft Exchange, Dynamics 365, Facebook en Salesforce.,
  • krijg gegevens van andere bronnen zoals een tabel of Bereik in de huidige werkmap, van het web, een Microsoft Query, Hadoop, OData feed, ODBC en OLEDB.
  • We kunnen twee queries samenvoegen, vergelijkbaar met het samenvoegen van twee queries in SQL.
  • we kunnen een query toevoegen aan een andere query die lijkt op een samenvoeging van twee query ‘ s in SQL.

opmerking: de beschikbare opties voor gegevensverbinding zijn afhankelijk van uw versie van Excel.,

er zijn een paar van de meest voorkomende query types beschikbaar in het bovenste niveau van de ribbon commando ‘ s gevonden in de Get & Transform sectie van het Data tabblad. Vanaf hier kunnen we gemakkelijk toegang krijgen tot de van tekst / CSV, van Web en van tabel/bereik queries. Deze zijn net gedupliceerd buiten de opdracht Get Data voor het gemak van gebruik, omdat je waarschijnlijk zal worden met behulp van deze vaker.,

afhankelijk van het type dataverbinding dat u kiest, zal Excel u door de ingestelde verbinding leiden en er kunnen verschillende opties zijn om tijdens het proces te selecteren.

aan het einde van het installatieproces komt u bij het venster gegevensvoorbeeld. U kunt hier een voorbeeld van de gegevens bekijken om er zeker van te zijn dat het is wat u verwacht. U kunt vervolgens de gegevens laden zoals het is door op de knop laden te drukken, of u kunt doorgaan naar de query-editor om alle gegevenstransformatiestappen toe te passen door op de knop Bewerken te drukken.,

een eenvoudig voorbeeld van het importeren van gegevens in een Excel-bestand

laten we eens kijken naar het importeren van gegevens uit een Excel-werkmap in actie. We gaan een Excel-bestand importeren, genaamd Office Supply Sales Data.xlsx. Het bevat verkoopgegevens op een blad genaamd verkoopgegevens en klantgegevens op een ander blad genaamd klantgegevens. Beide gegevensbladen beginnen in cel A1 en de eerste rij van de gegevens bevat kolomkoppen.

Ga naar het tabblad Data en selecteer de opdracht Data ophalen in de sectie Get & Transform Data., Ga vervolgens naar van Bestand en kies uit werkmap.

Dit opent een bestandkiezer menu waar u kunt navigeren naar het bestand dat u wilt importeren. Selecteer het bestand en druk op de knop Importeren.

na het selecteren van het bestand dat u wilt importeren, wordt het venster data preview Navigator geopend. Dit geeft u een lijst met alle objecten die beschikbaar zijn om uit de werkmap te importeren. Schakel het selectievakje in om meerdere items te selecteren, omdat we gegevens uit twee verschillende bladen importeren. Nu kunnen we zowel de Klantgegevens als de verkoopgegevens controleren.,

wanneer u op een van de objecten in de werkmap klikt, kunt u een voorbeeld van de gegevens voor het zien aan de rechterkant van het navigator venster. Dit is geweldig voor een sense check om er zeker van te zijn dat je het juiste bestand hebt.

Als u tevreden bent dat u alles hebt wat u nodig hebt uit de werkmap, kunt u op de knop Bewerken of Laden drukken. De knop Bewerken brengt u naar de query-editor waar u uw gegevens kunt transformeren voordat u deze laadt. Als u op de knop laden drukt, worden de gegevens in tabellen in nieuwe bladen in de werkmap geladen.,

in dit eenvoudige voorbeeld omzeilen we de editor en gaan we direct naar het laden van de gegevens in Excel. Druk op de kleine pijl naast de knop laden om toegang te krijgen tot de Opties Laden. Dit geeft je een paar extra laadopties.

We zullen ervoor kiezen om de gegevens in een tabel in een nieuw blad te laden, maar er zijn verschillende andere opties. U kunt de gegevens ook rechtstreeks in een draaitabel of draaigrafiek laden, of u kunt voorkomen dat de gegevens worden geladen en gewoon een verbinding met de gegevens maken.,

nu worden de tabellen in nieuwe bladen in Excel geladen en hebben we ook twee query ‘ s die snel kunnen worden vernieuwd als de gegevens in de originele werkmap ooit worden bijgewerkt.

de Query-Editor

na het doorlopen van De Gids Voor het verbinden van uw gegevens en het selecteren van de optie Bewerken, wordt u gepresenteerd met de query-editor. Dit is waar alle data transformatie stappen zullen worden gemaakt of bewerkt. Er zijn 6 belangrijkste gebied in de editor om vertrouwd te raken met.,

  1. Het Lint – de gebruikersinterface voor de editor is vergelijkbaar met Excel en gebruikt een visueel lintstijl commando centrum. Het organiseert data transformatie commando ‘ s en andere power query opties in 5 belangrijkste tabbladen.
  2. Querylijst – dit gebied toont alle query ‘ s in de huidige werkmap. U kunt naar elke query vanuit dit gebied navigeren om het te bewerken.
  3. Data Preview – in dit gebied ziet u een voorbeeld van de gegevens met alle transformatiestappen die momenteel worden toegepast., U kunt ook toegang krijgen tot een groot deel van de transformatie commando ‘ s hier, hetzij van de filterpictogrammen in de kolomkoppen of met een klik met de rechtermuisknop op de kolomkop.
  4. formulebalk-hier kunt u de M-code van de huidige transformatiestap bekijken en bewerken. Elke transformatie die u maakt op uw gegevens wordt geregistreerd en verschijnt als een stap in het gebied toegepaste stappen.
  5. Properties-Hier kunt u uw zoekopdracht een naam geven. Wanneer u de query sluit en in een Excel-tabel laadt, maakt power query een tabel met dezelfde naam als de bronquery als de tabelnaam nog niet is gebruikt., De query naam is ook hoe de M-code zal verwijzen naar deze query als we nodig hebben om het te vragen in een andere query.
  6. Toegepaste stappen-dit gebied is een chronologische lijst van alle transformatiestappen die op de gegevens zijn toegepast. U kunt de stappen hier doorlopen en de wijzigingen in het gegevensvoorbeeldgebied bekijken. U kunt ook stappen in de query hier verwijderen, wijzigen of Opnieuw ordenen.

de Querylijst

De Querylijst heeft andere mogelijkheden dan alleen het weergeven van alle query ‘ s van de huidige werkmap.,

een van de primaire functies van de query-lijst is navigatie. Het is niet nodig om de query-editor te verlaten om te schakelen aan welke query u werkt. U kunt links klikken op een query om te schakelen. De zoekopdracht die u momenteel gebruikt, wordt gemarkeerd in een lichtgroene kleur.

wanneer u uiteindelijk de editor afsluit met de knop sluiten en laden, worden wijzigingen in alle query ‘ s die u bewerkt opgeslagen.

u kunt de querylijst verbergen om meer ruimte te creëren voor het gegevensvoorbeeld., Klik met de linkermuisknop op de kleine pijl in de rechterbovenhoek om de lijst tussen verborgen en zichtbaar te schakelen.

Als u met de rechtermuisknop op een zoekopdracht in de lijst klikt, zijn er verschillende opties beschikbaar.

  • kopiëren en plakken-kopieer en plak een query om er nog een kopie van te maken.
  • Delete-de query verwijderen. Als u per ongeluk een query verwijdert, is er geen knop Ongedaan maken, maar u kunt de query-editor verlaten zonder op te slaan via sluiten en laden om uw query te herstellen.
  • hernoemen-uw zoekopdracht hernoemen., Dit is hetzelfde als het hernoemen van het gedeelte Eigenschappen aan de linkerkant van de editor.
  • dupliceren-maak nog een kopie van de query. Dit is hetzelfde als kopiëren en plakken, maar verandert het proces in een stap.
  • verplaatsen naar Groep-Plaats uw query ‘ s in een mapachtige structuur om ze georganiseerd te houden wanneer de lijst groot wordt.
  • omhoog en Omlaag verplaatsen-herschik de volgorde waarin uw vragen verschijnen in de lijst of in de mapgroepen om toe te voegen aan uw organisatorische inspanningen. Dit kan ook worden gedaan door het slepen en neerzetten van de query naar een nieuwe locatie.,
  • Create Function-Verander uw query in een query functie. Hiermee kunt u een parameter doorgeven aan de query en resultaten retourneren op basis van de parameter doorgegeven.
  • converteren naar Parameter-Hiermee kunt u parameters converteren naar queries Of queries naar parameters.
  • geavanceerde Editor-Open de geavanceerde editor om de M-code voor de query te bewerken.
  • Properties-Hiermee kunt u de naam van de query wijzigen, een beschrijvingstekst toevoegen en de optie Snelle gegevensbelasting voor de query inschakelen.,

Als u met de rechtermuisknop op een leeg gebied in de query-lijst klikt, kunt u een nieuwe query aanmaken.

the data Preview

de belangrijkste taak van het data preview-gebied is om transformatiestappen toe te passen op uw gegevens en een voorbeeld te tonen van de stappen die u toepast.

in het gegevensvoorbeeldgebied kunt u kolommen selecteren met een paar verschillende methoden. Een kolom wordt gemarkeerd in een lichtgroene kleur wanneer deze is geselecteerd.

  • selecteer een enkele kolom met een linker muisklik op de kolomkop.,
  • Selecteer meerdere aangrenzende kolommen met een linker muisknop op de eerste kolomkop, houd Shift ingedrukt en klik met de linker muisknop op de laatste kolomkop.
  • Selecteer meerdere niet-aangrenzende kolommen door CTRL ingedrukt te houden en vervolgens met de linkermuisknop te klikken op kolomkoppen die u wilt selecteren.

u kunt dan alle relevante gegevenstransformatiestappen toepassen op geselecteerde kolommen van het lint of bepaalde stappen kunnen worden benaderd met een klik met de rechtermuisknop op de kolomkop. Opdrachten die niet beschikbaar zijn voor de geselecteerde kolom of kolommen worden grijs weergegeven in het lint.,

elke kolom heeft een gegevenstype-pictogram aan de linkerkant van de kolomkop. U kunt er met de linkermuisknop op klikken om het gegevenstype van de kolom te wijzigen.

met behulp van de Locale optie kunt u het formaat van het gegevenstype instellen met behulp van de conventie vanaf verschillende locaties. Als u bijvoorbeeld de datum in het Amerikaanse m/d/yyyy formaat wilt weergeven in plaats van de gebruikelijke dd/mm/yyyy, kunt u Verenigde Staten selecteren als de landinstelling.,

Er is een klein tabelpictogram in de linkerbovenhoek van het gegevensvoorbeeld, u kunt hierop met de rechtermuisknop of met de linkermuisknop klikken om toegang te krijgen tot verschillende acties die de hele tabel beïnvloeden.

het hernoemen van een kolomkop is heel eenvoudig. Dubbelklik met de linkermuisknop op een kolomkop en typ vervolgens uw nieuwe naam en druk op Enter wanneer u klaar bent.

u kunt de volgorde van elke kolom wijzigen met een klik met de linker muisknop en sleep actie., De groene rand tussen twee kolommen wordt de nieuwe locatie van de gesleepte kolom wanneer u de linker muisknop loslaat.

elke kolom heeft ook een filter aan de rechterkant. Klik hier met de linker muisknop om uw gegevens te sorteren en te filteren. Dit filter menu is zeer vergelijkbaar met de filters gevonden in een gewone spreadsheet en zal werken op dezelfde manier.

de lijst van getoonde posten is gebaseerd op een steekproef van de gegevens, zodat niet alle beschikbare posten in de gegevens kunnen worden opgenomen. U kunt meer laden door te klikken op de laad meer tekst in het blauw.,

veel transformaties in het lintmenu zijn ook toegankelijk vanuit het gegevensvoorbeeldgebied met een klik met de rechtermuisknop op de kolomkop. Een deel van de actie die u selecteert in dit rechtsklikmenu vervangt de huidige kolom. Als u een nieuwe kolom wilt maken, gebruikt u in plaats daarvan een opdracht op het tabblad Kolom toevoegen.

de toegepaste stappen

elke transformatie die u maakt naar uw gegevens zal verschijnen als een stap in het gebied Toegepaste stappen. Het stelt u ook in staat om te navigeren door uw zoekopdracht., Klik met de linkermuisknop op een stap en de data preview wordt bijgewerkt om alle transformaties tot en met die stap weer te geven.

u kunt op elk gewenst moment nieuwe stappen in de query invoegen door de vorige stap te selecteren en vervolgens de transformatie te maken in het gegevensvoorbeeld. Power Query zal dan vragen of u deze nieuwe stap wilt invoegen. Voorzichtig echter, omdat dit de volgende stappen die verwijzen naar iets wat je veranderd kan breken.

u kunt alle stappen die zijn toegepast verwijderen met behulp van de x aan de linkerkant van de naam van de stap in het gebied Toegepaste stappen., Voorzichtigheid is echter nodig, alsof een van de volgende stappen afhankelijk is van de stap die u probeert te verwijderen, zult u uw query te breken. Dit is waar verwijderen tot het einde van het rechtsklikmenu handig kan zijn.

veel van de transformatiestappen die beschikbaar zijn in power query zullen verschillende invoerparameters voor gebruikers en andere instellingen hebben. Als u een filter op de productkolom toepast om alle items weer te geven die niet met de Pen beginnen, kunt u later beslissen dat u deze filterstap moet wijzigen om alle items weer te geven die niet gelijk zijn aan de Pen., U kunt deze bewerkingen uitvoeren vanuit het toegepaste Stapgebied.

sommige stappen hebben een klein tandwielpictogram aan de rechterkant. Hiermee kunt u de ingangen en instellingen van die stap bewerken.

u kunt de volgorde waarin de stappen worden uitgevoerd in uw zoekopdracht herschikken. Gewoon links klikken op een stap en sleep het naar een nieuwe locatie. Een groene lijn tussen de stappen geeft de nieuwe locatie aan. Dit is een andere die je nodig hebt om voorzichtig te zijn met als een heleboel stappen zal afhangen van eerdere stappen, en het veranderen van de volgorde kan fouten maken als gevolg van dit.,

Klik met de rechtermuisknop op een stap om een menu met opties te openen.

  • Instellingen bewerken – Hiermee kunt u de instellingen van de stap bewerken, vergelijkbaar met het tandwielpictogram aan de rechterkant van de stap.
  • hernoemen – hiermee kunt u het steps-label hernoemen. In plaats van de weergave van de generieke naam zoals “gefilterde Rijen”, je zou kunnen hebben dit scherm iets als “gefilterde product rijen op pennen”, zodat u gemakkelijk kunt identificeren wat de stap doet.
  • Delete – hiermee wordt de huidige stap verwijderd, vergelijkbaar met de x aan de linkerkant van de stap.,
  • Delete Until End-Hiermee kunt u de huidige stap plus alle stappen tot het einde verwijderen. Omdat stappen kunnen afhangen van eerdere stappen, is het verwijderen van alle stappen na een stap een goede manier om fouten te voorkomen.
  • stap na invoegen – Hiermee kunt u een nieuwe stap na de huidige stap invoegen.
  • omhoog en Omlaag verplaatsen-hiermee kunt u de query-stappen herschikken, vergelijkbaar met de slepen en neerzetten methode.
  • vorige uitpakken-dit kan een zeer nuttige optie zijn. Hiermee kunt u een nieuwe kopie van de query te maken tot de geselecteerde stap.,

de formulebalk

wanneer u op verschillende stappen van het transformatieproces klikt in het gebied Toegepaste stappen, wordt de formulebalk bijgewerkt om de M-code te tonen die voor die stap is gemaakt. Als de gegenereerde M-code langer is dan de formulebalk, kunt u de formulebalk uitbreiden met de pijlknop aan de rechterkant.

u kunt de M-code voor een stap rechtstreeks vanuit de formulebalk bewerken zonder de geavanceerde editor te hoeven openen., In dit voorbeeld hebben we ons filter veranderd van “Pen” naar “stoel” door in de formulebalk te typen en vervolgens op Enter te drukken of het vinkje aan de linkerkant te gebruiken om de wijziging te bevestigen. Druk op Esc of gebruik de X op de
links om wijzigingen te negeren.

het tabblad Bestand

het tabblad Bestand bevat verschillende opties voor het opslaan van wijzigingen in uw query ‘s, evenals opties en instellingen voor energiequery’ s.

  • Close & Load – hiermee slaat u uw query ‘ s op en laadt u uw huidige query in een Excel-tabel in de werkmap.,
  • Close & Laden naar – dit opent het menu Gegevens importeren met verschillende opties om uit te kiezen.
  • negeren & sluiten – hiermee worden alle wijzigingen die u tijdens uw sessie in de editor hebt aangebracht, genegeerd en wordt de editor gesloten.

Opmerking, U moet de werkmap nog steeds op de reguliere manier opslaan om eventuele wijzigingen in query ‘ s te behouden als u de werkmap sluit.

Close & Load and Close & Load To commando ‘ s zijn ook beschikbaar op het tabblad Home.,

opties voor het laden van gegevens

wanneer u de optie Close & Load To gebruikt om de editor te verlaten, opent u het menu Gegevens importeren.

u kunt ervoor kiezen om de query te laden in een tabel, draaitabel, draaigrafiek of alleen een verbinding te maken voor de query. De optie Alleen verbinding betekent dat er geen gegevensuitvoer naar de werkmap is, maar u kunt deze query nog steeds gebruiken in andere query ‘ s. Dit is een goede optie als de query een tussenstap is in een datatransformatieproces.,

u kunt ook de locatie selecteren waarnaar u in uw werkmap wilt laden als u in de vorige sectie een tabel, draaitabel of draaigrafiek hebt geselecteerd. U kunt een cel in een bestaand werkblad kiezen of deze in een nieuw blad laden dat Excel automatisch voor u maakt.

de andere optie die u krijgt is de voeg deze gegevens toe aan het gegevensmodel. Dit zal u toelaten om de gegevensuitvoer te gebruiken in Power Pivot en gebruik maken van andere Data Model functionaliteit zoals het bouwen van relaties tussen tabellen. Het datamodel Excel ‘ s nieuwe efficiënte manier om grote hoeveelheden gegevens op te slaan en te gebruiken.,

de query ‘ s & aansluitingen venster

wanneer u buiten de power query-editor werkt, kunt u alle query ’s in de werkmap zien en ermee communiceren via het query’ s & aansluitingen venster. Om dit te openen, gaat u naar het tabblad Data in het reguliere Excel-lint en drukt u vervolgens op de Queries & verbindingen commandoknop gevonden in de Queries & verbindingen sectie.

wanneer het geopend wordt, wordt het aan de rechterkant van de werkmap gekoppeld., U kunt het ongedaan maken door links te klikken op de titel en te slepen. U kunt het slepen naar de linkerkant en dock het daar of laat het drijven. U kunt het formaat van het venster ook wijzigen door met de linker muisknop te klikken en de randen te slepen.

Dit lijkt erg op de query-lijst in de editor en u kunt veel van dezelfde acties uitvoeren met een klik met de rechtermuisknop op elke query.

een optie die niet in het rechtsklikmenu van de query-lijst staat, is de optie Laden naar., Hiermee kunt u de laadoptie voor elke query wijzigen, zodat u alleen Verbindingsquery ‘ s kunt wijzigen om in een Excel-tabel in de werkmap te worden geladen.

een ander belangrijk punt is dat wanneer u met de muis over een query gaat, Excel een voorbeeld van Peekgegevens genereert. Dit toont u wat basisinformatie over de query.

  • data Preview-Dit is een live preview van de gegevens, vergelijkbaar met bij het instellen van een query.,
  • kolommen – dit geeft u een lijst van alle kolommen in de uiteindelijke resultaten van de query samen met een telling van het aantal kolommen er zijn. Als u op een van hen klikt, wordt de kolom in het gegevensvoorbeeld gemarkeerd.
  • Laatst ververst – dit geeft aan wanneer de gegevens voor het laatst ververst zijn.
  • laadstatus-dit geeft aan of de gegevens worden geladen in een tabel, draaitabel, draaigrafiek of alleen een verbinding is.
  • gegevensbronnen-Dit toont u de bron van de gegevens samen met een telling van het aantal bestanden als u het een van Map query.,
  • weergave in werkblad-als u hierop klikt, komt u bij de uitvoertabel als de query is geladen in een tabel, draaitabel of draaitabel.

u kunt deze Peekweergave ook openen door met de rechtermuisknop op de query te klikken en de peek tonen te selecteren.

er zijn ook enkele nuttige berichten weergegeven in de Queries & aansluitingen venster voor elke query. Het toont u of de query alleen een verbinding is, of er fouten waren toen de query voor het laatst liep, of hoeveel rijen geladen zijn.,

het tabblad Home

het tabblad Home bevat alle acties, transformaties en instellingen die van invloed zijn op de hele tabel.

  1. Close-u kunt toegang krijgen tot de Close & Load en Close & Load naar Opties vanaf hier. Deze zijn ook beschikbaar in het menu Bestand.
  2. Query-u kunt het gegevensvoorbeeld voor de huidige query of alle query-verbindingen vernieuwen., U kunt ook de eigenschappen-instellingen en de geavanceerde editor voor de huidige query openen en er zijn opties onder de knop Beheren om de huidige query te verwijderen, te dupliceren of te verwijzen.
  3. kolommen beheren – U kunt naar specifieke kolommen navigeren en ervoor kiezen om kolommen te behouden of te verwijderen.
  4. rijen verkleinen – u kunt de rijen met gegevens beheren in deze sectie. Er zijn veel opties om bepaalde rijen te behouden of bepaalde rijen te verwijderen. Houd of verwijder de bovenste n-rijen, de onderste n-rijen, een bepaald rijbereik, afwisselende rijen, dubbele rijen of rijen met fouten., Een optie die alleen beschikbaar is voor het verwijderen van Rijen is om lege rijen te verwijderen.
  5. Sorteer-u kunt elke kolom in oplopende of aflopende volgorde sorteren.
  6. Transform-deze sectie bevat een mix van nuttige transformatieopties.
    • kolommen splitsen – hiermee kunt u de gegevens in een kolom splitsen op basis van een scheidingsteken of tekenlengte.
    • groep door-Hiermee kunt u uw Gegevens Groeperen en samenvatten vergelijkbaar met een groep door in SQL.
    • gegevenstype – Hiermee kunt u het gegevenstype van elke kolom wijzigen.,
    • gebruik de eerste rij als kopteksten-Hiermee kunt u de eerste rij met gegevens naar kolomkoppen promoveren of de kolomkoppen degraderen naar een rij met gegevens.
    • waarden vervangen – Hiermee kunt u elke waarde uit een kolom vinden en vervangen.
  7. combineren-deze secties bevatten alle commando ’s om uw query met andere query’ s samen te voegen. U kunt samenvoegen, query ‘ s toevoegen of bestanden combineren wanneer u werkt met een query van mappen.
  8. Parameters-Power Query stelt u in staat om parameters voor uw query ‘ s te maken., Bijvoorbeeld bij het opzetten van een van Map query, wilt u misschien dat het mappad een parameter is, zodat u eenvoudig de locatie kunt wijzigen. U kunt bestaande parameters maken en beheren vanuit deze sectie.
  9. gegevensbronnen-deze sectie bevat de gegevensbroninstellingen, inclusief machtigingsbeheer voor alle gegevensbronnen waarvoor wachtwoorden moeten worden gebruikt.
  10. nieuwe Query-u kunt nieuwe query ‘ s maken uit nieuwe gegevensbronnen of eerder gebruikte gegevensbronnen uit deze sectie.,

het verschil tussen de tabbladen transformeren en kolom toevoegen

het grootste deel van alle beschikbare transformaties in Power query kan worden benaderd via het tabblad transformeren of het tabblad Kolom toevoegen.

u zou kunnen denken dat er veel duplicatie is tussen deze twee tabbladen. Bijvoorbeeld, beide tabbladen bevatten een van tekst sectie met veel van dezelfde commando ‘ s. Het is niet echt het geval, er is een subtiel verschil!,

wanneer u een opdracht van het tabblad Kolom toevoegen gebruikt die in beide tabbladen wordt gevonden, zal er een nieuwe kolom met de getransformeerde gegevens worden aangemaakt en zal de oorspronkelijke kolom intact blijven. Terwijl het gebruik van de equivalente opdracht van het tabblad transformeren de oorspronkelijke kolom zal veranderen en er geen nieuwe kolom wordt gemaakt.

Dit is een kritiek punt om je bewust van te zijn!

The Transform Tab

De Transform tab sections.

  1. Tabel – deze sectie bevat commando ‘ s die de hele tabel transformeren., U kunt uw query groeperen en samenvoegen, rijen naar kopteksten bevorderen, kopteksten naar rijen demonteren, uw gegevens transponeren, rijvolgorde omkeren en Rijen tellen.
  2. elke kolom – deze sectie bevat opdrachten die op elke kolom met gegevens werken, ongeacht het gegevenstype. U kunt het gegevenstype wijzigen, automatisch het gegevenstype detecteren en wijzigen, de kolomkop hernoemen, waarden zoeken en vervangen, waarden naar beneden (of omhoog) vullen om lege of nulls te vervangen door de waarde erboven (of eronder), kolommen draaien of loskoppelen, kolommen verplaatsen naar een nieuwe locatie of een kolom converteren naar een lijst.,
  3. tekstkolom – deze sectie bevat opdrachten voor tekstgegevens. U kunt kolommen splitsen met een scheidingsteken, de behuizing opmaken, bijsnijden en opschonen, twee of meer kolommen samenvoegen, tekst extraheren en XML-of JSON-objecten ontleden.
  4. Aantal kolom – deze sectie bevat opdrachten voor numerieke gegevens. U kunt verschillende aggregaties zoals sommen en gemiddelden uit te voeren, standaard algebra operaties of trigonometrie en ronde getallen omhoog of omlaag uit te voeren.
  5. Date & Tijdkolom – deze sectie bevat opdrachten voor datum-en tijdgegevens., U kunt informatie halen uit uw data, tijden en duur gegevens.
  6. gestructureerde kolom – deze sectie bevat opdrachten voor het werken met geneste gegevensstructuren, zoals wanneer uw kolom tabellen bevat.

het tabblad Kolom toevoegen

het tabblad Kolom toevoegen bevat veel commando ‘ s die vergelijkbaar zijn met het tabblad transformeren, maar het belangrijkste verschil is dat ze een nieuwe kolom zullen aanmaken met de transformatie.

  1. algemeen-in deze sectie kunt u nieuwe kolommen toevoegen op basis van formules of aangepaste functies., U kunt ook indexkolommen toevoegen of een kolom dupliceren vanaf hier.
  2. uit tekst – zeer vergelijkbaar met de uit tekst sectie in het tabblad transformeren, maar deze commando ‘ s zullen een nieuwe kolom aanmaken met de transformatie.
  3. From Number – zeer vergelijkbaar met De From Number sectie in de Transform tab, maar deze commando ‘ s zullen een nieuwe kolom aanmaken met de transformatie.
  4. vanaf datum & tijd – zeer vergelijkbaar met de vanaf datum & tijd sectie in de Transform tab, maar deze commando ‘ s zullen een nieuwe kolom aanmaken met de transformatie.,

het tabblad Weergave

het tabblad Weergave is vrij schaars in vergelijking met de andere tabbladen. Er zijn geen transformatiecommando ‘ s in te vinden. De meeste Power Query gebruikers zullen zelden nodig hebben om dit gebied te gebruiken, maar er zijn nog steeds een paar dingen de moeite waard te weten over.

  1. lay-out – in deze sectie kunt u het Query-instellingenpaneel (dat de eigenschappen en toegepaste stappen bevat) en de formulebalk weergeven of verbergen.,
  2. Data Preview – in deze sectie kunt u witruimtetekens tonen of verbergen of het lettertype omzetten in een vaste breedte-lettertype in het gegevensvoorbeeldgebied. Dit is handig bij het omgaan met gegevens gescheiden door een bepaald aantal tekens.
  3. kolommen-Hiermee kunt u naar een bepaalde kolom gaan en deze selecteren in het gegevensvoorbeeld. Dit commando is ook beschikbaar in het tabblad Home.
  4. Parameters-Hiermee kunt u parametrering inschakelen in gegevensbronnen en transformatiestappen.
  5. Geavanceerd-Hiermee opent u de geavanceerde query-editor die de M-code voor de query toont., Dit is ook beschikbaar op het tabblad Home.
  6. afhankelijkheden-dit opent een diagramweergave van de query afhankelijkheden in de werkmap.

in het bijzonder is de weergave van Query-afhankelijkheden een nuttige bron waarmee u een visuele weergave van de processtroom van gegevenstransformatie kunt zien.

conclusies

Power Query kan op het eerste gezicht overweldigend lijken voor iemand die er nieuw in is, maar de gebruikersinterface is zeer goed ingedeeld en gemakkelijk te vangen. Hoewel het misschien nieuw is voor een gebruiker, moeten veel van de concepten al bekend zijn voor een Excel-gebruiker.,

vertrouwd raken met alle delen van de editor en de lay-out van de ribbon tabs is een essentiële eerste stap in het verkennen van Power Query en het opnemen in uw dagelijkse werk.

hoewel er veel te leren valt over Power Query, is het de moeite waard om de tijd in te zetten om te leren. Er is een enorm potentieel om tijd te besparen in repetitieve data reiniging en opmaak taken met het. Het is een van de krachtigste en handigste tools die sinds draaitabellen aan Excel zijn toegevoegd.

wilt u nog meer Power query goedheid?, Bekijk dan deze geweldige Power Query Tips om u te helpen het meeste uit te halen!