Parametrització de camins de dades a Power Query

Si ja heu començat a utilitzar les eines del complement gratuït Power Query a Microsoft Excel, aviat us trobareu amb un problema altament especialitzat, però molt freqüent i molest associat a la ruptura constant dels enllaços a les dades d'origen. L'essència del problema és que si a la vostra consulta us referiu a fitxers o carpetes externs, Power Query codificarà el camí absolut cap a ells al text de la consulta. Tot funciona bé al vostre ordinador, però si decidiu enviar un fitxer amb una sol·licitud als vostres companys, es quedaran decebuts, perquè. tenen un camí diferent a les dades d'origen al seu ordinador i la nostra consulta no funcionarà.

Què fer en una situació així? Vegem aquest cas amb més detall amb l'exemple següent.

Formulació del problema

Suposem que tenim a la carpeta E:Informes de vendes es troba l'arxiu Els 100 millors productes.xls, que és una càrrega des de la nostra base de dades corporativa o sistema ERP (1C, SAP, etc.) Aquest fitxer conté informació sobre els articles de productes bàsics més populars i té aquest aspecte a l'interior:

Probablement estigui clar d'entrada que és gairebé impossible treballar-hi a Excel d'aquesta forma: les files buides a través d'una amb dades, les cel·les combinades, les columnes addicionals, una capçalera de diversos nivells, etc. interferiran.

Per tant, al costat d'aquest fitxer a la mateixa carpeta, creem un altre fitxer nou Handler.xlsx, en què crearem una consulta de Power Query que carregarà dades lletjos del fitxer de càrrega font Els 100 millors productes.xls, i posa-los en ordre:

Realització d'una sol·licitud a un fitxer extern

Obrint l'arxiu Handler.xlsx, seleccioneu a la pestanya dades Comando Obteniu dades - Del fitxer - Des del llibre de treball d'Excel (Dades — Obtenir dades — Del fitxer — Des d'Excel), després especifiqueu la ubicació del fitxer font i el full que necessitem. Les dades seleccionades es carregaran a l'editor de Power Query:

Tornem-los a la normalitat:

1. Elimina les línies buides amb Inici — Suprimeix línies — Suprimeix línies buides (Inici — Elimina les files — Elimina les files buides).
2. Suprimeix les 4 línies superiors innecessàries Inici — Suprimeix les files — Suprimeix les files superiors (Inici — Elimina les files — Elimina les files superiors).
3. Aixequeu la primera fila a la capçalera de la taula amb el botó Utilitzeu la primera línia com a capçaleres llengüeta Home (Inici — Utilitzeu la primera fila com a capçalera).
4. Separeu l'article de cinc dígits del nom del producte a la segona columna mitjançant l'ordre columna dividida llengüeta Transformació (Transformar — Dividir la columna).
5. Elimina les columnes innecessàries i canvia el nom dels encapçalaments de les restants per a una millor visibilitat.

Com a resultat, hauríem d'obtenir la següent imatge, molt més agradable:

Queda per tornar a carregar aquesta taula ennoblida al full del nostre fitxer Handler.xlsx l'equip tancar i descarregar (Inici — Tancar i carregar) llengüeta Home:

Trobar el camí a un fitxer en una sol·licitud

Ara vegem com es veu la nostra consulta "sota el capó", en el llenguatge intern integrat a Power Query amb el nom concís "M". Per fer-ho, torneu a la nostra consulta fent doble clic sobre ella al panell dret Sol·licituds i connexions i a la pestanya Crítica triar Editor avançat (Veure — Editor avançat):

A la finestra que s'obre, la segona línia mostra immediatament un camí codificat en dur al nostre fitxer de càrrega original. Si podem substituir aquesta cadena de text per un paràmetre, una variable o un enllaç a una cel·la de full d'Excel on aquest camí estigui escrit prèviament, podem canviar-lo fàcilment més endavant.

Afegiu una taula intel·ligent amb una ruta de fitxer

Tanquem Power Query de moment i tornem al nostre fitxer Handler.xlsx. Afegim un nou full buit i fem una petita taula "intel·ligent", a l'única cel·la de la qual s'escriurà el camí complet al nostre fitxer de dades font:

Per crear una taula intel·ligent a partir d'un rang normal, podeu utilitzar la drecera del teclat Ctrl+T o botó Format com a taula llengüeta Home (Inici — Format com a taula). L'encapçalament de la columna (cel·la A1) pot ser absolutament qualsevol cosa. Tingueu en compte també que per a més claredat he donat un nom a la taula Paràmetres llengüeta constructor (Disseny).

Per descomptat, copiar un camí des de l'Explorador o fins i tot introduir-lo manualment no és especialment difícil, però el millor és minimitzar el factor humà i determinar el camí, si és possible, automàticament. Això es pot implementar mitjançant la funció estàndard de full de treball d'Excel CEL·LULAR (CEL·LULA), que pot proporcionar un munt d'informació útil sobre la cel·la especificada com a argument, inclosa la ruta al fitxer actual:

Si suposem que el fitxer de dades font sempre es troba a la mateixa carpeta que el nostre processador, el camí que necessitem es pot formar amb la fórmula següent:

=LEFT(CELL(“nom del fitxer”);TROBAR(“[“;CELL(“nom del fitxer”)-1)&”Els 100 millors productes.xls”

o en versió anglesa:

=LEFT(CELL(«nom del fitxer»);TROBAR(«[«;CELL(«nom del fitxer»))-1)&»Топ-100 товаров.xls»

… on és la funció LEVSIMV (ESQUERRA) agafa un fragment de text des de l'enllaç complet fins al claudàtor d'obertura (és a dir, el camí a la carpeta actual), i després el nom i l'extensió del nostre fitxer de dades font s'hi enganxa.

Parametritza el camí a la consulta

L'últim i més important toc queda: escriure el camí al fitxer font a la sol·licitud Els 100 millors productes.xls, fent referència a la cel·la A2 de la nostra taula "intel·ligent" creada Paràmetres.

Per fer-ho, tornem a la consulta de Power Query i tornem a obrir-la Editor avançat llengüeta Crítica (Veure — Editor avançat). En lloc d'un camí de cadena de text entre cometes "E: Informes de vendesTop 100 products.xlsx" Introduïm la següent estructura:

Excel.CurrentWorkbook(){[Name="Configuració"]}[Contingut]0 {}[Camí a les dades d'origen]

Vegem en què consisteix:

• Excel.CurrentWorkbook() és una funció del llenguatge M per accedir al contingut del fitxer actual
• {[Name="Configuració"]}[Contingut] – aquest és un paràmetre de perfeccionament de la funció anterior, que indica que volem obtenir el contingut de la taula "intel·ligent" Paràmetres
• [Camí a les dades d'origen] és el nom de la columna de la taula Paràmetresal qual ens referim
  
• 0 {} és el número de fila de la taula Paràmetresdel qual volem treure dades. La tapa no compta i la numeració comença des de zero, no des d'un.

Això és tot, de fet.

Queda per clicar Finish i comproveu com funciona la nostra sol·licitud. Ara, en enviar tota la carpeta amb els dos fitxers dins a un altre ordinador, la sol·licitud romandrà operativa i determinarà automàticament el camí a les dades.

• Què és Power Query i per què es necessita quan es treballa a Microsoft Excel
• Com importar un fragment de text flotant a Power Query
• Redisseny d'una taula encreuada XNUMXD a una taula plana amb Power Query