Parameterizing Data Paths mu Power Query

Ngati mwayamba kale kugwiritsa ntchito zida za Power Query add-in mu Microsoft Excel, ndiye posachedwa mudzakumana ndi vuto limodzi lapadera, koma pafupipafupi komanso lokwiyitsa lomwe limakhudzana ndi kuswa maulalo pafupipafupi kuti mupeze deta. Chofunikira chavuto ndichakuti ngati mufunso lanu mumayang'ana mafayilo akunja kapena zikwatu, ndiye kuti Power Query hardcodes njira yofikira kwa iwo pazolemba zamafunso. Chilichonse chimagwira ntchito bwino pa kompyuta yanu, koma ngati mwasankha kutumiza fayilo ndi pempho kwa anzanu, ndiye kuti adzakhumudwitsidwa, chifukwa. ali ndi njira yosiyana yopezera deta pakompyuta yawo, ndipo funso lathu silingagwire ntchito.

Zotani zikatero? Tiyeni tiwone nkhaniyi mwatsatanetsatane ndi chitsanzo chotsatirachi.

Kupanga vuto

Tiyerekeze kuti tili mufoda E:Malipoti ogulitsa yanama fayilo Zogulitsa 100 zapamwamba.xls, yomwe imakwezedwa kuchokera ku database yathu yamakampani kapena dongosolo la ERP (1C, SAP, etc.) Fayiloyi ili ndi chidziwitso chokhudza zinthu zotchuka kwambiri ndipo ikuwoneka ngati izi mkati:

Zikuwonekeratu pomwepo kuti ndizosatheka kugwira nawo ntchito mu Excel mwanjira iyi: mizere yopanda kanthu kudutsa imodzi yokhala ndi data, ma cell ophatikizidwa, mizati yowonjezera, mutu wamitundu yambiri, ndi zina zambiri.

Chifukwa chake, pafupi ndi fayiloyi mufoda yomweyi, timapanga fayilo ina yatsopano Handler.xlsx, momwe tidzapangire funso la Power Query lomwe lidzatsegula deta yonyansa kuchokera ku fayilo yoyika gwero Zogulitsa 100 zapamwamba.xls, ndi kuziika mu dongosolo:

Kupanga pempho ku fayilo yakunja

Kutsegula fayilo Handler.xlsx, sankhani pa tabu Deta lamulo Pezani Zambiri - Kuchokera Fayilo - Kuchokera ku Excel Workbook (Deta - Pezani Zambiri - Kuchokera pafayilo - Kuchokera ku Excel), kenako tchulani malo a fayilo yoyambira ndi pepala lomwe tikufuna. Zomwe zasankhidwa zidzakwezedwa mu Power Query editor:

Tiyeni tibwerere ku chikhalidwe:

1. Chotsani mizere yopanda kanthu ndi Kunyumba - Chotsani mizere - Chotsani mizere yopanda kanthu (Kunyumba - Chotsani Mizere - Chotsani Mizere Yopanda kanthu).
2. Chotsani mizere 4 yapamwamba yosafunikira Kunyumba - Chotsani Mizere - Chotsani Mizere Yapamwamba (Kunyumba - Chotsani Mizere - Chotsani Mizere Yapamwamba).
3. Kwezani mzere woyamba kumutu wa tebulo ndi batani Gwiritsani ntchito mzere woyamba ngati mitu tsamba Kunyumba (Kunyumba - Gwiritsani ntchito mzere woyamba ngati mutu).
4. Lekanitsa nkhani ya manambala asanu kuchokera ku dzina lazogulitsa mugawo lachiwiri pogwiritsa ntchito lamulo gawo logawanika tsamba Transformation (Sinthani - Gawani Mzere).
5. Chotsani mizati yosafunikira ndikutchulanso mitu ya otsalawo kuti muwoneke bwino.

Zotsatira zake, tiyenera kupeza chithunzi chotsatirachi, chosangalatsa kwambiri:

Zimatsalira kukweza tebulo lopangidwa bwinoli kutsamba lomwe lili mufayilo yathu Handler.xlsx timu kutseka ndikutsitsa (Kunyumba - Tsekani & Katundu) tsamba Kunyumba:

Kupeza njira yopita ku fayilo mu pempho

Tsopano tiyeni tiwone momwe funso lathu limawonekera "pansi pa hood", m'chinenero chamkati chomangidwa mu Power Query ndi dzina lachidule "M". Kuti muchite izi, bwererani ku funso lathu ndikudina kawiri pagawo lakumanja Zofunsira ndi kulumikizana ndi pa tabu Review kusankha Advanced Editor (Onani - Advanced Editor):

Pazenera lomwe limatsegulidwa, mzere wachiwiri nthawi yomweyo umawulula njira yolimba yolowera ku fayilo yathu yoyambira. Ngati titha kusintha chingwe chalembachi ndi parameter, kusinthika, kapena ulalo ku cell sheet ya Excel pomwe njirayi idalembedweratu, ndiye kuti titha kuyisintha pambuyo pake.

Onjezani tebulo lanzeru ndi njira yamafayilo

Tiyeni titseke Power Query pakadali pano ndikubwerera ku fayilo yathu Handler.xlsx. Tiyeni tiwonjezere pepala latsopano lopanda kanthu ndikupanga tebulo laling'ono "lanzeru", mu selo yokhayo yomwe njira yonse yopita ku fayilo yathu ya data idzalembedwa:

Kuti mupange tebulo lanzeru kuchokera kunthawi zonse, mutha kugwiritsa ntchito njira yachidule ya kiyibodi Ctrl+T kapena batani Pangani ngati tebulo tsamba Kunyumba (Kunyumba - Pangani Monga Table). Mutu wagawo (selo A1) ukhoza kukhala chilichonse. Onaninso kuti kuti zimveke bwino ndapatsa tebulo dzina magawo tsamba Constructor (Kapangidwe).

Kukopera njira yochokera ku Explorer kapena kuilowetsa pamanja, ndithudi, sikovuta kwambiri, koma ndi bwino kuchepetsa chiwerengero cha anthu ndikuzindikira njira, ngati n'kotheka, basi. Izi zitha kuchitika pogwiritsa ntchito ntchito yokhazikika ya Excel worksheet Cell (SELU), yomwe ingapereke zambiri zothandiza za selo lomwe latchulidwa ngati mkangano - kuphatikizapo njira yopita ku fayilo yamakono:

Ngati tikuganiza kuti fayilo yochokera ku data nthawi zonse imakhala mufoda yomweyi monga processor yathu, ndiye kuti njira yomwe tikufuna ikhoza kupangidwa ndi njira iyi:

=KULEFT(CELL("fayilo");PEZANI("[";CELL("fayilo"))-1)&"Top 100 products.xls"

kapena mu Chingerezi:

=KULEFT(CELL(«fayilo»); PEZANI(«[«; CELL(«dzina lafayilo»)))-1)&»Топ-100 товаров.xls»

… ntchito ili kuti LEVSIMV (Kumanzere) amatenga chidutswa cha mawu kuchokera pa ulalo wathunthu kupita ku bulaketi lalikulu lotsegulira (ie njira yopita ku chikwatu chomwe chilipo), ndiyeno dzina ndi kukulitsa kwa fayilo yathu yochokera kumalumikizidwa.

Parameterize njira mu funso

Kukhudza komaliza komanso kofunikira kumakhalabe - kulemba njira yopita ku fayilo yoyambira mu pempho Zogulitsa 100 zapamwamba.xls, ponena za selo A2 ya tebulo lathu "lanzeru" lopangidwa magawo.

Kuti tichite izi, tiyeni tibwerere ku funso la Power Query ndikutsegulanso Advanced Editor tsamba Review (Onani - Advanced Editor). M'malo mwa njira yachingwe muzolemba "E: Malipoti ogulitsa Top 100 product.xlsx" Tiyeni tiyambitse dongosolo ili:

Excel.CurrentWorkbook(){[Dzina="Zokonda"][Zosintha]0 {}[Njira yochokera ku data]

Tiyeni tiwone chomwe chimaphatikizapo:

• Excel.CurrentWorkbook() ndi ntchito ya chilankhulo cha M kuti mupeze zomwe zili mufayilo yamakono
• {[Dzina="Zokonda"][Zosintha] - ichi ndi gawo lowongolera ku ntchito yapitayi, zomwe zikuwonetsa kuti tikufuna kupeza zomwe zili patebulo la "smart" magawo
• [Njira yochokera ku data] ndilo dzina la mzati patebulo magawozomwe tikunena
  
• 0 {} ndi nambala ya mzere mu tebulo magawokumene tikufuna kutenga deta. Chipewa sichimawerengera ndipo manambala amayambira pa ziro, osati pa chimodzi.

Ndizo zonse, kwenikweni.

Zimakhalabe kuti alemba pa chitsiriziro ndikuwona momwe pempho lathu limagwirira ntchito. Tsopano, potumiza chikwatu chonse ndi mafayilo onse mkati mwa PC ina, pempholi likhalabe likugwira ntchito ndikuzindikira njira yopita ku datayo.

• Kodi Power Query ndi chiyani ndipo chifukwa chiyani imafunikira mukamagwira ntchito mu Microsoft Excel
• Momwe mungalowetsere mawu oyandama mu Power Query
• Kupanganso XNUMXD Crosstab kukhala Patebulo Lapansi Lokhala ndi Mphamvu Yofunsa