Innhold
Hvis du allerede har begynt å bruke verktøyene til det gratis Power Query-tillegget i Microsoft Excel, vil du snart støte på et svært spesialisert, men svært hyppig og irriterende problem forbundet med stadig å bryte koblinger til kildedata. Essensen av problemet er at hvis du i spørringen din refererer til eksterne filer eller mapper, så koder Power Query den absolutte banen til dem i spørringsteksten. Alt fungerer bra på datamaskinen din, men hvis du bestemmer deg for å sende en fil med en forespørsel til kollegene dine, vil de bli skuffet, fordi. de har en annen vei til kildedataene på datamaskinen, og spørringen vår vil ikke fungere.
Hva skal man gjøre i en slik situasjon? La oss se på denne saken mer detaljert med følgende eksempel.
Formulering av problemet
Anta at vi har i mappen E:Salgsrapporter ligger filen Topp 100 produkter.xls, som er en opplasting fra vår bedriftsdatabase eller ERP-system (1C, SAP, etc.) Denne filen inneholder informasjon om de mest populære varevarene og ser slik ut inni:
Det er sannsynligvis klart med en gang at det er nesten umulig å jobbe med det i Excel i denne formen: tomme rader gjennom en med data, sammenslåtte celler, ekstra kolonner, en overskrift på flere nivåer, etc. vil forstyrre.
Derfor oppretter vi en ny fil ved siden av denne filen i samme mappe Handler.xlsx, der vi vil lage en Power Query-spørring som vil laste inn stygge data fra kildeopplastingsfilen Topp 100 produkter.xls, og sett dem i rekkefølge:
Sende en forespørsel til en ekstern fil
Åpner filen Handler.xlsx, velg på fanen Data Kommando Hent data – fra fil – fra Excel-arbeidsbok (Data — Hent data — Fra fil — Fra Excel), spesifiser deretter plasseringen til kildefilen og arket vi trenger. De valgte dataene vil bli lastet inn i Power Query-redigeringsprogrammet:
La oss bringe dem tilbake til det normale:
- Slett tomme linjer med Hjem — Slett linjer — Slett tomme linjer (Hjem — Fjern rader — Fjern tomme rader).
- Slett unødvendig topp 4 linjer gjennom Hjem — Slett rader — Slett øverste rader (Hjem — Fjern rader — Fjern øverste rader).
- Hev den første raden til tabelloverskriften med knappen Bruk første linje som overskrifter tab Hjemprodukt (Hjem – Bruk første rad som overskrift).
- Skill den femsifrede artikkelen fra produktnavnet i den andre kolonnen ved å bruke kommandoen delt kolonne tab Transformation (Transform – delt kolonne).
- Slett unødvendige kolonner og gi nytt navn til overskriftene til de resterende for bedre synlighet.
Som et resultat bør vi få følgende, mye mer behagelige bilde:
Det gjenstår å laste opp denne foredlede tabellen tilbake til arket i filen vår Handler.xlsx laget lukk og last ned (Hjem — Lukk og last) tab Hjemprodukt:
Finne banen til en fil i en forespørsel
La oss nå se hvordan søket vårt ser ut "under panseret", på det interne språket innebygd i Power Query med det konsise navnet "M". For å gjøre dette, gå tilbake til søket vårt ved å dobbeltklikke på det i høyre rute Forespørsler og koblinger og på fanen Anmeldelse velge Avansert redaktør (Vis — Avansert redaktør):
I vinduet som åpnes, avslører den andre linjen umiddelbart en hardkodet bane til vår opprinnelige opplastingsfil. Hvis vi kan erstatte denne tekststrengen med en parameter, variabel eller en lenke til en Excel-arkcelle hvor denne banen er forhåndsskrevet, så kan vi enkelt endre den senere.
Legg til en smart tabell med en filbane
La oss lukke Power Query for nå og gå tilbake til filen vår Handler.xlsx. La oss legge til et nytt tomt ark og lage en liten "smart" tabell på den, i den eneste cellen som hele banen til kildedatafilen vår vil bli skrevet:
For å lage et smart bord fra et vanlig utvalg, kan du bruke hurtigtasten Ctrl+T eller knappen Formater som en tabell tab Hjemprodukt (Hjem — Formater som tabell). Kolonneoverskriften (celle A1) kan være absolutt hva som helst. Legg også merke til at jeg for klarhetens skyld har gitt tabellen et navn parametere tab Constructor (Design).
Å kopiere en sti fra Explorer eller til og med legge inn den manuelt er selvfølgelig ikke spesielt vanskelig, men det er best å minimere den menneskelige faktoren og bestemme banen, hvis mulig, automatisk. Dette kan implementeres ved hjelp av standard Excel-regnearkfunksjon CELLE (CELLE), som kan gi ut en haug med nyttig informasjon om cellen spesifisert som et argument – inkludert banen til gjeldende fil:
Hvis vi antar at kildedatafilen alltid ligger i samme mappe som prosessoren vår, kan banen vi trenger dannes av følgende formel:
=LEFT(CELL(“filnavn”);FINN(“[“;CELL(“filnavn”))-1)&”Topp 100 produkter.xls”
eller i engelsk versjon:
=VENSTRE(CELLE(«filnavn»);FINN(«[«;CELLE(«filnavn»))-1)&»Топ-100 товаров.xls»
… hvor er funksjonen LEVSIMV (VENSTRE) tar et stykke tekst fra den fullstendige lenken opp til den åpne hakeparentesen (dvs. banen til den gjeldende mappen), og deretter limes navnet og filtypen til kildedatafilen til den.
Parametriser banen i spørringen
Den siste og viktigste berøringen gjenstår - å skrive banen til kildefilen i forespørselen Topp 100 produkter.xls, med henvisning til celle A2 i vår opprettede "smart" tabell parametere.
For å gjøre dette, la oss gå tilbake til Power Query-spørringen og åpne den igjen Avansert redaktør tab Anmeldelse (Vis — Avansert redaktør). I stedet for en tekststreng-bane i anførselstegn "E: Salgsrapporter Topp 100 produkter.xlsx" La oss introdusere følgende struktur:
Excel.CurrentWorkbook(){[Name="Innstillinger"]}[Innhold]0 {}[Veien til kildedata]
La oss se hva den består av:
- Excel.CurrentWorkbook() er en funksjon av M-språket for å få tilgang til innholdet i gjeldende fil
- {[Name="Innstillinger"]}[Innhold] - dette er en avgrensningsparameter til forrige funksjon, som indikerer at vi ønsker å få innholdet i den "smarte" tabellen parametere
- [Veien til kildedata] er navnet på kolonnen i tabellen parameteresom vi viser til
- 0 {} er radnummeret i tabellen parameteresom vi ønsker å hente data fra. Toppen teller ikke og nummereringen starter fra null, ikke fra én.
Det er alt, faktisk.
Det gjenstår å klikke på Finish og sjekk hvordan forespørselen vår fungerer. Nå, når du sender hele mappen med begge filene inne til en annen PC, vil forespørselen forbli operativ og bestemme banen til dataene automatisk.
- Hva er Power Query og hvorfor er det nødvendig når du arbeider i Microsoft Excel
- Slik importerer du en flytende tekstbit til Power Query
- Redesigne en XNUMXD krysstabell til en flat tabell med Power Query