Përmbajtje
Nëse tashmë keni filluar të përdorni veglat e shtesës falas Power Query në Microsoft Excel, atëherë shumë shpejt do të hasni një problem shumë të specializuar, por shumë të shpeshtë dhe të bezdisshëm që lidhet me thyerjen e vazhdueshme të lidhjeve me të dhënat burimore. Thelbi i problemit është se nëse në pyetjen tuaj i referoheni skedarëve ose dosjeve të jashtme, atëherë Power Query kodon rrugën absolute drejt tyre në tekstin e pyetjes. Gjithçka funksionon mirë në kompjuterin tuaj, por nëse vendosni t'i dërgoni një skedar me një kërkesë kolegëve tuaj, atëherë ata do të zhgënjehen, sepse. ata kanë një rrugë tjetër drejt të dhënave burimore në kompjuterin e tyre dhe pyetja jonë nuk do të funksionojë.
Çfarë duhet bërë në një situatë të tillë? Le ta shohim më në detaje këtë rast me shembullin e mëposhtëm.
Formulimi i problemit
Supozoni se kemi në dosje E:Raportet e shitjeve shtrihet dosja 100 produktet kryesore.xls, i cili është një ngarkim nga baza e të dhënave tona të korporatës ose sistemi ERP (1C, SAP, etj.) Ky skedar përmban informacione rreth artikujve më të njohur të mallrave dhe duket si ky brenda:
Është ndoshta e qartë menjëherë se është pothuajse e pamundur të punosh me të në Excel në këtë formë: rreshtat bosh përmes një me të dhëna, qelizat e bashkuara, kolonat shtesë, një kokë me shumë nivele, etj. do të ndërhyjnë.
Prandaj, pranë këtij skedari në të njëjtën dosje, ne krijojmë një skedar tjetër të ri Trajtuesi.xlsx, në të cilin ne do të krijojmë një pyetje Power Query që do të ngarkojë të dhëna të shëmtuara nga skedari i ngarkimit të burimit 100 produktet kryesore.xls, dhe vendosini ato në rregull:
Bërja e një kërkese për një skedar të jashtëm
Hapja e skedarit Trajtuesi.xlsx, zgjidhni në skedën Data Komandë Merrni të dhëna – Nga skedari – Nga libri i punës në Excel (Të dhënat - Merrni të dhëna - Nga skedari - Nga Excel), më pas specifikoni vendndodhjen e skedarit burim dhe fletën që na nevojitet. Të dhënat e zgjedhura do të ngarkohen në redaktorin e Power Query:
Le t'i kthejmë ato në normalitet:
- Fshi linjat boshe me Faqja kryesore — Fshi linjat — Fshi linjat boshe (Faqja kryesore - Hiq rreshtat - Hiq rreshtat bosh).
- Fshini 4 rreshtat e panevojshëm të parë Faqja kryesore — Fshi rreshtat — Fshi rreshtat kryesore (Faqja kryesore - Hiq rreshtat - Hiq rreshtat e sipërm).
- Ngrini rreshtin e parë në kokën e tabelës me butonin Përdorni rreshtin e parë si kokë tab Fillimi (Shtëpia - Përdorni rreshtin e parë si kokë).
- Ndani artikullin me pesë shifra nga emri i produktit në kolonën e dytë duke përdorur komandën kolonë e ndarë tab Transformim (Transformo - Kolona e ndarë).
- Fshini kolonat e panevojshme dhe riemërtoni titujt e atyre të mbetura për shikueshmëri më të mirë.
Si rezultat, duhet të marrim pamjen e mëposhtme, shumë më të këndshme:
Mbetet për ta ngarkuar këtë tabelë të fisnikëruar përsëri në fletën e skedarit tonë Trajtuesi.xlsx ekipi mbyllni dhe shkarkoni (Shtëpia - Mbyll&Ngarko) tab Fillimi:
Gjetja e rrugës drejt një skedari në një kërkesë
Tani le të shohim se si duket pyetja jonë "nën kapuç", në gjuhën e brendshme të integruar në Power Query me emrin konciz "M". Për ta bërë këtë, kthehuni te pyetja jonë duke klikuar dy herë mbi të në panelin e djathtë Kërkesat dhe lidhjet dhe në skedën Rishikim zgjedh Redaktues i avancuar (Shiko - Redaktues i avancuar):
Në dritaren që hapet, rreshti i dytë zbulon menjëherë një shteg të koduar në skedarin tonë origjinal të ngarkimit. Nëse mund ta zëvendësojmë këtë varg teksti me një parametër, variabël ose një lidhje në një qelizë të fletës Excel ku kjo rrugë është e parashkruar, atëherë mund ta ndryshojmë lehtësisht më vonë.
Shtoni një tabelë të zgjuar me një shteg skedari
Le të mbyllim Power Query tani për tani dhe të kthehemi te skedari ynë Trajtuesi.xlsx. Le të shtojmë një fletë të re bosh dhe të bëjmë një tabelë të vogël "të zgjuar" në të, në të vetmen qelizë të së cilës do të shkruhet shtegu i plotë për skedarin tonë të të dhënave burimore:
Për të krijuar një tabelë të zgjuar nga një gamë e rregullt, mund të përdorni shkurtoren e tastierës Ctrl+T ose butoni Formatoni si tabelë tab Fillimi (Shtëpia - Formatoni si tabelë). Titulli i kolonës (qeliza A1) mund të jetë absolutisht çdo gjë. Gjithashtu vini re se për qartësi i kam dhënë tabelës një emër Parameters tab konstruktor (Dizajn).
Kopjimi i një shtegu nga Explorer apo edhe futja e tij me dorë, natyrisht, nuk është veçanërisht e vështirë, por është më mirë të minimizoni faktorin njerëzor dhe të përcaktoni shtegun, nëse është e mundur, automatikisht. Kjo mund të zbatohet duke përdorur funksionin standard të fletës së punës Excel QELIZA (QELI), i cili mund të japë një mori informacionesh të dobishme në lidhje me qelizën e specifikuar si argument – duke përfshirë shtegun për në skedarin aktual:
Nëse supozojmë se skedari i të dhënave burimore qëndron gjithmonë në të njëjtën dosje si Procesori ynë, atëherë shtegu që na nevojitet mund të formohet me formulën e mëposhtme:
=LEFT(CELL("emri i skedarit");FIND("[";CELL("emri i skedarit"))-1)&"100 produktet kryesore.xls"
ose në versionin anglisht:
=LEFT(CELL(«emri i skedarit»);FIND(«[«;CELL(«emri i skedarit»))-1)&»Top-100 товаров.xls»
… ku është funksioni LEVSIMV (Majtas) merr një pjesë teksti nga lidhja e plotë deri te kllapa e hapjes katrore (dmth. shtegu për në dosjen aktuale), dhe më pas emri dhe shtrirja e skedarit tonë të të dhënave burimore ngjiten në të.
Parametizoni shtegun në pyetje
Mbetet prekja e fundit dhe më e rëndësishme - të shkruani shtegun drejt skedarit burimor në kërkesë 100 produktet kryesore.xls, duke iu referuar qelizës A2 të tabelës sonë "të zgjuar" të krijuar Parameters.
Për ta bërë këtë, le të kthehemi te pyetësori i Power Query dhe ta hapim përsëri Redaktues i avancuar tab Rishikim (Shiko - Redaktues i avancuar). Në vend të një vargu teksti në thonjëza "E: Raportet e shitjeve 100 produktet më të mira.xlsx" Le të prezantojmë strukturën e mëposhtme:
Excel.CurrentWorkbook(){[Name="Cilësimet"]}[Përmbajtja]0 {}[Rruga drejt të dhënave burimore]
Le të shohim se nga çfarë përbëhet:
- Excel.CurrentWorkbook() është një funksion i gjuhës M për të hyrë në përmbajtjen e skedarit aktual
- {[Name="Cilësimet"]}[Përmbajtja] - ky është një parametër i përsosjes së funksionit të mëparshëm, që tregon se duam të marrim përmbajtjen e tabelës "smart" Parameters
- [Rruga drejt të dhënave burimore] është emri i kolonës në tabelë Parameterstë cilit i referohemi
- 0 {} është numri i rreshtit në tabelë Parametersnga i cili duam të marrim të dhëna. Kapaku nuk llogaritet dhe numërimi fillon nga zero, jo nga një.
Kjo është e gjitha, në fakt.
Mbetet për të klikuar Fund dhe kontrolloni se si funksionon kërkesa jonë. Tani, kur dërgoni të gjithë dosjen me të dy skedarët brenda në një kompjuter tjetër, kërkesa do të mbetet funksionale dhe do të përcaktojë automatikisht rrugën drejt të dhënave.
- Çfarë është Power Query dhe pse nevojitet kur punoni në Microsoft Excel
- Si të importoni një fragment teksti lundrues në Power Query
- Ridizajnimi i një kryqezimi XNUMXD në një tabelë të sheshtë me Query Power