Innehåll
Om du redan har börjat använda verktygen för det kostnadsfria Power Query-tillägget i Microsoft Excel, kommer du mycket snart att stöta på ett mycket specialiserat, men mycket frekvent och irriterande problem i samband med att ständigt bryta länkar till källdata. Kärnan i problemet är att om du i din fråga hänvisar till externa filer eller mappar, så hårdkodar Power Query den absoluta sökvägen till dem i frågetexten. Allt fungerar bra på din dator, men om du bestämmer dig för att skicka en fil med en förfrågan till dina kollegor, kommer de att bli besvikna, eftersom. de har en annan väg till källdata på sin dator, och vår fråga kommer inte att fungera.
Vad ska man göra i en sådan situation? Låt oss titta på det här fallet mer i detalj med följande exempel.
Formulering av problemet
Anta att vi har i mappen E:Försäljningsrapporter ligger filen Topp 100 produkter.xls, som är en uppladdning från vår företagsdatabas eller ERP-system (1C, SAP, etc.) Denna fil innehåller information om de mest populära råvarorna och ser ut så här inuti:
Det är förmodligen klart direkt att det är nästan omöjligt att arbeta med det i Excel i den här formen: tomma rader genom en med data, sammanslagna celler, extra kolumner, en multi-level header, etc. kommer att störa.
Därför skapar vi en ny fil bredvid den här filen i samma mapp Handler.xlsx, där vi kommer att skapa en Power Query-fråga som laddar ful data från källuppladdningsfilen Topp 100 produkter.xls, och placera dem i ordning:
Göra en begäran till en extern fil
Öppnar filen Handler.xlsx, välj på fliken Data Kommando Hämta data – från fil – från Excel-arbetsbok (Data — Hämta data — Från fil — Från Excel), ange sedan platsen för källfilen och det ark vi behöver. Den valda datan kommer att laddas in i Power Query-redigeraren:
Låt oss återställa dem till det normala:
- Ta bort tomma rader med Hem — Ta bort rader — Ta bort tomma rader (Hem — Ta bort rader — Ta bort tomma rader).
- Ta bort onödiga topp 4 rader igenom Hem — Ta bort rader — Ta bort översta rader (Hem — Ta bort rader — Ta bort översta rader).
- Höj den första raden till tabellhuvudet med knappen Använd första raden som rubriker fliken Hem (Hem — Använd första raden som rubrik).
- Separera den femsiffriga artikeln från produktnamnet i den andra kolumnen med kommandot delad kolumn fliken Transformation (Transformera – dela kolumn).
- Ta bort onödiga kolumner och byt namn på rubrikerna på de återstående för bättre synlighet.
Som ett resultat bör vi få följande, mycket trevligare bild:
Det återstår att ladda upp denna förädlade tabell tillbaka till arket i vår fil Handler.xlsx laget stäng och ladda ner (Hem — Stäng och ladda) fliken Hem:
Hitta sökvägen till en fil i en begäran
Låt oss nu se hur vår fråga ser ut "under huven", på det interna språket inbyggt i Power Query med det kortfattade namnet "M". För att göra detta, gå tillbaka till vår fråga genom att dubbelklicka på den i den högra rutan Förfrågningar och anslutningar och på fliken översyn välja Avancerad redaktör (Visa — Avancerad redigerare):
I fönstret som öppnas visar den andra raden omedelbart en hårdkodad sökväg till vår ursprungliga uppladdningsfil. Om vi kan ersätta denna textsträng med en parameter, variabel eller en länk till en Excel-arkcell där denna sökväg är förskriven, så kan vi enkelt ändra den senare.
Lägg till en smart tabell med en filsökväg
Låt oss stänga Power Query för nu och återgå till vår fil Handler.xlsx. Låt oss lägga till ett nytt tomt ark och skapa en liten "smart" tabell på den, i den enda cell som den fullständiga sökvägen till vår källdatafil kommer att skrivas:
För att skapa ett smart bord från ett vanligt sortiment kan du använda kortkommandot ctrl+T eller knapp Formatera som en tabell fliken Hem (Hem — Formatera som tabell). Kolumnrubriken (cell A1) kan vara absolut vad som helst. Notera också att jag för tydlighetens skull har gett tabellen ett namn parametrar fliken Konstruktör (Design).
Att kopiera en sökväg från Utforskaren eller till och med ange den manuellt är naturligtvis inte särskilt svårt, men det är bäst att minimera den mänskliga faktorn och bestämma sökvägen, om möjligt, automatiskt. Detta kan implementeras med hjälp av standardfunktionen Excel-kalkylblad CELL (cell), som kan ge ut en massa användbar information om cellen som anges som ett argument – inklusive sökvägen till den aktuella filen:
Om vi antar att källdatafilen alltid ligger i samma mapp som vår processor, kan sökvägen vi behöver bildas av följande formel:
=LEFT(CELL(“filnamn”);FIND(“[“;CELL(“filnamn”))-1)&”Top 100 products.xls”
eller i engelsk version:
=VÄNSTER(CELL(«filnamn»);FIND(«[«;CELL(«filnamn»))-1)&»Топ-100 товаров.xls»
… var är funktionen LEVSIMV (VÄNSTER) tar ett stycke text från den fullständiga länken upp till den öppna hakparentesen (dvs sökvägen till den aktuella mappen), och sedan limmas namnet och förlängningen av vår källdatafil på den.
Parametrar sökvägen i frågan
Den sista och viktigaste beröringen återstår – att skriva sökvägen till källfilen i begäran Topp 100 produkter.xls, med hänvisning till cell A2 i vår skapade "smarta" tabell parametrar.
För att göra detta, låt oss gå tillbaka till Power Query-frågan och öppna den igen Avancerad redaktör fliken översyn (Visa — Avancerad redigerare). Istället för en textsträng-sökväg inom citattecken "E: Försäljningsrapporter Topp 100 produkter.xlsx" Låt oss presentera följande struktur:
Excel.CurrentWorkbook(){[Name="Inställningar"]}[Innehåll]0 {}[Väg till källdata]
Låt oss se vad den består av:
- Excel.CurrentWorkbook() är en funktion av M-språket för att komma åt innehållet i den aktuella filen
- {[Name="Inställningar"]}[Innehåll] – detta är en förfiningsparameter till föregående funktion, vilket indikerar att vi vill få innehållet i den "smarta" tabellen parametrar
- [Väg till källdata] är namnet på kolumnen i tabellen parametrarsom vi hänvisar till
- 0 {} är radnumret i tabellen parametrarsom vi vill hämta data från. Locket räknas inte och numreringen börjar från noll, inte från ett.
Det är allt, faktiskt.
Det återstår att klicka på Finish och kontrollera hur vår begäran fungerar. När nu hela mappen med båda filerna inuti skickas till en annan dator, kommer begäran att förbli operativ och bestämmer sökvägen till data automatiskt.
- Vad är Power Query och varför behövs det när du arbetar i Microsoft Excel
- Så här importerar du ett flytande textutdrag till Power Query
- Omdesigna en XNUMXD korstabell till en platt tabell med Power Query