Parameterizing Data Paths nyob rau hauv Power Query

Yog tias koj twb tau pib siv cov cuab yeej ntawm cov lus nug dawb ntxiv hauv Microsoft Excel, tom qab ntawd sai sai koj yuav ntsib ib qho tshwj xeeb, tab sis qhov teeb meem nquag thiab ntxhov siab cuam tshuam nrog kev txuas mus tas li rau cov ntaub ntawv. Lub ntsiab ntawm qhov teeb meem yog tias yog tias hauv koj cov lus nug koj xa mus rau lwm cov ntaub ntawv lossis cov folders, ces Power Query hardcodes txoj hauv kev rau lawv hauv cov lus nug. Txhua yam ua haujlwm zoo hauv koj lub computer, tab sis yog tias koj txiav txim siab xa cov ntaub ntawv nrog kev thov rau koj cov npoj yaig, ces lawv yuav poob siab, vim. lawv muaj txoj hauv kev sib txawv rau cov ntaub ntawv hauv lawv lub computer, thiab peb cov lus nug yuav tsis ua haujlwm.

Yuav ua li cas rau qhov xwm txheej zoo li no? Cia peb saib cov ntaub ntawv no kom ntxaws ntxiv nrog rau cov piv txwv hauv qab no.

Formulation ntawm qhov teeb meem

Piv txwv tias peb muaj nyob rau hauv daim ntawv tais ceev tseg E:Cov ntaub ntawv muag khoom dag cov ntaub ntawv Sab saum toj 100 khoom.xls, uas yog ib qho kev xa tawm los ntawm peb cov tuam txhab database lossis ERP system (1C, SAP, thiab lwm yam) Cov ntaub ntawv no muaj cov ntaub ntawv hais txog cov khoom lag luam nrov tshaj plaws thiab zoo li qhov no hauv:

Tej zaum nws yog qhov tseeb tawm ntawm tus puav uas nws yuav luag tsis tuaj yeem ua haujlwm nrog nws hauv Excel hauv daim ntawv no: cov kab khoob los ntawm ib qho nrog cov ntaub ntawv, cov hlwb sib koom ua ke, cov kab ntxiv, ntau qib header, thiab lwm yam yuav cuam tshuam.

Yog li ntawd, ib sab ntawm cov ntaub ntawv no hauv tib lub nplaub tshev, peb tsim lwm cov ntaub ntawv tshiab Handler.xlsx, nyob rau hauv uas peb yuav tsim ib tug Power Query query uas yuav thauj cov ntaub ntawv dab tuag los ntawm qhov chaw upload ntaub ntawv Sab saum toj 100 khoom.xls, thiab muab lawv nyob rau hauv kev txiav txim:

Ua kev thov rau cov ntaub ntawv sab nraud

Qhib cov ntaub ntawv Handler.xlsx, xaiv ntawm tab Cov ntaub ntawv Hais kom Tau txais cov ntaub ntawv - Los ntawm cov ntaub ntawv - Los ntawm Excel Workbook (Cov ntaub ntawv - Tau cov ntaub ntawv - Los ntawm cov ntaub ntawv - Los ntawm Excel), ces qhia qhov chaw ntawm cov ntaub ntawv qhov chaw thiab daim ntawv peb xav tau. Cov ntaub ntawv xaiv yuav raug thauj mus rau hauv Power Query editor:

Cia peb coj lawv rov qab mus rau qhov qub:

1. Rho tawm cov kab khoob nrog Tsev — Rho tawm kab — Rho tawm cov kab khoob (Lub Tsev - Tshem Tawm - Tshem Tawm Kab Nruag).
2. Tshem tawm qhov tsis tsim nyog sab saum toj 4 kab dhau los Tsev — Rho tawm kab — Rho tawm sab saum toj kab (Lub Tsev - Tshem Tawm - Tshem Tawm Sab saum toj).
3. Tsa thawj kab mus rau lub rooj header nrog lub pob Siv thawj kab ua headers tab Tsev (Lub Tsev - Siv thawj kab ua header).
4. Cais tsib tus lej tsab xov xwm los ntawm cov khoom npe hauv kab thib ob uas siv cov lus txib cais kab tab transformation (Transform — Split Kem).
5. Tshem tawm cov kab uas tsis tsim nyog thiab hloov npe cov npe ntawm cov seem ntxiv kom pom kev zoo dua.

Yog li ntawd, peb yuav tsum tau txais cov duab hauv qab no, ntau qhov zoo nkauj dua:

Nws tseem yuav upload lub rooj ennobled no rov qab rau hauv daim ntawv hauv peb cov ntaub ntawv Handler.xlsx pab neeg kaw thiab download tau (Lub Tsev - Kaw & Load) tab Tsev:

Nrhiav txoj hauv kev rau ib daim ntawv thov

Tam sim no cia saib yuav ua li cas peb cov lus nug zoo li "hauv qab lub hood", hauv cov lus sab hauv tau tsim rau hauv Power Query nrog lub npe luv "M". Txhawm rau ua qhov no, rov qab mus rau peb cov lus nug los ntawm ob npaug nias rau nws nyob rau hauv txoj cai pane Kev thov thiab kev sib txuas thiab ntawm lub tab Review xaiv Advanced Editor (Saib - Advanced Editor):

Hauv qhov rai uas qhib, kab thib ob tam sim ntawd nthuav tawm txoj hauv kev nyuaj rau peb cov ntaub ntawv upload qub. Yog tias peb tuaj yeem hloov cov kab ntawv no nrog qhov tsis sib xws, hloov pauv, lossis qhov txuas mus rau Excel daim ntawv xovtooj ntawm txoj kev no tau sau ua ntej, ces peb tuaj yeem hloov tau yooj yim tom qab.

Ntxiv ib lub rooj ntse nrog rau txoj kev ua ntaub ntawv

Cia peb kaw Power Query tam sim no thiab rov qab mus rau peb cov ntaub ntawv Handler.xlsx. Cia peb ntxiv ib daim ntawv tshiab thiab ua ib lub rooj me me "ntse" rau nws, hauv tib lub xovtooj ntawm txoj kev tag nrho rau peb cov ntaub ntawv cov ntaub ntawv yuav raug sau:

Txhawm rau tsim lub rooj ntse los ntawm ntau yam tsis tu ncua, koj tuaj yeem siv cov keyboard shortcut Ctrl+T los yog khawm Format ua ib lub rooj tab Tsev (Lub Tsev — Format as Table). Cov kab ntawv kab lus (cell A1) tuaj yeem yog txhua yam. Kuj tseem nco ntsoov tias kom paub meej kuv tau muab lub rooj rau lub npe tsis tab constructor (Tsim).

Luam ib txoj hauv kev los ntawm Explorer lossis txawm nkag mus rau nws manually yog, ntawm chav kawm, tsis yooj yim, tab sis nws yog qhov zoo tshaj plaws kom txo qis tib neeg thiab txiav txim siab txoj hauv kev, yog tias ua tau, txiav. Qhov no tuaj yeem ua tiav siv cov txheej txheem Excel ua haujlwm CELL (CELL), uas tuaj yeem muab tawm ntau cov ntaub ntawv tseem ceeb ntawm lub xov tooj ntawm tes uas yog qhov kev sib cav - suav nrog txoj hauv kev mus rau cov ntaub ntawv tam sim no:

Yog tias peb xav tias cov ntaub ntawv cov ntaub ntawv ib txwm nyob hauv tib lub nplaub tshev raws li peb Cov Txheej Txheem, ces txoj kev peb xav tau tuaj yeem tsim los ntawm cov qauv hauv qab no:

=LEFT(CELL(“filename”); FIND(“[“;CELL(“filename”))-1)&”Top 100 products.xls”

los yog nyob rau hauv lus Askiv version:

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

… qhov ua haujlwm nyob qhov twg LEVSIMV (QAUG) siv ib daim ntawv los ntawm tag nrho qhov txuas mus rau qhov qhib square bracket (piv txwv li txoj kev mus rau lub nplaub tshev tam sim no), thiab tom qab ntawd lub npe thiab txuas ntxiv ntawm peb cov ntaub ntawv cov ntaub ntawv yog glued rau nws.

Parameterize txoj kev hauv cov lus nug

Qhov kawg thiab tseem ceeb tshaj plaws kov tseem nyob - los sau txoj hauv kev rau cov ntaub ntawv hauv qhov kev thov Sab saum toj 100 khoom.xls, xa mus rau ntawm tes A2 ntawm peb lub rooj tsim "ntse". tsis.

Txhawm rau ua qhov no, cia peb rov qab mus rau Power Query query thiab qhib nws dua Advanced Editor tab Review (Saib - Advanced Editor). Hloov ntawm kab ntawv kab-txoj kev hauv quotes "E: Cov ntaub ntawv muag khoomTop 100 khoom.xlsx" Cia peb qhia cov qauv hauv qab no:

Excel.CurrentWorkbook(){[Npe = "Settings"] [Cov ntsiab lus]0 {}[Txoj kev mus rau qhov chaw cov ntaub ntawv]

Cia peb saib seb nws muaj li cas:

• Excel.CurrentWorkbook() yog ib txoj haujlwm ntawm M hom lus rau kev nkag mus rau cov ntsiab lus ntawm cov ntaub ntawv tam sim no
• {[Npe = "Settings"] [Cov ntsiab lus] - qhov no yog qhov ua kom zoo dua rau qhov ua haujlwm dhau los, qhia tias peb xav tau cov ntsiab lus ntawm "ntse" lub rooj tsis
• [Txoj kev mus rau qhov chaw cov ntaub ntawv] yog lub npe ntawm kab hauv lub rooj tsisqhov peb xa mus
  
• 0 {} yog tus lej kab hauv lub rooj tsislos ntawm qhov peb xav tau cov ntaub ntawv. Lub kaus mom tsis suav thiab tus lej pib ntawm xoom, tsis yog los ntawm ib qho.

Qhov ntawd yog tag nrho, qhov tseeb.

Nws tseem nyem rau Tag thiab xyuas seb peb qhov kev thov ua haujlwm li cas. Tam sim no, thaum xa tag nrho cov nplaub tshev nrog ob cov ntaub ntawv sab hauv mus rau lwm lub PC, qhov kev thov yuav nyob twj ywm thiab txiav txim siab txoj hauv kev rau cov ntaub ntawv tau txais.

• Power Query yog dab tsi thiab vim li cas nws thiaj xav tau thaum ua haujlwm hauv Microsoft Excel
• Yuav ua li cas import cov ntawv ntab ntab rau hauv Cov Lus Nug Lub Zog
• Rov tsim dua XNUMXD Crosstab rau lub rooj tiaj tus nrog lub zog lus nug