Saturs
Ja jau esat sācis izmantot bezmaksas Power Query pievienojumprogrammas rīkus programmā Microsoft Excel, tad pavisam drīz jūs saskarsieties ar vienu ļoti specializētu, bet ļoti biežu un kaitinošu problēmu, kas saistīta ar nepārtrauktu saišu pārtraukšanu uz avota datiem. Problēmas būtība ir tāda, ka, ja vaicājumā atsaucaties uz ārējiem failiem vai mapēm, Power Query vaicājuma tekstā iekodē absolūto ceļu uz tiem. Jūsu datorā viss darbojas labi, bet, ja jūs nolemjat nosūtīt failu ar pieprasījumu saviem kolēģiem, tad viņi būs vīlušies, jo. viņiem ir atšķirīgs ceļš uz avota datiem savā datorā, un mūsu vaicājums nedarbosies.
Ko darīt šādā situācijā? Apskatīsim šo gadījumu sīkāk, izmantojot šādu piemēru.
Problēmas formulēšana
Pieņemsim, ka mums ir mapē E:Pārdošanas pārskati slēpjas fails Top 100 produkti.xls, kas ir augšupielāde no mūsu korporatīvās datu bāzes vai ERP sistēmas (1C, SAP utt.) Šis fails satur informāciju par populārākajām preču precēm un izskatās šādi:
Iespējams, uzreiz ir skaidrs, ka ar to Excel ir gandrīz neiespējami strādāt šādā formā: tukšas rindas līdz vienai ar datiem, sapludinātas šūnas, papildu kolonnas, daudzlīmeņu galvene utt.
Tāpēc blakus šim failam tajā pašā mapē mēs izveidojam vēl vienu jaunu failu Handler.xlsx, kurā mēs izveidosim Power Query vaicājumu, kas ielādēs neglītus datus no avota augšupielādes faila Top 100 produkti.xls, un sakārtojiet tos:
Pieprasījuma veikšana ārējam failam
Faila atvēršana Handler.xlsx, atlasiet cilnē Datums Komanda Datu iegūšana — no faila — no Excel darbgrāmatas (Dati — iegūt datus — no faila — no Excel), pēc tam norādiet avota faila un vajadzīgās lapas atrašanās vietu. Atlasītie dati tiks ielādēti Power Query redaktorā:
Atgriezīsim tos normālā stāvoklī:
- Dzēsiet tukšas rindas ar Sākums — Dzēst rindas — Dzēst tukšas rindas (Sākums — Noņemt rindas — Noņemt tukšās rindas).
- Izdzēsiet nevajadzīgās 4 augšējās rindas Sākums — Dzēst rindas — Dzēst augšējās rindas (Sākums — Noņemt rindas — Noņemt augšējās rindas).
- Ar pogu paceliet pirmo rindu līdz tabulas galvenei Izmantojiet pirmo rindiņu kā galvenes tab Sākumlapa (Sākums — izmantojiet pirmo rindu kā galveni).
- Atdaliet piecu ciparu rakstu no produkta nosaukuma otrajā kolonnā, izmantojot komandu sadalīta kolonna tab transformācija (Pārveidot — sadalīta kolonna).
- Izdzēsiet nevajadzīgās kolonnas un pārdēvējiet atlikušo virsrakstus, lai nodrošinātu labāku redzamību.
Rezultātā mums vajadzētu iegūt šādu, daudz patīkamāku attēlu:
Atliek augšupielādēt šo uzlaboto tabulu atpakaļ uz mūsu faila lapu Handler.xlsx komanda aizveriet un lejupielādējiet (Sākums — aizvērt un ielādēt) tab Sākumlapa:
Pieprasījumā esošā faila ceļa atrašana
Tagad redzēsim, kā mūsu vaicājums izskatās “zem pārsega” iekšējā valodā, kas iebūvēta Power Query ar īsu nosaukumu “M”. Lai to izdarītu, atgriezieties pie mūsu vaicājuma, veicot dubultklikšķi uz tā labajā rūtī Pieprasījumi un savienojumi un cilnē Pārskats izvēlēties Uzlabots redaktors (Skatīt — uzlabotais redaktors):
Atvērtajā logā otrā rindiņa uzreiz atklāj šifrētu ceļu uz mūsu sākotnējo augšupielādes failu. Ja mēs varam aizstāt šo teksta virkni ar parametru, mainīgo vai saiti uz Excel lapas šūnu, kurā šis ceļš ir iepriekš uzrakstīts, mēs to varam viegli mainīt vēlāk.
Pievienojiet viedo tabulu ar faila ceļu
Pagaidām aizvērsim Power Query un atgriezīsimies pie faila Handler.xlsx. Pievienosim jaunu tukšu lapu un izveidosim uz tās nelielu “gudru” tabulu, kuras vienīgajā šūnā tiks ierakstīts pilns ceļš uz mūsu avota datu failu:
Lai izveidotu viedo tabulu no parasta diapazona, varat izmantot īsinājumtaustiņus Ctrl+T vai pogu Formatēt kā tabulu tab Sākumlapa (Sākums — formatēt kā tabulu). Kolonnas virsraksts (šūna A1) var būt pilnīgi jebkas. Ņemiet vērā arī to, ka skaidrības labad esmu devusi tabulai nosaukumu parametri tab Konstruktors (Dizains).
Ceļa kopēšana no Explorer vai pat manuāla ievadīšana, protams, nav īpaši sarežģīta, taču vislabāk ir samazināt cilvēcisko faktoru un noteikt ceļu, ja iespējams, automātiski. To var ieviest, izmantojot standarta Excel darblapas funkciju ŠŪNA (ŠŪNA), kas var sniegt daudz noderīgas informācijas par šūnu, kas norādīta kā arguments, tostarp ceļš uz pašreizējo failu:
Ja pieņemam, ka avota datu fails vienmēr atrodas tajā pašā mapē, kurā atrodas mūsu procesors, tad mums nepieciešamo ceļu var izveidot pēc šādas formulas:
=LEFT(CELL("faila nosaukums");Atrast("[";ŠŪNA("faila nosaukums")-1)&"100 populārākie produkti.xls"
vai angļu valodā:
=LEFT(CELL(«faila nosaukums»);Atrast(«[«;ŠŪNA(«faila nosaukums»))-1)&»Топ-100 товаров.xls»
… kur ir funkcija LEVSIMV (KREISI) aizņem teksta daļu no pilnas saites līdz sākuma kvadrātiekavai (ti, ceļu uz pašreizējo mapi), un pēc tam tam tiek pielīmēts mūsu avota datu faila nosaukums un paplašinājums.
Parametrējiet ceļu vaicājumā
Atliek pēdējais un svarīgākais pieskāriens – pieprasījumā ierakstīt ceļu uz avota failu Top 100 produkti.xls, atsaucoties uz mūsu izveidotās “viedās” tabulas šūnu A2 parametri.
Lai to izdarītu, atgriezīsimies pie Power Query vaicājuma un atveriet to vēlreiz Uzlabots redaktors tab Pārskats (Skatīt — uzlabotais redaktors). Teksta virknes-ceļa vietā pēdiņās “E: Pārdošanas pārskati 100 populārākie produkti.xlsx” Ieviesīsim šādu struktūru:
Excel.CurrentWorkbook(){[Name=”Iestatījumi”]}[Saturs]0 {}[Ceļš uz avota datiem]
Apskatīsim, no kā tas sastāv:
- Excel.CurrentWorkbook() ir M valodas funkcija, lai piekļūtu pašreizējā faila saturam
- {[Name=”Iestatījumi”]}[Saturs] - tas ir iepriekšējās funkcijas precizēšanas parametrs, kas norāda, ka mēs vēlamies iegūt "viedās" tabulas saturu parametri
- [Ceļš uz avota datiem] ir tabulas kolonnas nosaukums parametriuz ko mēs atsaucamies
- 0 {} ir rindas numurs tabulā parametrino kuriem mēs vēlamies ņemt datus. Vāciņš neskaitās un numerācija sākas no nulles, nevis no viena.
Tas arī viss, patiesībā.
Atliek noklikšķināt apdare un pārbaudiet, kā darbojas mūsu pieprasījums. Tagad, nosūtot visu mapi ar abiem failiem uz citu datoru, pieprasījums turpinās darboties un automātiski nosaka ceļu uz datiem.
- Kas ir Power Query un kāpēc tas ir nepieciešams, strādājot programmā Microsoft Excel
- Kā importēt peldošā teksta fragmentu programmā Power Query
- XNUMXD Crosstab pārveidošana par plakanu tabulu, izmantojot Power Query