Contents [show]
As jy reeds die gereedskap van die gratis Power Query-byvoeging in Microsoft Excel begin gebruik het, sal jy binnekort een hoogs gespesialiseerde, maar baie gereelde en irriterende probleem teëkom wat geassosieer word met die voortdurend breek van skakels na brondata. Die kern van die probleem is dat as jy in jou navraag na eksterne lêers of vouers verwys, dan hardkodeer Power Query die absolute pad na hulle in die navraagteks. Alles werk goed op jou rekenaar, maar as jy besluit om 'n lêer met 'n versoek aan jou kollegas te stuur, dan sal hulle teleurgesteld wees, want. hulle het 'n ander pad na die brondata op hul rekenaar, en ons navraag sal nie werk nie.
Wat om te doen in so 'n situasie? Kom ons kyk na hierdie geval in meer besonderhede met die volgende voorbeeld.
Formulering van die probleem
Gestel ons het in die gids E:Verkoopsverslae lê die lêer Top 100 produkte.xls, wat 'n oplaai is vanaf ons korporatiewe databasis of ERP-stelsel (1C, SAP, ens.) Hierdie lêer bevat inligting oor die gewildste kommoditeitsitems en lyk soos volg binne:
Dit is waarskynlik dadelik duidelik dat dit byna onmoontlik is om daarmee in Excel in hierdie vorm te werk: leë rye deur een met data, saamgevoegde selle, ekstra kolomme, 'n multi-vlak kop, ens. sal inmeng.
Daarom, langs hierdie lêer in dieselfde vouer, skep ons nog 'n nuwe lêer Hanteerder.xlsx, waarin ons 'n Power Query-navraag sal skep wat lelike data vanaf die bronoplaailêer sal laai Top 100 produkte.xls, en plaas hulle in volgorde:
Maak 'n versoek na 'n eksterne lêer
Maak die lêer oop Hanteerder.xlsx, kies op die blad data Command Kry Data - Van Lêer - Van Excel Werkboek (Data - Kry Data - Van lêer - Van Excel), spesifiseer dan die ligging van die bronlêer en die blad wat ons benodig. Die geselekteerde data sal in die Power Query-redigeerder gelaai word:
Kom ons bring hulle terug na normaal:
- Vee leë reëls uit met Tuis — Vee reëls uit — Vee leë reëls uit (Tuis — Verwyder rye — Verwyder leë rye).
- Verwyder onnodige top 4 reëls deur Tuis — Vee rye uit — Vee boonste rye uit (Tuis — Verwyder rye — Verwyder boonste rye).
- Lig die eerste ry na die tabelkopskrif met die knoppie Gebruik eerste reël as opskrifte tab What is This (Tuis - Gebruik eerste ry as kopskrif).
- Skei die vyfsyferartikel van die produknaam in die tweede kolom deur die opdrag te gebruik verdeel kolom tab Transformasie (Transformeer – Verdeel kolom).
- Vee onnodige kolomme uit en hernoem die opskrifte van die oorblywendes vir beter sigbaarheid.
As gevolg hiervan behoort ons die volgende, baie aangenamer prentjie te kry:
Dit bly om hierdie veredelde tabel terug te laai na die blad in ons lêer Hanteerder.xlsx die span maak toe en laai af (Tuis — Maak toe en laai) tab What is This:
Vind die pad na 'n lêer in 'n versoek
Kom ons kyk nou hoe ons navraag “onder die enjinkap” lyk, in die interne taal wat in Power Query ingebou is met die bondige naam “M”. Om dit te doen, gaan terug na ons navraag deur daarop te dubbelklik in die regter paneel Versoeke en verbindings en op die blad Resensie kies Gevorderde redakteur (Bekyk - Gevorderde Redigeerder):
In die venster wat oopmaak, wys die tweede reël onmiddellik 'n hardgekodeerde pad na ons oorspronklike oplaailêer. As ons hierdie teksstring kan vervang met 'n parameter, veranderlike of 'n skakel na 'n Excel-bladsel waar hierdie pad vooraf geskryf is, dan kan ons dit later maklik verander.
Voeg 'n slim tabel met 'n lêerpad by
Kom ons maak Power Query vir eers toe en keer terug na ons lêer Hanteerder.xlsx. Kom ons voeg 'n nuwe leë vel by en maak 'n klein "slim" tabel daarop, in die enigste sel waarvan die volledige pad na ons brondatalêer geskryf sal word:
Om 'n slim tafel uit 'n gewone reeks te skep, kan jy die sleutelbordkortpad gebruik Ctrl+T of knoppie Formateer as 'n tabel tab What is This (Tuis — Formateer as tabel). Die kolomopskrif (sel A1) kan absoluut enigiets wees. Let ook daarop dat ek vir duidelikheid die tabel 'n naam gegee het Grense tab konstruktor (Ontwerp).
Om 'n pad vanaf Explorer te kopieer of selfs met die hand in te voer is natuurlik nie besonder moeilik nie, maar dit is die beste om die menslike faktor te minimaliseer en die pad, indien moontlik, outomaties te bepaal. Dit kan geïmplementeer word met behulp van die standaard Excel-werkbladfunksie SEL (SEL), wat 'n klomp nuttige inligting kan gee oor die sel wat as 'n argument gespesifiseer is - insluitend die pad na die huidige lêer:
As ons aanvaar dat die brondatalêer altyd in dieselfde vouer as ons verwerker lê, kan die pad wat ons benodig deur die volgende formule gevorm word:
=LINKS(SEL(“lêernaam”);VIND(“[“;SEL(“lêernaam”))-1)&”Top 100 produkte.xls”
of in Engelse weergawe:
=LINKS(SEL(«lêernaam»);VIND(«[«;SEL(«lêernaam»))-1)&»Топ-100 товаров.xls»
… waar is die funksie LEVSIMV (Links) neem 'n stukkie teks van die volledige skakel tot by die opening vierkantige hakie (dws die pad na die huidige vouer), en dan word die naam en uitbreiding van ons brondatalêer daaraan vasgeplak.
Parameteriseer die pad in die navraag
Die laaste en belangrikste aanraking bly oor - om die pad na die bronlêer in die versoek te skryf Top 100 produkte.xls, met verwysing na sel A2 van ons geskepte "slim" tabel Grense.
Om dit te doen, kom ons gaan terug na die Power Query-navraag en maak dit weer oop Gevorderde redakteur tab Resensie (Bekyk - Gevorderde Redigeerder). In plaas van 'n teksstringpad tussen aanhalingstekens "E:Verkope verslaeTop 100 produkte.xlsx" Kom ons stel die volgende struktuur bekend:
Excel.CurrentWorkbook(){[Naam="Instellings"]}[Inhoud]0 {}[Pad na brondata]
Kom ons kyk waaruit dit bestaan:
- Excel.CurrentWorkbook() is 'n funksie van die M-taal vir toegang tot die inhoud van die huidige lêer
- {[Naam="Instellings"]}[Inhoud] - dit is 'n verfyning parameter vir die vorige funksie, wat aandui dat ons die inhoud van die "slim" tabel wil kry Grense
- [Pad na brondata] is die naam van die kolom in die tabel Grensewaarna ons verwys
- 0 {} is die rynommer in die tabel Grensewaaruit ons data wil neem. Die pet tel nie en die nommering begin van nul, nie van een nie.
Dit is al, in werklikheid.
Dit bly om op te klik Voltooi en kyk hoe ons versoek werk. Nou, wanneer die hele vouer met beide lêers binne na 'n ander rekenaar gestuur word, sal die versoek operasioneel bly en die pad na die data outomaties bepaal.
- Wat is Power Query en hoekom is dit nodig wanneer jy in Microsoft Excel werk
- Hoe om 'n drywende teksbrokkie in Power Query in te voer
- Herontwerp van 'n XNUMXD kruistabel na 'n plat tafel met Power Query