Excel

Samenvatting

Item Beschrijving
Release-status Algemene beschikbaarheid
Producten Power BI (gegevenssets)
Power BI (gegevensstromen)
Power Apps (gegevensstromen)
Excel
Dynamics 365 Customer Insights
Analysis Services
Ondersteunde verificatietypen Anoniem (online)
Basic (online)
Organisatieaccount (online)
Naslagdocumentatie voor functies Excel.Workbook
Excel.CurrentWorkbook

Notitie

Sommige mogelijkheden zijn mogelijk aanwezig in het ene 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 ACCESS Database Engine OLEDB-provider (of ACE) vereist. Als u deze provider wilt installeren, gaat u naar de downloadpagina en installeert u de relevante versie (32-bits of 64-bits). 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 waarin ACE is geïnstalleerd om verbinding te maken met de verouderde Excel bestanden.

Ondersteunde mogelijkheden

  • Importeren

Verbinding maken een Excel van Power Query Desktop

De verbinding maken vanaf Power Query Desktop:

  1. Selecteer de Excel optie in de connectorselectie.

  2. Blader naar en selecteer Excel werkmap die u wilt laden. Selecteer vervolgens Openen.

    Selecteer de Excel werkmap in Verkenner.

    Als de Excel werkmap online is, gebruikt u de webconnector om verbinding te maken met de werkmap.

  3. Selecteer in Navigator de werkmapgegevens die u wilt, 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.

    Excel werkmap geïmporteerd in Power Query Desktop Navigator.

Verbinding maken een Excel van Power Query Online

De verbinding maken vanuit Power Query Online:

  1. Selecteer de Excel optie in de connectorselectie.

  2. Geef in Excel dialoogvenster dat wordt weergegeven het pad naar de Excel werkmap op.

    Verbindingsgegevens voor toegang tot Excel werkmap.

  3. Selecteer indien nodig een on-premises gegevensgateway voor toegang tot de Excel werkmap.

  4. Als dit de eerste keer is dat u deze Excel-werkmap hebt gebruikt, selecteert u het type verificatie en meld u zich aan bij uw account (indien nodig).

  5. Selecteer in Navigator de werkmapgegevens die u wilt en vervolgens Gegevens transformeren om door te gaan met het transformeren van de gegevens in Power Query Editor.

    Excel werkmap geïmporteerd in Power Query Online Navigator.

Problemen oplossen

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

Wanneer u Excel importeert, ziet u mogelijk dat bepaalde getalwaarden enigszins lijken te veranderen wanneer ze in de Power Query. Als u bijvoorbeeld een cel met 0,049 in Excel selecteert, wordt dit getal in de formulebalk weergegeven als 0,049. Maar als u dezelfde cel in Power Query importeert en selecteert, wordt deze in de preview-details weergegeven als 0,04900000000002 (hoewel deze in de preview-tabel is opgemaakt als 0.049). Wat gebeurt er hier?

Het antwoord is een beetje gecompliceerd en heeft te maken met hoe Excel slaat met behulp van iets dat binaire drijvende-punt notatie wordt genoemd. De onderste regel is dat er bepaalde getallen zijn die Excel niet met 100% precisie kunnen vertegenwoordigen. Als u het .xlsx-bestand opent en de werkelijke waarde bekijkt die wordt opgeslagen, ziet u dat in het .xlsx-bestand 0,049 daadwerkelijk wordt opgeslagen als 0,049000000000000000000002. Dit is de waarde Power Query uit de .xlsx, en dus de waarde die wordt weergegeven wanneer u de cel in de Power Query. (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 met 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. Ga als volgende te werk om de koppeling naar het bestand op te halen:

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

Verouderde ACE-connector

Power Query verouderde werkmappen (zoals .xls of .xlsb) leest, gebruikt u de ACCESS Database Engine (of ACE) OLEDB-provider. Daarom kan er onverwacht gedrag optreden bij het importeren van verouderde werkmappen die niet optreden bij het importeren van OpenXML-werkmappen (zoals .xlsx). Hier zijn enkele veelvoorkomende voorbeelden.

Onverwachte waardeopmaak

Vanwege ACE kunnen waarden uit een verouderde Excel met minder precisie of betrouwbaarheid worden geïmporteerd dan verwacht. Stel dat uw Excel het getal 1024.231 bevat, dat u hebt opgemaakt voor weergave als '1.024.23'. Wanneer deze waarde Power Query in Power Query, wordt deze 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 laat zien, maar alleen de waarde zoals deze wordt weergegeven in Excel.

Onverwachte null-waarden

Wanneer ACE een werkblad laadt, worden de eerste acht rijen gebruikt 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 met het type overeenkomen. Als een kolom bijvoorbeeld getallen bevat in de eerste acht rijen (zoals 1000, 1001, en meer), maar niet-numerieke gegevens bevat in latere rijen (zoals '100Y' en '100Z'), concludeert ACE dat de kolom getallen bevat en dat niet-numerieke waarden worden geretourneerd als null.

Inconsistente waardeopmaak

In sommige gevallen retourneert ACE totaal verschillende resultaten bij vernieuwingen. In het voorbeeld dat wordt beschreven in de opmaaksectie zietu mogelijk plotseling de waarde 1024,231 in plaats van '1.024,23'. Dit verschil kan worden veroorzaakt doordat de verouderde werkmap in een Excel is geopend tijdens het importeren in Power Query. Sluit de werkmap om dit probleem op te lossen.

Ontbrekende of onvolledige Excel gegevens

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

Onjuiste dimensies diagnosticeren

De afmetingen van een werkblad weergeven:

  1. Wijzig de naam van het xlsx-bestand in een .zip extensie.
  2. Open het bestand in Verkenner.
  3. Navigeer naar xl\werkbladen.
  4. Kopieer het XML-bestand voor het problematische werkblad (bijvoorbeeld Sheet1.xml) uit het ZIP-bestand naar een andere locatie.
  5. 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, voer dan de volgende opdracht uit vanaf een opdrachtprompt: meer Sheet1.xml.
  6. Zoek naar <dimension .../> een tag (bijvoorbeeld <dimension ref="A1:C200" /> ).

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

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

Onjuiste dimensies oplossen

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

  • Open het document en open het opnieuw in Excel. Met deze actie worden de onjuiste dimensies die in het bestand zijn opgeslagen, overschreven met de juiste waarde.

  • Zorg ervoor dat het hulpprogramma waarmee het Excel is gegenereerd, is hersteld zodat de dimensies correct worden uitgevoerd.

  • Werk uw M-query bij om de onjuiste dimensies te negeren. Vanaf de release van december 2020 Power Query Excel.Workbook een optie InferSheetDimensions ondersteund. Indien 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 is een voorbeeld van hoe u deze optie op te geven:

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

Trage of trage prestaties bij het laden van Excel gegevens

Traag 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 nodig is, 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 de laatste cel op een werkblad te zoeken en opnieuw in te stellen voor gedetailleerde instructies.

Slechte prestaties bij het laden van gegevens uit SharePoint

Wanneer u gegevens opvraagt van Excel computer of van SharePoint, moet u rekening houden 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 aanzienlijke bedrijfslogica in een Excel-bestand hebt dat wordt opgehaald uit SharePoint, moet deze bedrijfslogica mogelijk worden uitgevoerd wanneer u uw gegevens vernieuwt, wat tot gecompliceerde berekeningen kan leiden. U kunt gegevens aggregeren en vooraf berekenen of meer van de bedrijfslogica uit de Excel en naar de Power Query verplaatsen.

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

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