Excel

Artikel
02/07/2024

Samenvatting

Item	Beschrijving
Releasestatus	Algemene beschikbaarheid
Producten	Excel Power BI (Semantische modellen) Power BI (gegevensstromen) Fabric (Dataflow Gen2) Power Apps (gegevensstromen) Dynamics 365 Customer Insights Analysis Services
Ondersteunde verificatietypen	Anoniem (online) Basic (online) Organisatieaccount (online)
Documentatie voor functiereferenties	Excel.Workbook Excel.CurrentWorkbook

Notitie

Sommige mogelijkheden zijn mogelijk aanwezig in één product, maar niet in andere vanwege implementatieschema's en hostspecifieke mogelijkheden.

Vereisten

Als u verbinding wilt maken met een verouderde werkmap (zoals .xls of .xlsb), is de OLEDB-provider (Access Database Engine of ACE) vereist. Als u deze provider wilt installeren, gaat u naar de downloadpagina en installeert u de relevante (32-bits of 64-bits) versie. Als u deze niet hebt geïnstalleerd, ziet u de volgende fout bij het maken van verbinding met verouderde werkmappen:

The 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine. The 32-bit (or 64-bit) version of the Access Database Engine OLEDB provider may be required to read this type of file. To download the client software, visit the following site: https://go.microsoft.com/fwlink/?LinkID=285987.

ACE kan niet worden geïnstalleerd in cloudserviceomgevingen. Dus als u deze fout ziet in een cloudhost (zoals Power Query Online), moet u een gateway gebruiken waarop ACE is geïnstalleerd om verbinding te maken met de verouderde Excel-bestanden.

Ondersteunde mogelijkheden

Importeren

Verbinding maken naar een Excel-werkmap vanuit Power Query Desktop

De verbinding maken vanuit Power Query Desktop:

Selecteer Excel-werkmap in de ervaring Gegevens ophalen. De ervaring voor het ophalen van gegevens in Power Query Desktop varieert tussen apps. Ga voor meer informatie over de Power Query Desktop-ervaring voor uw app naar Waar u gegevens kunt ophalen.
Blader naar en selecteer de Excel-werkmap die u wilt laden. Selecteer vervolgens Openen.

Als de Excel-werkmap online is, gebruikt u de webconnector om verbinding te maken met de werkmap.
Selecteer in Navigator de gewenste werkmapgegevens en selecteer vervolgens Laden om de gegevens te laden of Gegevens transformeren om door te gaan met het transformeren van de gegevens in Power Query-editor.

Verbinding maken naar een Excel-werkmap vanuit Power Query Online

De verbinding maken vanuit Power Query Online:

Selecteer de excel-werkmapoptie in de ervaring Gegevens ophalen. Verschillende apps hebben verschillende manieren om toegang te krijgen tot Power Query Online om gegevens te krijgen. Ga naar Waar u gegevens kunt ophalen voor de Power Query Online-app voor meer informatie over het ophalen van gegevens.
Geef in het Excel-dialoogvenster dat wordt weergegeven het pad naar de Excel-werkmap op.
Selecteer indien nodig een on-premises gegevensgateway voor toegang tot de Excel-werkmap.
Als dit de eerste keer is dat u deze Excel-werkmap hebt geopend, selecteert u het verificatietype en meldt u zich aan bij uw account (indien nodig).
Selecteer in Navigator de gewenste werkmapgegevens en transformeer gegevens om de gegevens in Power Query-editor te blijven transformeren.

Voorgestelde tabellen

Als u verbinding maakt met een Excel-werkmap die niet specifiek één tabel bevat, probeert de Power Query-navigator een voorgestelde lijst met tabellen te maken waaruit u kunt kiezen. Bekijk bijvoorbeeld het volgende werkmapvoorbeeld met gegevens van A1 naar C5, meer gegevens van D8 naar E10 en meer van C13 naar F16.

Schermopname van een Excel-werkmap met drie gegevenssets.

Wanneer u verbinding maakt met de gegevens in Power Query, maakt de Power Query-navigator twee lijsten. De eerste lijst bevat het hele werkmapblad en de tweede lijst bevat drie voorgestelde tabellen.

Als u het hele blad in de navigator selecteert, wordt de werkmap weergegeven zoals deze wordt weergegeven in Excel, met alle lege cellen gevuld met null.

Als u een van de voorgestelde tabellen selecteert, wordt elke afzonderlijke tabel die Power Query kon bepalen op basis van de indeling van de werkmap weergegeven in de navigator. Als u bijvoorbeeld Tabel 3 selecteert, worden de gegevens weergegeven die oorspronkelijk in de cellen C13 tot F16 zijn weergegeven.

Notitie

Als het blad voldoende wordt gewijzigd, wordt de tabel mogelijk niet goed vernieuwd. Mogelijk kunt u de vernieuwing herstellen door de gegevens opnieuw te importeren en een nieuwe voorgestelde tabel te selecteren.

Probleemoplossing

Numerieke precisie (of 'Waarom zijn mijn getallen gewijzigd?')

Bij het importeren van Excel-gegevens ziet u mogelijk dat bepaalde getalwaarden enigszins veranderen wanneer ze in Power Query worden geïmporteerd. Als u bijvoorbeeld een cel met 0,049 selecteert in Excel, wordt dit getal weergegeven in de formulebalk als 0,049. Maar als u dezelfde cel in Power Query importeert en deze selecteert, worden de voorbeelddetails weergegeven als 0,0490000000000002 (ook al is deze in de voorbeeldtabel opgemaakt als 0,049). Wat is er aan de hand?

Het antwoord is een beetje ingewikkeld en moet te maken hebben met de wijze waarop Excel getallen opslaat met behulp van een binaire drijvendekommage-notatie. De onderste regel is dat er bepaalde getallen zijn die niet met 100% precisie kunnen worden aangegeven. Als u het .xlsx bestand openraakt en de werkelijke waarde bekijkt die wordt opgeslagen, ziet u dat in het .xlsx bestand 0.049 daadwerkelijk wordt opgeslagen als 0,049000000000000002. Dit is de waarde die Power Query leest uit de .xlsx en dus de waarde die wordt weergegeven wanneer u de cel in Power Query selecteert. (Voor meer informatie over numerieke precisie in Power Query gaat u naar de secties 'Decimaal getal' en 'Vast decimaal getal' van Gegevenstypen in Power Query.)

Verbinding maken naar een online Excel-werkmap

Als u verbinding wilt maken met een Excel-document dat wordt gehost in Sharepoint, kunt u dit doen via de webconnector in Power BI Desktop, Excel en gegevensstromen, en ook met de Excel-connector in gegevensstromen. De koppeling naar het bestand ophalen:

Open het document in de bureaubladversie van Excel.
Open het menu Bestand , selecteer het tabblad Info en selecteer vervolgens Pad kopiëren.
Kopieer het adres naar het veld Bestandspad of URL en verwijder het ?web=1 van het einde van het adres.

Verouderde ACE-connector

Power Query leest verouderde werkmappen (zoals .xls of .xlsb) met behulp van de OLEDB-provider van Access Database Engine (of ACE). Hierdoor kunt u onverwacht gedrag tegenkomen bij het importeren van verouderde werkmappen die niet optreden bij het importeren van OpenXML-werkmappen (zoals .xlsx). Hier volgen enkele veelvoorkomende voorbeelden.

Onverwachte waardeopmaak

Vanwege ACE kunnen waarden uit een verouderde Excel-werkmap worden geïmporteerd met minder precisie of betrouwbaarheid dan verwacht. Stel dat uw Excel-bestand het getal 1024.231 bevat dat u hebt opgemaakt voor weergave als '1024.23'. Wanneer deze waarde wordt geïmporteerd in Power Query, wordt deze waarde weergegeven als de tekstwaarde 1.024.23 in plaats van als het onderliggende volledige betrouwbaarheidsnummer (1024,231). Dit komt doordat ACE in dit geval niet de onderliggende waarde naar Power Query weergeeft, maar alleen de waarde die wordt weergegeven in Excel.

Onverwachte null-waarden

Wanneer ACE een blad laadt, worden de eerste acht rijen bekeken om de gegevenstypen van de kolommen te bepalen. Als de eerste acht rijen niet representatief zijn voor de latere rijen, kan ACE een onjuist type toepassen op die kolom en null-waarden retourneren voor een waarde die niet overeenkomt met het type. Als een kolom bijvoorbeeld getallen bevat in de eerste acht rijen (zoals 1000, 1001, enzovoort), maar niet-numerieke gegevens bevat in latere rijen (zoals 100Y en 100Z), eindigt ACE dat de kolom getallen bevat en dat eventuele niet-numerieke waarden als null worden geretourneerd.

Inconsistente opmaak van waarden

In sommige gevallen retourneert ACE totaal verschillende resultaten bij vernieuwingen. Met behulp van het voorbeeld dat wordt beschreven in de opmaaksectie, ziet u plotseling de waarde 1024.231 in plaats van '1.024.23'. Dit verschil kan worden veroorzaakt door het openen van de verouderde werkmap in Excel tijdens het importeren in Power Query. Sluit de werkmap om dit probleem op te lossen.

Ontbrekende of onvolledige Excel-gegevens

Soms kan Power Query niet alle gegevens uit een Excel-werkblad extraheren. Deze fout wordt vaak veroorzaakt doordat het werkblad onjuiste afmetingen heeft (bijvoorbeeld wanneer A1:C200 de werkelijke gegevens meer dan drie kolommen of 200 rijen in beslag nemen).

Onjuiste dimensies diagnosticeren

De afmetingen van een werkblad weergeven:

Wijzig de naam van het xlsx-bestand met een .zip-extensie.
Open het bestand in Bestandenverkenner.
Navigeer naar xl\werkbladen.
Kopieer het XML-bestand voor het problematische blad (bijvoorbeeld Sheet1.xml) uit het zip-bestand naar een andere locatie.
Inspecteer de eerste paar regels van het bestand. Als het bestand klein genoeg is, opent u het in een teksteditor. Als het bestand te groot is om te worden geopend in een teksteditor, voert u de volgende opdracht uit vanaf een opdrachtprompt: meer Sheet1.xml.
Zoek naar een <dimension .../> tag (bijvoorbeeld <dimension ref="A1:C200" />).

Als uw bestand een dimensiekenmerk heeft dat verwijst naar één cel (zoals <dimension ref="A1" />), gebruikt Power Query dit kenmerk om de beginrij en kolom van de gegevens op het blad te vinden.

Als uw bestand echter een dimensiekenmerk heeft dat verwijst naar meerdere cellen (zoals <dimension ref="A1:AJ45000"/>), gebruikt Power Query dit bereik om de beginrij en kolom te vinden, evenals de eindrij en kolom. Als dit bereik niet alle gegevens op het blad bevat, worden sommige gegevens niet geladen.

Onjuiste dimensies oplossen

U kunt problemen oplossen die worden veroorzaakt door onjuiste dimensies door een van de volgende acties uit te voeren:

Het document openen en opnieuw opslaan in Excel. Met deze actie worden de onjuiste dimensies overschreven die zijn opgeslagen in het bestand met de juiste waarde.
Zorg ervoor dat het hulpprogramma dat het Excel-bestand heeft gegenereerd, is vastgezet om de dimensies correct uit te voeren.
Werk uw M-query bij om de onjuiste dimensies te negeren. Vanaf de release van december 2020 van Power Query wordt Excel.Workbook nu een InferSheetDimensions optie ondersteund. Wanneer waar, zorgt deze optie ervoor dat de functie de dimensies negeert die zijn opgeslagen in de werkmap en deze in plaats daarvan bepaalt door de gegevens te inspecteren.

Hier volgt een voorbeeld van hoe u deze optie kunt opgeven:

Excel.Workbook(File.Contents("C:\MyExcelFile.xlsx"), [DelayTypes = true, InferSheetDimensions = true])

Het langzaam laden van Excel-gegevens kan ook worden veroorzaakt door onjuiste dimensies. In dit geval wordt de traagheid echter veroorzaakt doordat de dimensies veel groter zijn dan ze nodig hebben, in plaats van te klein te zijn. Te grote dimensies zorgen ervoor dat Power Query een veel grotere hoeveelheid gegevens uit de werkmap leest dan daadwerkelijk nodig is.

U kunt dit probleem oplossen door te verwijzen naar de laatste cel op een werkblad zoeken en opnieuw instellen voor gedetailleerde instructies.

Houd bij het ophalen van gegevens uit Excel op uw computer of vanuit SharePoint rekening met zowel het volume van de betrokken gegevens als de complexiteit van de werkmap.

U ziet prestatievermindering bij het ophalen van zeer grote bestanden uit SharePoint. Dit is echter slechts één deel van het probleem. Als u belangrijke bedrijfslogica hebt in een Excel-bestand dat wordt opgehaald uit SharePoint, moet deze bedrijfslogica mogelijk worden uitgevoerd wanneer u uw gegevens vernieuwt, wat ingewikkelde berekeningen kan veroorzaken. U kunt gegevens aggregeren en vooraf berekenen of meer bedrijfslogica uit de Excel-laag verplaatsen en naar de Power Query-laag gaan.

Fouten bij het gebruik van de Excel-connector voor het importeren van CSV-bestanden

Hoewel CSV-bestanden kunnen worden geopend in Excel, zijn ze geen Excel-bestanden. Gebruik in plaats daarvan de connector Text/CSV .

Fout bij het importeren van 'Strict Open XML Spreadsheet'-werkmappen

Mogelijk ziet u de volgende fout bij het importeren van werkmappen die zijn opgeslagen in de indeling Strict Open XML Spreadsheet van Excel:

DataFormat.Error: The specified package is invalid. The main part is missing.

Deze fout treedt op wanneer het ACE-stuurprogramma niet op de hostcomputer is geïnstalleerd. Werkmappen die zijn opgeslagen in de indeling Strict Open XML Spreadsheet, kunnen alleen worden gelezen door ACE. Omdat dergelijke werkmappen echter dezelfde bestandsextensie gebruiken als gewone Open XML-werkmappen (.xlsx), kunnen we de extensie niet gebruiken om het gebruikelijke the Access Database Engine OLEDB provider may be required to read this type of file foutbericht weer te geven.

Installeer het ACE-stuurprogramma om de fout op te lossen. Als de fout optreedt in een cloudservice, moet u een gateway gebruiken die wordt uitgevoerd op een computer waarop het ACE-stuurprogramma is geïnstalleerd.

Fouten met 'Bestand bevat beschadigde gegevens'

Mogelijk ziet u de volgende fout bij het importeren van bepaalde Excel-werkmappen.

DataFormat.Error: File contains corrupted data.

Deze fout geeft meestal aan dat er een probleem is met de indeling van het bestand.

Soms kan deze fout echter optreden wanneer een bestand een Open XML-bestand lijkt te zijn (zoals .xlsx), maar het ACE-stuurprogramma is eigenlijk nodig om het bestand te verwerken. Ga naar de sectie Legacy ACE-connector voor meer informatie over het verwerken van bestanden waarvoor het ACE-stuurprogramma is vereist.

Bekende problemen en beperkingen

Power Query Online heeft geen toegang tot versleutelde Excel-bestanden. Omdat Excel-bestanden met andere vertrouwelijkheidstypen dan Openbaar of Niet-Zakelijk zijn versleuteld, zijn ze niet toegankelijk via Power Query Online.
Power Query Online biedt geen ondersteuning voor Excel-bestanden die met een wachtwoord zijn beveiligd.