Parameterizing Nzira dzeData mune Simba Query

Kana iwe watotanga kushandisa maturusi emahara Power Query yekuwedzera-muMicrosoft Excel, saka munguva pfupi iwe uchasangana neakanyanya hunyanzvi, asi anowanzo uye anogumbura dambudziko rine chekuita nekugara uchityora zvinongedzo kune sosi data. Izvo zvakakosha zvedambudziko ndezvekuti kana mumubvunzo wako iwe uchinongedzera kune ekunze mafaera kana maforodha, ipapo Power Query hardcodes nzira yakakwana kwavari mune yemubvunzo zvinyorwa. Zvese zvinoshanda zvakanaka pakombuta yako, asi kana ukafunga kutumira faira nechikumbiro kune vaunoshanda navo, ivo vanozoodzwa mwoyo, nekuti. vane nzira yakasiyana yekuwana data pakombiyuta yavo, uye mubvunzo wedu haushande.

Chii chekuita mumamiriro ezvinhu akadaro? Ngatitarisei nyaya iyi zvakadzama nemuenzaniso unotevera.

Kugadziriswa kwedambudziko

Ngatitii tine mufolda E:Sales mishumo nhema faira Pamusoro 100 zvigadzirwa.xls, iyo inotumirwa kubva kune yedu yekambani database kana ERP system (1C, SAP, etc.) Iyi faira ine ruzivo pamusoro pezvinhu zvakakurumbira zvezvinhu uye inoratidzika seyiyi mukati:

Zvingangodaro zvakajeka kubva pachiremwaremwa kuti hazvigoneke kushanda nayo muExcel mune iyi fomu: mitsara isina chinhu kuburikidza neimwe ine data, masero akabatanidzwa, mamwe makoramu, ane-level header, nezvimwe zvinokanganisa.

Naizvozvo, padivi peiyi faira mune imwecheteyo folda, isu tinogadzira imwe faira nyowani Handler.xlsx, umo isu tichagadzira Power Query query iyo inoremedza yakashata data kubva kune sosi yekurodha faira Pamusoro 100 zvigadzirwa.xls, wozvironga;

Kuita chikumbiro kune yekunze faira

Kuvhura faira Handler.xlsx, sarudza pane tab Data murayiro Tora Dhata - Kubva Faira - Kubva kuExcel Workbook (Data - Tora Dhata - Kubva faira - Kubva kuExcel), wobva watsanangura nzvimbo yefaira faira uye pepa ratinoda. Iyo data yakasarudzwa ichaiswa muPower Query edhita:

Ngativadzosere kune zvakajairika:

1. Delete mitsara isina chinhu ne Musha — Dzima mitsetse — Delete mitsetse isina chinhu (Kumba - Bvisa Mitsara - Bvisa Empty Rows).
2. Delete zvisina basa pamusoro 4 mitsetse kuburikidza Musha — Delete Rows — Delete Top Rows (Kumba - Bvisa Mitsara - Bvisa Mitsetse Yepamusoro).
3. Simudza mutsara wekutanga kune musoro wetafura nebhatani Shandisa mutsara wekutanga semusoro Tab Home (Kumba - Shandisa mutsara wekutanga semusoro).
4. Kupatsanura chinyorwa chine manhamba mashanu kubva pazita rechigadzirwa muchikamu chechipiri uchishandisa murairo split column Tab Kuchinja (Shandura - Split Column).
5. Bvisa makoramu asina kufanira uye utumidzezve misoro yeasara kuti aonekwe zviri nani.

Nekuda kweizvozvo, isu tinofanirwa kuwana zvinotevera, zvakanyanya kufadza mufananidzo:

Izvo zvinosara kurodha iyi ennobled tafura kudzokera kune pepa mufaira redu Handler.xlsx timu kuvhara uye kudhawunirodha (Kumba - Vhara & Rodha) Tab Home:

Kutsvaga nzira yefaira mukukumbira

Zvino ngationei kuti mubvunzo wedu unotaridzika sei “pasi pehodhi”, mumutauro wemukati wakavakwa muPower Query ine zita ripfupi rekuti “M”. Kuti uite izvi, dzokera kumubvunzo wedu nekudzvanya kaviri pairi pane yekurudyi Zvikumbiro nekubatanidza uye pa tab ongorora sarudza Advanced Editor (Ona - Yepamberi Mupepeti):

Muhwindo rinovhurwa, mutsara wechipiri unobva waratidza nzira yakaoma-coded kune yedu yekutanga yekurodha faira. Kana isu tikakwanisa kutsiva iyi tambo yemavara neparameter, shanduko, kana chinongedzo kune Excel sheet sheet apo nzira iyi yakafanonyorwa, saka tinogona kuichinja nyore gare gare.

Wedzera tafura yakangwara ine nzira yefaira

Ngativharei Power Query parizvino todzokera kufaira redu Handler.xlsx. Ngatiwedzerei pepa idzva risina chinhu uye tiite diki "smart" tafura pairi, musero chete iyo nzira yakazara kune yedu sosi data faira ichanyorwa:

Kugadzira tafura yakangwara kubva kune yakajairwa renji, unogona kushandisa iyo keyboard nzira yekudimbudzira Ctrl+T kana bhatani Fomati setafura Tab Home (Kumba - Fomati seTafura). Iyo column musoro (sero A1) inogona kuva chero chinhu. Cherechedzawo kuti kujekesa ndapa tafura zita Parameters Tab Constructor (Dhizaini).

Kutevedzera nzira kubva kuExplorer kana kutoipinda nemaoko, hongu, haina kunyanya kuoma, asi zvakanakisa kudzikisa chinhu chemunhu uye kuona nzira, kana zvichibvira, otomatiki. Izvi zvinogona kuitwa uchishandisa yakajairwa Excel worksheet basa CHEMA (SERO), iyo inogona kupa ruzivo runobatsira pamusoro pesero rinotsanangurwa senharo - kusanganisira nzira yefaira iripo:

Kana isu tikafungidzira kuti iyo sosi yedata faira inogara iri mune imwechete folda seProsesa yedu, saka nzira yatinoda inogona kuumbwa neinotevera fomula:

= LEFT(CELL("zita refaira"); TSVAGA("[";SERO("zita refaira"))-1)&"Pamusoro 100 products.xls"

kana muChirungu vhezheni:

= LEFT(CELL(«zita refaira»); TSVAGA(«[«; CELL(«zita refaira»)))-1)&»Топ-100 товаров.xls»

... basa riripi LEVSIMV (RUBOSHWE) inotora chidimbu chemavara kubva pazere link ichikwira kusvika kune yekuvhura sikwe bracket (kureva nzira inoenda kune yazvino folda), uye ipapo zita nekuwedzera kwekwakabva data data kunonamirwa kwairi.

Parameterize nzira mumubvunzo

Kubata kwekupedzisira uye kwakakosha kunosara - kunyora nzira inoenda kune sosi faira muchikumbiro Pamusoro 100 zvigadzirwa.xls, zvichireva sero A2 yetafura yedu yakagadzirwa "smart". Parameters.

Kuti tiite izvi, ngatidzokerei kune Power Query mubvunzo uye tivhure zvakare Advanced Editor Tab ongorora (Ona - Yepamberi Mupepeti). Panzvimbo pemavara tambo-nzira mune makotesheni "E: Mishumo yekutengesaPamusoro 100 zvigadzirwa.xlsx" Ngatisumei inotevera chimiro:

Excel.CurrentWorkbook(){[Zita="Settings”][Zviri mukati]0, XNUMX, XNUMX {}[Nzira kuenda kunobva data]

Ngationei kuti inosanganisirei:

• Excel.CurrentWorkbook() ibasa reM mutauro wekuwana zviri mukati mefaira razvino
• {[Zita="Settings”][Zviri mukati] - iyi ndiyo yekunatsa parameter kune yekare basa, zvichiratidza kuti isu tinoda kuwana zviri mukati me "smart" tafura. Parameters
• [Nzira kuenda kunobva data] ndiro zita rekoramu iri patafura Parameterskwatinoreva
  
• 0, XNUMX, XNUMX {} ndiyo nhamba yemutsara patafura Parameterskubva kwatinoda kutora data. Chivharo hachiverengeki uye nhamba inotanga kubva pa zero, kwete kubva kune imwe.

Ndizvo zvose, chaizvoizvo.

Rinosara kudzvanya pedza uye tarisa kuti chikumbiro chedu chinoshanda sei. Zvino, kana uchitumira iyo folda yese nemafaira ari maviri mukati kune imwe PC, chikumbiro chinoramba chichishanda uye kuona nzira yedata otomatiki.

• Chii chinonzi Power Query uye nei ichidikanwa kana uchishanda muMicrosoft Excel
• Maitiro ekuunza snippet yemavara anoyangarara muPower Query
• Kugadzirisazve XNUMXD Crosstab kune Flat Tafura ine Simba Query