Excel

Sammanfattning

Förutsättningar

För att ansluta till en äldre arbetsbok (till exempel .xls eller .xlsb) krävs OLEDB-providern (eller ACE)-providern för Access Database Engine. Om du vill installera den här providern går du till nedladdningssidan och installerar den relevanta versionen (32 bitar eller 64 bitar). Om du inte har installerat den visas följande fel när du ansluter till äldre arbetsböcker:

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 inte installeras i molntjänstmiljöer. Om du ser det här felet på en molnvärd (till exempel Power Query Online) måste du använda en gateway som har ACE installerat för att ansluta till de äldre Excel-filerna.

Funktioner som stöds

• Importera

Anslut till en Excel-arbetsbok från Power Query Desktop

Så här upprättar du anslutningen från Power Query Desktop:

1. Välj Excel-arbetsbok i hämta dataupplevelsen. Hämta dataupplevelsen i Power Query Desktop varierar mellan appar. Mer information om Power Query Desktop för att hämta data för din app finns i Var du hämtar data.

2. Bläddra efter och välj den Excel-arbetsbok som du vill läsa in. Välj sedan Öppna.

   

   Om Excel-arbetsboken är online använder du webbanslutningsappen för att ansluta till arbetsboken.

3. I Navigatör väljer du den arbetsboksinformation du vill ha och väljer sedan Läs in för att läsa in data eller Transformera data för att fortsätta transformera data i Power Query-redigeraren.

   

Anslut till en Excel-arbetsbok från Power Query Online

Så här gör du anslutningen från Power Query Online:

1. Välj alternativet Excel-arbetsbok i hämta dataupplevelsen. Olika appar har olika sätt att komma till Power Query Online för att hämta data. Mer information om hur du kommer till Power Query Online för att hämta data från din app finns i Var du kan hämta data.

   

2. I dialogrutan Excel som visas anger du sökvägen till Excel-arbetsboken.

   

3. Om det behövs väljer du en lokal datagateway för att komma åt Excel-arbetsboken.

4. Om det är första gången du använder excel-arbetsboken väljer du autentiseringstyp och loggar in på ditt konto (om det behövs).

5. I Navigatör väljer du den arbetsboksinformation du vill ha och sedan Transformera data för att fortsätta transformera data i Power Query-redigeraren.

   

Föreslagna tabeller

Om du ansluter till en Excel-arbetsbok som inte specifikt innehåller en enda tabell försöker Power Query-navigatören skapa en föreslagen lista med tabeller som du kan välja mellan. Tänk dig till exempel följande arbetsboksexempel som innehåller data från A1 till C5, mer data från D8 till E10 och mer från C13 till F16.

När du ansluter till data i Power Query skapar Power Query-navigatören två listor. Den första listan innehåller hela arbetsboksbladet och den andra listan innehåller tre föreslagna tabeller.

Om du markerar hela bladet i navigatören visas arbetsboken som den visades i Excel, med alla tomma celler fyllda med null.

Om du väljer en av de föreslagna tabellerna visas varje enskild tabell som Power Query kunde fastställa från arbetsbokens layout i navigatören. Om du till exempel väljer Tabell 3 visas de data som ursprungligen visades i cellerna C13 till F16.

Felsökning

Numerisk precision (eller "Varför ändrades mina siffror?")

När du importerar Excel-data kanske du märker att vissa talvärden verkar ändras något när de importeras till Power Query. Om du till exempel markerar en cell som innehåller 0,049 i Excel visas det här talet i formelfältet som 0,049. Men om du importerar samma cell till Power Query och väljer den visas förhandsgranskningsinformationen som 0,04900000000000000002 (även om den i förhandsgranskningstabellen är formaterad som 0,049). Vad är det som händer här?

Svaret är lite komplicerat och har att göra med hur Excel lagrar tal med något som kallas binär flyttals notation. Slutsatsen är att det finns vissa tal som Excel inte kan representera med 100 % precision. Om du öppnar filen .xlsx och tittar på det faktiska värdet som lagras ser du att 0.049 faktiskt lagras som 0,049000000000000000000000000000000002 i den .xlsx .xlsx filen och tittar på det faktiska värdet som lagras. Det här är värdet som Power Query läser från .xlsx och därmed värdet som visas när du markerar cellen i Power Query. (Mer information om numerisk precision i Power Query finns i avsnitten "Decimaltal" och "Fast decimaltal" iDatatyper i Power Query.)

Anslut till en Excel-arbetsbok online

Om du vill ansluta till ett Excel-dokument som finns i Sharepoint kan du göra det via webbanslutningsappen i Power BI Desktop, Excel och Dataflöden, och även med Excel-anslutningsappen i Dataflöden. Så här hämtar du länken till filen:

1. Öppna dokumentet i Excel Desktop.
2. Öppna menyn Arkiv, välj fliken Info och välj sedan Kopiera sökväg.
3. Kopiera adressen till fältet Filsökväg eller URL och ta bort ?web=1 från slutet av adressen.

Äldre ACE-anslutningsprogram

Power Query läser äldre arbetsböcker (till exempel .xls eller .xlsb) med hjälp av OLEDB-providern Access Database Engine (eller ACE). På grund av detta kan du stöta på oväntade beteenden när du importerar äldre arbetsböcker som inte inträffar när du importerar OpenXML-arbetsböcker (till exempel .xlsx). Här är några vanliga exempel.

Oväntad värdeformatering

På grund av ACE kan värden från en äldre Excel-arbetsbok importeras med mindre precision eller återgivning än förväntat. Anta till exempel att Excel-filen innehåller numret 1024.231, som du har formaterat för visning som "1 024.23". När det importeras till Power Query representeras det här värdet som textvärdet "1 024,23" i stället för som det underliggande fullständiga återgivningsnumret (1024.231). Det beror på att ACE i det här fallet inte visar det underliggande värdet för Power Query, utan bara värdet som det visas i Excel.

Oväntade null-värden

När ACE läser in ett blad tittar det på de första åtta raderna för att fastställa kolumnernas datatyper. Om de första åtta raderna inte är representativa för de senare raderna kan ACE tillämpa en felaktig typ på kolumnen och returnera nullvärden för alla värden som inte matchar typen. Om en kolumn till exempel innehåller tal i de första åtta raderna (till exempel 1000, 1001 och så vidare) men har icke-numeriska data i senare rader (till exempel "100Y" och "100Z"), drar ACE slutsatsen att kolumnen innehåller tal och att icke-numeriska värden returneras som null.

Inkonsekvent värdeformatering

I vissa fall returnerar ACE helt olika resultat mellan uppdateringar. Med hjälp av exemplet som beskrivs i formateringsavsnittet kan du plötsligt se värdet 1024.231 i stället för "1 024,23". Den här skillnaden kan bero på att den äldre arbetsboken är öppen i Excel när den importeras till Power Query. Stäng arbetsboken för att lösa problemet.

Saknade eller ofullständiga Excel-data

Ibland misslyckas Power Query med att extrahera alla data från ett Excel-kalkylblad. Det här felet orsakas ofta av att kalkylbladet har felaktiga dimensioner (till exempel att ha dimensioner för A1:C200 när faktiska data upptar mer än tre kolumner eller 200 rader).

Så här diagnostiserar du felaktiga dimensioner

Så här visar du måtten för ett kalkylblad:

1. Byt namn på xlsx-filen med ett .zip-tillägg.
2. Öppna filen i Utforskaren.
3. Navigera till xl\kalkylblad.
4. Kopiera xml-filen för det problematiska bladet (till exempel Sheet1.xml) från zip-filen till en annan plats.
5. Granska de första raderna i filen. Om filen är tillräckligt liten öppnar du den i en textredigerare. Om filen är för stor för att öppnas i en textredigerare kör du följande kommando från en kommandotolk: mer Sheet1.xml.
6. Leta efter en <dimension .../> tagg (till exempel <dimension ref="A1:C200" />).

Om filen har ett dimensionsattribut som pekar på en enskild cell (till exempel <dimension ref="A1" />) använder Power Query det här attributet för att hitta startraden och kolumnen för data på bladet.

Men om filen har ett dimensionsattribut som pekar på flera celler (till exempel <dimension ref="A1:AJ45000"/>), använder Power Query det här intervallet för att hitta startraden och kolumnen samt den avslutande raden och kolumnen. Om det här intervallet inte innehåller alla data på bladet läses vissa data inte in.

Så här åtgärdar du felaktiga dimensioner

Du kan åtgärda problem som orsakas av felaktiga dimensioner genom att utföra någon av följande åtgärder:

• Öppna och spara dokumentet igen i Excel. Den här åtgärden skriver över de felaktiga dimensioner som lagras i filen med rätt värde.

• Kontrollera att verktyget som genererade Excel-filen är fast för att mata ut dimensionerna korrekt.

• Uppdatera M-frågan för att ignorera de felaktiga dimensionerna. Från och med december 2020-versionen av Power Query stöder Excel.Workbook nu ett InferSheetDimensions alternativ. När det är sant gör det här alternativet att funktionen ignorerar de dimensioner som lagras i arbetsboken och i stället fastställer dem genom att granska data.

  Här är ett exempel på hur du anger det här alternativet:

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

Långsamma eller långsamma prestanda vid inläsning av Excel-data

Långsam inläsning av Excel-data kan också orsakas av felaktiga dimensioner. Men i det här fallet orsakas långsamheten av att dimensionerna är mycket större än de behöver vara, snarare än att de är för små. Alltför stora dimensioner gör att Power Query läser en mycket större mängd data från arbetsboken än vad som faktiskt behövs.

Om du vill åtgärda det här problemet kan du läsa mer i Hitta och återställa den sista cellen i ett kalkylblad .

Dåliga prestanda vid inläsning av data från SharePoint

När du hämtar data från Excel på datorn eller från SharePoint bör du överväga både volymen av de berörda data och arbetsbokens komplexitet.

Du ser prestandaförsämring när du hämtar mycket stora filer från SharePoint. Detta är dock bara en del av problemet. Om du har betydande affärslogik i en Excel-fil som hämtas från SharePoint kan den här affärslogiken behöva köras när du uppdaterar dina data, vilket kan orsaka komplicerade beräkningar. Överväg att aggregera och förberäkna data, eller flytta mer av affärslogik från Excel-lagret och till Power Query-lagret.

Fel när du använder Excel-anslutningsappen för att importera CSV-filer

Även om CSV-filer kan öppnas i Excel är de inte Excel-filer. Använd anslutningsappen Text/CSV i stället.

Fel vid import av arbetsböcker med strikt öppna XML-kalkylblad

Du kan se följande fel när du importerar arbetsböcker som sparats i Excels format "Strikt öppna XML-kalkylblad":

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

Det här felet inträffar när ACE-drivrutinen inte är installerad på värddatorn. Arbetsböcker som sparats i formatet "Strikt öppet XML-kalkylblad" kan bara läsas av ACE. Men eftersom sådana arbetsböcker använder samma filnamnstillägg som vanliga Öppna XML-arbetsböcker (.xlsx) kan vi inte använda tillägget för att visa det vanliga the Access Database Engine OLEDB provider may be required to read this type of file felmeddelandet.

Lös felet genom att installera ACE-drivrutinen. Om felet uppstår i en molntjänst måste du använda en gateway som körs på en dator som har ACE-drivrutinen installerad.

"Filen innehåller skadade data"-fel

Du kan se följande fel när du importerar vissa Excel-arbetsböcker.

DataFormat.Error: File contains corrupted data.

Vanligtvis indikerar det här felet att det finns ett problem med filens format.

Men ibland kan det här felet inträffa när en fil verkar vara en Öppen XML-fil (till exempel .xlsx), men ACE-drivrutinen behövs faktiskt för att bearbeta filen. Gå till avsnittet Äldre ACE-anslutningsprogram för mer information om hur du bearbetar filer som kräver ACE-drivrutinen.

Kända problem och begränsningar

• Power Query Online kan inte komma åt krypterade Excel-filer. Eftersom Excel-filer som är märkta med andra känslighetstyper än "Offentliga" eller "icke-företag" krypteras är de inte tillgängliga via Power Query Online.
• Power Query Online stöder inte lösenordsskyddade Excel-filer.