Gegevens importeren en vormgeven in Excel voor Mac (Power Query)

Excel voor de Mac bevat Power Query (ook wel Get & transform genoemd) om meer mogelijkheden te bieden bij het importeren, vernieuwen en verifiëren van gegevensbronnen, het beheren van Power Query gegevensbronnen, het wissen van referenties, het wijzigen van de locatie van op bestanden gebaseerde gegevensbronnen en het vormgeven van de gegevens in een tabel die aan uw vereisten voldoet. U kunt ook een Power Query-query maken met behulp van VBA.

Microsoft 365 Legacy

Opmerking: SQL Server databasegegevensbron kan alleen worden geïmporteerd in Insiders Beta.

U kunt gegevens importeren in Excel met behulp van Power Query uit een groot aantal gegevensbronnen: Excel werkmap, Text/CSV, XML, JSON, SQL Server Database, SharePoint Online Lijst, OData, Lege tabel en Lege query.

Selecteer Gegevens > Gegevens ophalen.
Als u de gewenste gegevensbron wilt selecteren, selecteert u Gegevens ophalen (Power Query).
Selecteer in het dialoogvenster Gegevensbron kiezen een van de beschikbare gegevensbronnen.
Maak verbinding met de gegevensbron. Zie Gegevens importeren uit gegevensbronnen voor meer informatie over het maken van verbinding met elke gegevensbron.
Kies de gegevens die u wilt importeren.
Laad de gegevens door op de Laden knop te klikken.

Resultaat

De geïmporteerde gegevens worden weergegeven in een nieuw blad.

Typische resultaten voor een query

Volgende stappen

Als u gegevens wilt vormgeven en transformeren met behulp van de Power Query-editor, selecteert u Gegevens transformeren. Zie Shapegegevens met Power Query Editorvoor meer informatie.

Opmerking: Deze functie is algemeen beschikbaar voor Microsoft 365 abonnees met versie 16.69 (23010700) of hoger van Excel voor Mac. Als u een Microsoft 365-abonnee bent, controleert u of u de nieuwste versie van Office hebt.

Procedure

Selecteer Gegevens > Gegevens ophalen (Power Query).
Als u de Query-editor wilt openen, selecteert u Start Power Query Editor.

Tip: U kunt de Query-editor ook openen door Gegevens ophalen (Power Query) te selecteren en vervolgens op Volgendete klikken.
Vorm en transformeer uw gegevens met behulp van de Query-editor zoals u zou doen in Excel voor Windows. Zie Power Query voor Excel Help voor meer informatie.
Wanneer u klaar bent, selecteert u Start > Sluiten & laden.

Resultaat

De zojuist geïmporteerde gegevens worden weergegeven in een nieuw blad.

Typische resultaten voor een query

U kunt de volgende gegevensbronnen vernieuwen: SharePoint-bestanden, SharePoint-lijsten, SharePoint-mappen, OData, tekst-/CSV-bestanden, Excel-werkmappen (.xlsx), XML- en JSON-bestanden, lokale tabellen en bereiken en een Microsoft SQL Server database.

De eerste keer vernieuwen

De eerste keer dat u gegevensbronnen op basis van bestanden in uw werkmapquery's probeert te vernieuwen, moet u mogelijk het bestandspad bijwerken.

Selecteer Gegevens, de pijl naast Gegevens ophalen, en vervolgens Gegevensbroninstellingen. Het dialoogvenster Gegevensbroninstellingen wordt weergegeven.
Selecteer een verbinding en selecteer vervolgens Bestandspad wijzigen.
Selecteer in het dialoogvenster Bestandspad dialoogvenster, selecteer een nieuwe locatie, en selecteer vervolgens Gegevens ophalen.
Selecteer Sluiten.

Volgende keren vernieuwen

Vernieuwen:

Alle gegevensbronnen in de werkmap, selecteer Gegevens > Alle vernieuwen.
Een specifieke gegevensbron, klik met de rechtermuisknop op een querytabel op een blad en selecteer vervolgens Vernieuwen.
Een draaitabel, selecteer een cel in de draaitabel en selecteer vervolgens Draaitabel analyseren > Gegevens vernieuwen.

De eerste keer dat u toegang krijgt tot SharePoint, SQL Server, OData of andere gegevensbronnen waarvoor toestemming is vereist, moet u de juiste referenties opgeven. U kunt ook de referenties wissen om nieuwe referenties in te voeren.

Referenties invoeren

Wanneer u een query voor de eerste keer vernieuwt, wordt u mogelijk gevraagd om u aan te melden. Selecteer de verificatiemethode en geef de aanmeldingsreferenties op om verbinding te maken met de gegevensbron en ga door met het vernieuwen.

Als aanmelding is vereist, deReferenties invoeren dialoogvenster wordt weergegeven.

Bijvoorbeeld:

SharePoint-referenties:
SQL Server referenties:

Referenties wissen

Selecteer Gegevens > Gegevens ophalen > egevensbroninstellingen.
In het dialoogvenster Gegevensbroninstelling selecteert u de verbinding die u wilt.
Selecteer onderaan Toestemming Wissen.
Bevestig dat u dit wilt doen en selecteer vervolgens Verwijderen.

Hoewel creatie in de Power Query Editor niet beschikbaar is in Excel voor Mac, biedt VBA wel ondersteuning voor Power Query ontwerpen. Het overdragen van een VBA-codemodule in een bestand van Excel voor Windows naar Excel voor Mac is een proces in twee stappen. Aan het einde van deze sectie vindt u een voorbeeldprogramma.

Stap één: Excel voor Windows

In Excel Windows kunt u query's ontwikkelen met behulp van VBA. VBA-code die gebruikmaakt van de volgende entiteiten in het objectmodel van Excel, werkt ook in Excel voor Mac: Query's-object, WorkbookQuery-object, Eigenschap Workbook.Queries.Zie Excel VBA-verwijzing voor meer informatie.
Controleer in Excel of de Visual Basic Editor is geopend door op ALT+F11 te drukken.
Klik met de rechtermuisknop op de module en selecteer vervolgens Bestand exporteren. Het dialoogvenster Exporteren wordt weergegeven.
Voer een bestandsnaam in, controleer of de bestandsextensie .bas is en selecteer vervolgens Opslaan.
Upload het VBA-bestand naar een onlineservice om het bestand toegankelijk te maken vanaf de Mac. U kunt Microsoft OneDrive gebruiken. Zie Bestanden synchroniseren met OneDrive op Mac OS X voor meer informatie.

Stap twee: Excel voor Mac

Download het VBA-bestand naar een lokaal bestand, het VBA-bestand dat u hebt opgeslagen in Stap één: Excel voor Windows en geüpload naar een onlineservice.
Selecteer in Excel voor Mac Hulpprogramma's> Macro > Virtual Basic Editor. Het Visual Basic Editor venster wordt weergegeven.
Klik met de rechtermuisknop op een object in het projectvenster en selecteer vervolgens Bestand importeren. Het dialoogvenster Bestand importeren wordt weergegeven.
Zoek het VBA-bestand en selecteer vervolgens Openen.

Voorbeeldcode

Hier volgt basiscode die u kunt aanpassen en gebruiken. Dit is een voorbeeldquery waarmee een lijst met waarden van 1 tot 100 wordt gemaakt.

Sub CreateSampleList()
    ActiveWorkbook.Queries.Add Name:="SampleList", Formula:= _
        "let" & vbCr & vbLf & _
            "Source = {1..100}," & vbCr & vbLf & _
            "ConvertedToTable = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error)," & vbCr & vbLf & _
            "RenamedColumns = Table.RenameColumns(ConvertedToTable,{{""Column1"", ""ListValues""}})" & vbCr & vbLf & _
        "in" & vbCr & vbLf & _
            "RenamedColumns"
    ActiveWorkbook.Worksheets.Add
    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
        "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=SampleList;Extended Properties=""""" _
        , Destination:=Range("$A$1")).QueryTable
        .CommandType = xlCmdSql
        .CommandText = Array("SELECT * FROM [SampleList]")
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .ListObject.DisplayName = "SampleList"
        .Refresh BackgroundQuery:=False
    End With
End Sub

Open de Excel-werkmap.
Als u een beveiligingswaarschuwing krijgt over het uitschakelen van externe gegevensverbindingen, selecteert u Inhoud inschakelen.
Als het dialoogvenster Bestandstoegang verlenen wordt weergegeven, selecteert u Selecteeren selecteert u vervolgens Toegang verlenen aan de map op het hoogste niveau met de gegevensbronbestanden.
Selecteer Gegevens> Uit tekst (verouderd). Het dialoogvenster Zoekfunctiewordt weergegeven.
Zoek het TXT- of CSV-bestand en selecteer Openen. De Tekst importeren wizard wordt weergegeven.Tip Controleer herhaaldelijk het Preview van geselecteerde gegevens deelvenster om uw keuzes te bevestigen.
Ga op de eerste pagina als volgt te werk:

bestandstype Als u het type tekstbestand wilt kiezen, selecteert u Gescheiden of vaste breedte.Rijnummer In Beginnen met importeren bij rij, selecteert u een rijnummer om de eerste rij met gegevens op te geven die u wilt importeren.Tekenset Selecteer in Bestandsoorsprong de tekenset die in het tekstbestand wordt gebruikt. In de meeste gevallen kunt u deze instelling op de standaardinstelling laten staan.
Op de tweede pagina, doet u het volgende:Gescheiden Als u op de eerste pagina Gescheidenhebt gekozen, selecteert u onder Scheidingstekenshet scheidingsteken of gebruikt u het selectievakje Overige om een teken in te voeren dat niet wordt vermeld. Selecteer Opeenvolgende scheidingstekens behandelen als één als uw gegevens een scheidingsteken van meer dan één teken tussen gegevensvelden bevatten of als uw gegevens meerdere aangepaste scheidingstekens bevatten. Selecteer in Tekstkwalificatie het teken dat waarden in het tekstbestand omsluit. Dit is meestal het aanhalingsteken (").

Vaste breedte Als u Vaste breedte op de eerste pagina hebt gekozen, volgt u de instructies voor het maken, verwijderen of verplaatsen van een onderbrekingslijn in het Voorbeeld van de geselecteerde gegevens vak.
Op de derde pagina, doet u het volgende: Voor elke kolom onder Voorbeeld van geselecteerde gegevens, selecteert u deze en wijzigt u de kolom desgewenst in een andere kolomindeling. U kunt de datumnotatie verder instellen en Geavanceerde selecteren om de instellingen voor numerieke gegevens te wijzigen. U kunt de gegevens ook converteren nadat u deze hebt geïmporteerd. Selecteer Voltooien. Het dialoogvenster Gegevens importeren wordt weergegeven.
Kies waar u de gegevens wilt toevoegen: op het bestaande blad, op een nieuw blad of in een draaitabel.
Selecteer OK.

Om ervoor te zorgen dat de verbinding werkt, voert u enkele gegevens in en selecteert u vervolgens Verbindingen > Vernieuwen.

Selecteer Gegevens > VanSQL Server ODBC. Het dialoogvenster Verbinding maken ODBC-gegevensbron SQL Server wordt weergegeven.
Voer de server in het vak Servernaam in en voer desgewenst de database in het vak Databasenaam in. Haal deze informatie op bij de databasebeheerder.
Selecteer onder Verificatieeen methode uit de lijst: Gebruikersnaam/Wachtwoord, Kerberos, of NTLM.
Voer referenties in de vakken Gebruikersnaam en Wachtwoord in.
Selecteer Verbinding maken. Het dialoogvenster Navigator wordt weergegeven.
Navigeer in het linkerdeelvenster naar de gewenste tabel en selecteer deze.
Bevestig de SQL-instructie in het rechterdeelvenster. U kunt de SQL-instructie naar wens wijzigen.
Als u een voorbeeld van de gegevens wilt bekijken, selecteert u Uitvoeren.
Wanneer u klaar bent, selecteert u Gegevens teruggeven. Het dialoogvenster Gegevens importeren wordt weergegeven.
Kies waar u de gegevens wilt toevoegen: op het bestaande blad, op een nieuw blad of in een draaitabel.
Als u verbindingseigenschappen wilt instellen op de tabbladen Gebruik en Definitie van het dialoogvenster Eigenschappen, selecteert u Eigenschappen. Nadat u de gegevens hebt geïmporteerd, kunt u ook Gegevens> Verbindingenselecteren en vervolgens in het dialoogvenster VerbindingseigenschappenEigenschappenselecteren.
Selecteer OK.
Om ervoor te zorgen dat de verbinding werkt, voert u enkele gegevens in en selecteert u vervolgens Gegevens > Alles vernieuwen.

Als u een externe bron wilt gebruiken die niet een SQL Database (bijvoorbeeld FileMaker Pro), kunt u een ODBC-stuurprogramma (Open Database Connectivity) gebruiken dat op uw Mac is geïnstalleerd. Op deze webpagina vindt u informatie over stuurprogramma's. Zodra het stuurprogramma voor uw gegevensbron is geïnstalleerd, volgt u deze stappen:

Selecteer Gegevens > Uit database (Microsoft Query).
Voeg de gegevensbron voor uw database toe en selecteer vervolgensOK.
Voer bij de SQL Server referentiesprompt de verificatiemethode, de gebruikersnaam en het wachtwoord in.
Selecteer aan de linkerkant de pijl naast de server om de databases weer te geven.
Selecteer de pijl naast de gewenste database.
Selecteer de gewenste tabel.
Als u een voorbeeld van de gegevens wilt bekijken, selecteert u Uitvoeren.
Wanneer u klaar bent, selecteert u Gegevens teruggeven.
Kies in het dialoogvenster Gegevens importeren waar u de gegevens wilt bevinden: op het bestaande blad, op een nieuw blad of in een draaitabel.
Selecteer OK.
Om ervoor te zorgen dat de verbinding werkt, voert u enkele gegevens in en selecteert u vervolgens Gegevens > Alles vernieuwen.

Als uw machtigingen niet werken, kunt u deze mogelijk niet eerst wissen en u vervolgens aanmelden.

Selecteer Gegevens > Verbindingen. Het dialoogvenster Werkmapverbindingen wordt weergegeven.
Selecteer de gewenste verbinding in de lijst en selecteer vervolgens Machtigingen wissen.