Indhold
Hvis du allerede er begyndt at bruge værktøjerne i det gratis Power Query-tilføjelsesprogram i Microsoft Excel, vil du meget snart støde på et højt specialiseret, men meget hyppigt og irriterende problem, der er forbundet med konstant at bryde links til kildedata. Essensen af problemet er, at hvis du i din forespørgsel henviser til eksterne filer eller mapper, så koder Power Query den absolutte sti til dem i forespørgselsteksten. Alt fungerer fint på din computer, men hvis du beslutter dig for at sende en fil med en anmodning til dine kolleger, så bliver de skuffede, pga. de har en anden vej til kildedataene på deres computer, og vores forespørgsel vil ikke fungere.
Hvad skal man gøre i sådan en situation? Lad os se på denne sag mere detaljeret med følgende eksempel.
Formulering af problemet
Antag, at vi har i mappen E:Salgsrapporter ligger filen Top 100 produkter.xls, som er en upload fra vores virksomhedsdatabase eller ERP-system (1C, SAP osv.) Denne fil indeholder information om de mest populære råvarer og ser sådan ud inde:
Det er sikkert klart lige fra starten, at det er næsten umuligt at arbejde med det i Excel i denne form: tomme rækker gennem en med data, flettede celler, ekstra kolonner, en overskrift på flere niveauer osv. vil forstyrre.
Derfor opretter vi en ny fil ved siden af denne fil i samme mappe Handler.xlsx, hvori vi vil oprette en Power Query-forespørgsel, der vil indlæse grimme data fra kildeoverførselsfilen Top 100 produkter.xls, og sæt dem i rækkefølge:
Forespørgsel til en ekstern fil
Åbner filen Handler.xlsx, vælg på fanen data Kommando Hent data – fra fil – fra Excel-projektmappe (Data — Hent data — Fra fil — Fra Excel), og angiv derefter placeringen af kildefilen og det ark, vi har brug for. De valgte data vil blive indlæst i Power Query-editoren:
Lad os bringe dem tilbage til det normale:
- Slet tomme linjer med Hjem — Slet linjer — Slet tomme linjer (Hjem — Fjern rækker — Fjern tomme rækker).
- Slet unødvendige top 4 linjer igennem Hjem — Slet rækker — Slet øverste rækker (Hjem — Fjern rækker — Fjern øverste rækker).
- Hæv den første række til tabeloverskriften med knappen Brug første linje som overskrifter fanen Home (Hjem — Brug første række som overskrift).
- Adskil den femcifrede artikel fra produktnavnet i anden kolonne ved hjælp af kommandoen split kolonne fanen Transformation (Transformer – Opdelt kolonne).
- Slet unødvendige kolonner og omdøb overskrifterne på de resterende for bedre synlighed.
Som et resultat bør vi få følgende, meget mere behagelige billede:
Det er tilbage at uploade denne forædlede tabel tilbage til arket i vores fil Handler.xlsx holdet luk og download (Hjem — Luk og indlæs) fanen Home:
At finde stien til en fil i en anmodning
Lad os nu se, hvordan vores forespørgsel ser ud "under motorhjelmen", på det interne sprog, der er indbygget i Power Query med det kortfattede navn "M". For at gøre dette skal du gå tilbage til vores forespørgsel ved at dobbeltklikke på den i højre rude Forespørgsler og forbindelser og på fanen anmeldelse vælge Avanceret editor (Vis — Avanceret editor):
I vinduet, der åbnes, afslører den anden linje straks en hårdkodet sti til vores originale uploadfil. Hvis vi kan erstatte denne tekststreng med en parameter, variabel eller et link til en Excel-arkcelle, hvor denne sti er forskrevet, så kan vi nemt ændre den senere.
Tilføj en smart tabel med en filsti
Lad os lukke Power Query for nu og vende tilbage til vores fil Handler.xlsx. Lad os tilføje et nyt tomt ark og lave en lille "smart" tabel på den, i den eneste celle, hvor den fulde sti til vores kildedatafil vil blive skrevet:
For at oprette et smart bord fra et almindeligt udvalg kan du bruge tastaturgenvejen Ctrl+T eller knap Formater som en tabel fanen Home (Hjem — Formater som tabel). Kolonneoverskriften (celle A1) kan være absolut hvad som helst. Bemærk også, at jeg for klarhedens skyld har givet bordet et navn parametre fanen Constructor (Design).
At kopiere en sti fra Explorer eller endda indtaste den manuelt er selvfølgelig ikke særlig vanskelig, men det er bedst at minimere den menneskelige faktor og bestemme stien, hvis det er muligt, automatisk. Dette kan implementeres ved hjælp af standard Excel regnearksfunktion CELLE (CELLE), som kan give en masse nyttige oplysninger om cellen angivet som et argument – inklusive stien til den aktuelle fil:
Hvis vi antager, at kildedatafilen altid ligger i den samme mappe som vores processor, kan den sti, vi har brug for, dannes af følgende formel:
=LEFT(CELL(“filnavn”);FIND(“[“;CELL(“filnavn”))-1)&”Top 100 produkter.xls”
eller i engelsk version:
=VENSTRE(CELLE(«filnavn»);FIND(«[«;CELLE(«filnavn»))-1)&»Топ-100 товаров.xls»
… hvor er funktionen LEVSIMV (VENSTRE) tager et stykke tekst fra det fulde link op til den åbne firkantede parentes (dvs. stien til den aktuelle mappe), og derefter limes navnet og udvidelsen af vores kildedatafil til den.
Parametrér stien i forespørgslen
Den sidste og vigtigste berøring er tilbage - at skrive stien til kildefilen i anmodningen Top 100 produkter.xls, med henvisning til celle A2 i vores oprettede "smart" tabel parametre.
For at gøre dette, lad os gå tilbage til Power Query-forespørgslen og åbne den igen Avanceret editor fanen anmeldelse (Vis — Avanceret editor). I stedet for en tekststreng-sti i anførselstegn "E:SalgsrapporterTop 100 produkter.xlsx" Lad os introducere følgende struktur:
Excel.CurrentWorkbook(){[Navn="Indstillinger"]}[Indhold]0 {}[Sti til kildedata]
Lad os se, hvad det består af:
- Excel.CurrentWorkbook() er en funktion af M-sproget til at få adgang til indholdet af den aktuelle fil
- {[Navn="Indstillinger"]}[Indhold] – dette er en forfiningsparameter til den forrige funktion, hvilket indikerer, at vi ønsker at få indholdet af den "smarte" tabel parametre
- [Sti til kildedata] er navnet på kolonnen i tabellen parametresom vi henviser til
- 0 {} er rækkenummeret i tabellen parametresom vi ønsker at tage data fra. Hætten tæller ikke, og nummereringen starter fra nul, ikke fra én.
Det er faktisk alt.
Det er tilbage at klikke på Finish og tjek, hvordan vores anmodning fungerer. Nu, når du sender hele mappen med begge filer inde til en anden pc, vil anmodningen forblive operationel og bestemme stien til dataene automatisk.
- Hvad er Power Query, og hvorfor er det nødvendigt, når du arbejder i Microsoft Excel
- Sådan importeres et flydende tekststykke til Power Query
- Omdesign af en XNUMXD krydstabel til en flad tabel med Power Query