Stilla gagnaleiðir í Power Query

Ef þú hefur þegar byrjað að nota verkfæri ókeypis Power Query viðbótarinnar í Microsoft Excel, þá muntu mjög fljótlega lenda í einu mjög sérhæfðu, en mjög tíðu og pirrandi vandamáli sem tengist stöðugt að brjóta tengla á upprunagögn. Kjarni vandans er sá að ef þú vísar í ytri skrár eða möppur í fyrirspurn þinni, þá kóðar Power Query algera slóðina að þeim í fyrirspurnartextanum. Allt virkar vel á tölvunni þinni, en ef þú ákveður að senda skrá með beiðni til samstarfsmanna þinna, þá verða þeir fyrir vonbrigðum, vegna þess. þeir hafa aðra leið að upprunagögnunum á tölvunni sinni og fyrirspurn okkar mun ekki virka.

Hvað á að gera í slíkum aðstæðum? Við skulum skoða þetta mál nánar með eftirfarandi dæmi.

Mótun vandans

Segjum að við höfum í möppunni E:Söluskýrslur liggur skráin Topp 100 vörur.xls, sem er upphleðsla úr fyrirtækjagagnagrunni okkar eða ERP kerfi (1C, SAP, o.s.frv.) Þessi skrá inniheldur upplýsingar um vinsælustu vöruhlutina og lítur svona út inni:

Það er líklega ljóst strax að það er nánast ómögulegt að vinna með það í Excel á þessu formi: tómar línur í gegnum eina með gögnum, sameinuðum hólfum, auka dálkum, fjölþrepa haus o.s.frv.

Þess vegna, við hliðina á þessari skrá í sömu möppu, búum við til aðra nýja skrá Handler.xlsx, þar sem við munum búa til Power Query fyrirspurn sem mun hlaða ljótum gögnum úr upprunaupphleðsluskránni Topp 100 vörur.xls, og settu þá í röð:

Að gera beiðni í utanaðkomandi skrá

Að opna skrána Handler.xlsx, veldu á flipanum Gögn Skipun Fáðu gögn - úr skrá - úr Excel vinnubók (Gögn - Fá gögn - Úr skrá - Frá Excel), tilgreindu síðan staðsetningu frumskrárinnar og blaðsins sem við þurfum. Valin gögn verða hlaðin inn í Power Query ritilinn:

Við skulum koma þeim aftur í eðlilegt horf:

1. Eyða tómum línum með Heim — Eyða línum — Eyða auðum línum (Heima — Fjarlægja raðir — Fjarlægja tómar raðir).
2. Eyða óþarfa efstu 4 línum í gegn Heim — Eyða línum — Eyða efstu línum (Heima — Fjarlægðu raðir — Fjarlægðu efstu raðir).
3. Hækkaðu fyrstu röðina að töfluhausnum með hnappinum Notaðu fyrstu línu sem haus flipi Heim (Heima - Notaðu fyrstu línu sem haus).
4. Skildu fimm stafa greinina frá vöruheitinu í öðrum dálki með því að nota skipunina skipt dálki flipi Umbreyting (Umbreyta - Skipta dálki).
5. Eyddu óþarfa dálkum og endurnefna fyrirsagnir þeirra sem eftir eru til að fá betri sýnileika.

Fyrir vikið ættum við að fá eftirfarandi, miklu skemmtilegri mynd:

Það er eftir að hlaða þessari göfuðu töflu aftur á blaðið í skránni okkar Handler.xlsx liðið loka og hlaða niður (Heima — Loka&hlaða) flipi Heim:

Að finna slóðina að skrá í beiðni

Nú skulum við sjá hvernig fyrirspurn okkar lítur út „undir hettunni“, á innra tungumáli sem er innbyggt í Power Query með hnitmiðuðu nafni „M“. Til að gera þetta, farðu aftur í fyrirspurnina okkar með því að tvísmella á hana í hægri glugganum Beiðnir og tengingar og á flipanum Review velja Háþróaður ritstjóri (Skoða — Ítarleg ritstjóri):

Í glugganum sem opnast sýnir önnur línan strax harðkóðaða slóð að upprunalegu upphleðsluskránni okkar. Ef við getum skipt út þessum textastreng fyrir færibreytu, breytu eða hlekk á Excel-blaðsreit þar sem þessi slóð er fyrirfram skrifuð, þá getum við auðveldlega breytt því síðar.

Bættu við snjalltöflu með skráarslóð

Við skulum loka Power Query í bili og fara aftur í skrána okkar Handler.xlsx. Við skulum bæta við nýju tómu blaði og búa til litla „snjöllu“ töflu á það, í eina reitnum sem öll slóðin að upprunagagnaskránni okkar verður skrifuð:

Til að búa til snjallborð úr venjulegu úrvali geturðu notað flýtilykla Ctrl+T eða hnappur Snið sem töflu flipi Heim (Heima - Snið sem töflu). Dálkafyrirsögnin (hólf A1) getur verið nákvæmlega hvað sem er. Athugaðu líka að til glöggvunar hef ég gefið töflunni nafn breytur flipi Framkvæmdaaðili (Hönnun).

Að afrita slóð úr Explorer eða jafnvel slá hana inn handvirkt er auðvitað ekkert sérstaklega erfitt, en best er að lágmarka mannlega þáttinn og ákveða slóðina, ef hægt er, sjálfkrafa. Þetta er hægt að útfæra með því að nota staðlaða Excel vinnublaðaaðgerðina SELJA (klefi), sem getur gefið út fullt af gagnlegum upplýsingum um reitinn sem tilgreindur er sem rök - þar á meðal slóðina að núverandi skrá:

Ef við gerum ráð fyrir að frumgagnaskráin sé alltaf í sömu möppu og örgjörvinn okkar, þá getur slóðin sem við þurfum verið mynduð með eftirfarandi formúlu:

=LEFT(CELL(“skráarnafn”);FIND(“[“;CELL(“skráarnafn”))-1)&“Top 100 vörur.xls“

eða í enskri útgáfu:

=LEFT(CELL(«skráarnafn»); FIND(«[«;CELL(«filename»))-1)&»Топ-100 товаров.xls»

… hvar er fallið LEVSIMV (VINSTRI) tekur texta frá öllum hlekknum upp í hornklofa sem opnuð er (þ.e. slóðin að núverandi möppu) og síðan er nafn og ending upprunagagnaskrárinnar límd við hana.

Stilltu slóðina í fyrirspurninni

Síðasta og mikilvægasta snertingin er eftir - að skrifa slóðina að upprunaskránni í beiðninni Topp 100 vörur.xls, sem vísar í reit A2 í „snjöllu“ töflunni okkar sem búið var til breytur.

Til að gera þetta skulum við fara aftur í Power Query fyrirspurnina og opna hana aftur Háþróaður ritstjóri flipi Review (Skoða — Ítarleg ritstjóri). Í stað textastrengs-slóðar innan gæsalappa „E: SöluskýrslurTop 100 vörur.xlsx“ Við skulum kynna eftirfarandi uppbyggingu:

Excel.CurrentWorkbook(){[Name=“Settings”]}[Efni]0 {}[Leið að upprunagögnum]

Við skulum sjá hvað það samanstendur af:

• Excel.CurrentWorkbook() er fall af M tungumálinu til að fá aðgang að innihaldi núverandi skráar
• {[Name=“Settings”]}[Efni] – þetta er fínstillingarfæribreyta við fyrri aðgerðina, sem gefur til kynna að við viljum fá innihald „snjöllu“ töflunnar breytur
• [Leið að upprunagögnum] er heiti dálksins í töflunni breytursem við vísum til
  
• 0 {} er línunúmerið í töflunni breytursem við viljum taka gögn úr. Lokið telur ekki og númerið byrjar frá núlli, ekki frá einum.

Það er allt í rauninni.

Það á eftir að smella á Ljúka og athugaðu hvernig beiðni okkar virkar. Nú, þegar þú sendir alla möppuna með báðar skrárnar inni í aðra tölvu, mun beiðnin haldast í notkun og ákvarða slóðina að gögnunum sjálfkrafa.

• Hvað er Power Query og hvers vegna er það nauðsynlegt þegar unnið er í Microsoft Excel
• Hvernig á að flytja fljótandi textabút inn í Power Query
• Endurhanna XNUMXD krosstöflu í flatt borð með Power Query